Well, here is a solution that gathers the data you want and then deposits it in a seperate database that you can then query.
You could probably make a call to this app from within access, and then make a call to the database where the info is stored.
Get the tool here:
http://office.microsoft.com/en-us/as...401661033.aspx
Here is a write up of the features from another post. I hope this takes all the text:
'=====
Scanning tool
This component of the Toolkit scans drives, file shares, or machines for Access database files. The tool searches specifically for .MDB or .MDE file extensions. The Toolkit can't detect Microsoft Access projects, ADPs, or ADEs. The scanning tool (which runs as the adscanner.exe program) collects file properties, Microsoft Access properties, and data access object (DAO) properties of the databases. The adscanner.exe program isn't an Access application, so you can run it from a computer that doesn't have Access installed. You run it from the command line interface, and it has eight possible command line arguments. This list describes the details about these command line arguments:
Result path
You use this required argument to specify the folder where you want to store the results of the scan. The adscanner.exe program creates one XML file for each database it scans.
Source path
This argument is optional, and you can use it to specify the location you want to scan for Access databases. You can specify a specific drive letter, a universal naming convention (UNC) sure path, or a specific database file. When you specify a drive letter or UNC, the scanning tool scans that respective folder and all the subfolders for Access databases. If you don't specify this command line argument, the adscanner.exe program scans all the local hard drives for Access databases. However, when you don't specify this argument, the scanning tool won't scan the mapped drives on that computer.
[/dao:off]
This is another optional argument you can use to specify whether the scanner collects DAO properties from the Access databases it scans. If you don't specify this argument, the adscanner.exe program collects the DAO properties from the databases it scans.
[/acc:on]
An optional argument, you can use it to specify whether the scanning tool will scan Microsoft Access properties from the Access databases. To collect these properties, you must run this program on a computer that has Access 95 or later. If you run this command line on a machine with previous versions of Access, it simply won't collect the appropriate data. By default, adscanner.exe doesn't collect Microsoft Access properties from the Access databases it scans. Collecting these properties can be somewhat time-consuming, so be sure you test these values on a large number of databases.
[/com:off]
This argument is optional, and you can use it to specify whether the program collects the computer properties for the Access databases it scans. If you don't specify this argument, the adscanner.exe program collects the computer properties for the Access databases.
[/noconvdlg:[on|off]]
This command line argument is optional. You can use this argument to set the noconvertdialog property for the databases the program scans. If this property is set to "Yes," access to process the appearance of the conversion dialog box from appearing when you open in a database that was created in access 2000 or earlier in either access 2002 were access 2003 ???Not sure what you mean here. Do you mean: Access prevents the conversion dialog box from appearing when you open a database in Access 2002 or 2003 that was created in Access 2000 or earlier? Please clarify??em Setting this property on the database does not place the database and the State worry cannot be converted ???Not sure what you mean here either. Please clarify???em. Instead, it forces the user to use specific menu prompts to convert the database rather than responding to a dialog.
If you set this property for an Access database, the last modified date of the Access database changes to the date on which you execute the scanning tool. If you don't specify this argument, the adscanner.exe program leaves the noconvertdialog property set to "uncreated."
[/maxcopy Size]
Another optional command line argument, you can use it to specify the maximum file size and megabytes (MB) you want to let the scanner copy for the collection of Microsoft Access properties. When the program collects Microsoft Access properties, it makes a copy of each database to scan and then opens the copy. The reason it does this instead of opening the original database is to ensure it doesn't alter the database's last modified date. If you don't specify this command line argument, the program uses a default file size of 50 MB.
[/temppath TempPath]
This optional argument specifies the path to which the program saves database copies it creates. You can specify a drive letter or a UNC path. If you don't specify a value in this argument, the adscanner.exe program uses the folder path specified for the tent variable and the Microsoft Windows operating system running on your computer.
You can use the adscanner.exe program on its own, in conjunction with the reporting tool, or in conjunction with programs intended to run command line executables. Because the scanning tool supports standalone command line use, you can run it remotely from a management workstation or from a centrally administered script. The scanning tool saves the information it collects in XML files, regardless of the command line options you use. You can easily import these XML files into the Microsoft Access Conversion Toolkit reporting tool.
Reporting tool
The reporting tool in the Microsoft Access Conversion Toolkit is an Access database that's designed to let the user run a scan through the user interface rather than having to use the command line tool, and interpret the scan results in an easy-to-read, customizable format. This part of the Toolkit helps you make sense of the information the scanning tool collects. There are a number of reports, charts, and forms that analyze and summarize the information the scanning tool gathered and stored. The reporting tool components that analyze your data are deliberately generic so any organization or individual can use them regardless of the type of business or the size of the organization. However, as with any example or template Microsoft Access database, you can customize the reporting tool to fit your specific needs.
You can break up the reporting tool into two separate functions: a friendly user interface to run the scanner, and to analyze the result set you receive after scanning your company, server, or a single machine for any potential conversion issues. By importing all the XML files the scanning tool creates, each of which represents a single database, the reporting tool accumulates the data to form a large picture of Microsoft Access usage. For example, you can produce a report that lists the known issues identified for each database. The reporting tool also categorizes each database according to the likelihood it may require manual intervention to convert successfully.
Beyond conversion
There are number of built-in functions in the reporting tool that help you analyze your databases. Clearly, this tool is a huge help to anyone converting Access 97 databases to a newer version of Access. However, there are several ways you can use this tool outside the conversion process to manage your Access databases. Here are some questions the tool can answer for you:
How many databases, created in each version of Microsoft Access, do I have or are in my organization?
Is anyone still using this database?
Do I have multiple copies of the same database? Which one has the most current data?
How much server space are my databases occupying?
I'm renaming one of my primary servers. How many of my linked tables will be affected?
I'm rolling out a new version of Windows and reformatting all the users' machines. Are there any external files (references) the database requires to be present to run?
Because the reporting tool is a Microsoft Access database, it expands to fit your specific needs and helps you answer the questions most important to you.
Understanding the tool's basic table structure is fairly simple. Each object type in a database has its own table. For example, it stores all information about queries in a table called "tblQueries." Database property information such as name, database path, create date, the version of Access in which you (or the original developer) created the database, object counts, and size is stored in the table "tblDatabases." The table "tblFields" stores the name and field type of every field in every table reference by ID numbers to the database and table to which it belongs.
Questions answered
So, armed with the information you've gathered from the Microsoft Access Conversion Toolkit, let's answer some of the questions I mentioned earlier. Because the structure of each copy of the reporting tool is identical, anyone can use the SQL statements and following examples.
How many databases, created in each version of Microsoft Access, do I or my organization have?
All the information about a database as a file is stored in a single table named "tblDatabases." The query to answer this question only has to contain that one table. This query's SQL statement is shown in Option A. However, the version number stored in this database table refers to the product's internal version number and not the user-friendly version. To include that user-friendly information, you have to add the "tblMicrosoft AccessVersions" lookup table to your query as shown in Option B.
Option A
SELECT Count(tblDatabases.DatabaseName)
AS CountOfDatabaseName, tblDatabases.Microsoft AccessVersion
FROM tblDatabases
GROUP BY tblDatabases.Microsoft AccessVersion
ORDER BY Count(tblDatabases.DatabaseName);
Option B
SELECT Count(tblDatabases.DatabaseName)
AS CountOfDatabaseName,
tblMicrosoft AccessVersions.Version
FROM tblMicrosoft AccessVersions
INNER JOIN tblDatabases
ON tblMicrosoft AccessVersions.VersionCode = tblDatabases.Microsoft AccessVersion
GROUP BY tblMicrosoft AccessVersions.Version
ORDER BY Count(tblDatabases.DatabaseName);
Is anyone still using this database?
You can approach this question in one of two ways: find a list of those databases you haven't used since a specific date or create a list of those databases that have been used since that date. The following two SQL statements represent the methods for answering both of those questions, respectively.
Option A
SELECT tblDatabases.DatabaseName,
tblDatabases.LastMicrosoft Accessed
FROM tblDatabases
WHERE (((tblDatabases.LastMicrosoft Accessed)<=#12/31/2004#));
Option B
SELECT tblDatabases.DatabaseName,
tblDatabases.LastMicrosoft Accessed
FROM tblDatabases
WHERE (((tblDatabases.LastMicrosoft Accessed)>#12/31/2004#));
In the table that stores general database information, there's a column called "LastModified," and this value shows the last time a user opened the database. However, if the database is a back-end database, which stores only tables linked to other databases, the last time the front end accessed the database may be greater than the "Last Modified" date. One way you can determine if it's a back-end database is to find out if it only contains tables. You will, of course, know your data best, but in cases where you have a database that contains objects other than tables, you should be able to reliably use the last modified date of the database to determine when it was really last used. This SQL statement is a query that asks a combination of questions: Is this a back-end database, and what is its last modified date?
SELECT tblDatabases.DatabaseName,
tblDatabases.LastMicrosoft Accessed,
tblDatabases.QueryCount,
tblDatabases.FormCount,
tblDatabases.ReportCount,
tblDatabases.MacroCount,
tblDatabases.ModuleCount
FROM tblDatabases
WHERE (((tblDatabases.LastMicrosoft Accessed)<#12/31/2004#)
AND ((tblDatabases.QueryCount)>=1))
OR (((tblDatabases.LastMicrosoft Accessed)<#12/31/2004#)
AND ((tblDatabases.FormCount)>=1))
OR (((tblDatabases.LastMicrosoft Accessed)<#12/31/2004#)
AND ((tblDatabases.ReportCount)>=1))
OR (((tblDatabases.LastMicrosoft Accessed)<#12/31/2004#)
AND ((tblDatabases.MacroCount)>=1))
OR (((tblDatabases.LastMicrosoft Accessed)<#12/31/2004#)
AND ((tblDatabases.ModuleCount)>=1));
Do I have multiple copies of the same database? Which one has the most current data?
The way you want to answer this question depends on how you define "most current." If you decide to use the date the database was last opened, then you want to refer to the "LastModifed" field in the table "tblDatabases." However, "most current" might mean to you the database with the most data and therefore the largest. You can satisfy both of these definitions with a query based on the same table. Here, I'll use a grouped query to count the number of copies of the database and the Max function to get the date the database was last opened or is the greatest size.
Option A
SELECT tblDatabases.DatabaseName,
Count(tblDatabases.DatabaseName)
AS CountOfDatabaseName, Max(tblDatabases.LastMicrosoft Accessed)
AS MaxOfLastMicrosoft Accessed
FROM tblDatabases
GROUP BY tblDatabases.DatabaseName
HAVING (((Count(tblDatabases.DatabaseName))>1))
ORDER BY tblDatabases.DatabaseName;
Option B
SELECT tblDatabases.DatabaseName,
Count(tblDatabases.DatabaseName)
AS CountOfDatabaseName, Max(tblDatabases.Size)
AS MaxOfSize
FROM tblDatabases
GROUP BY tblDatabases.DatabaseName
HAVING (((Count(tblDatabases.DatabaseName))>1))
ORDER BY tblDatabases.DatabaseName;
How much server space are my databases occupying?
You can use the Microsoft Access Conversion Toolkit to scan one or multiple machines. To determine how much space your Microsoft Access databases are using on a single machine depends on the type of scan you perform. The statement in Option A shows the query to get a total amount of space used when on a single machine. The statement in Option B shows how to choose a specific machine in a multiple machine scan.
Option A
SELECT Sum(tblDatabases.Size) AS SumOfSize
FROM tblDatabases;
Option B
SELECT Sum(tblDatabases.Size) AS SumOfSize, tblDatabases.Path
FROM tblDatabases
GROUP BY tblDatabases.Path
HAVING (((tblDatabases.Path)="\\myservername"));
I'm renaming one of my primary servers. How many of my linked tables will be affected?
It probably doesn't happen often, but you might have to remove, rename, or rebuild a server or machine you're using. In this example, I assume you have to determine how many of your databases rely on a database on that server. Unless everyone happens to be connected at the same time, there isn't a way to determine this on the server side, and up until now there hasn't been an easy way to determine it from the Microsoft Access side either. Unfortunately, this would typically mean you only find out how important a resource is after it's gone, so this query lets you find that out before you remove the resource.
In tblTables, there's a field that stores linked table connection information for any type of linked table. You can use this value in conjunction with tblDatabases to determine which database has a linked table to this server (Option A) or by itself to determine the number of tables linked to the server (Option B).
Option A
SELECT tblDatabases.DatabaseName
FROM tblDatabases INNER JOIN tblTables
ON tblDatabases.DatabaseID = tblTables.DatabaseID
WHERE (((tblTables.Connect) Like "myservername"))
GROUP BY tblDatabases.DatabaseName
ORDER BY tblDatabases.DatabaseName;
Option B
SELECT Count(tblTables.TableName)
AS CountOfTableName
FROM tblDatabases INNER JOIN tblTables
ON tblDatabases.DatabaseID = tblTables.DatabaseID
WHERE (((tblTables.Connect) Like "myservername"));
I'm rolling out a new version of Windows and will reformat all the users' machines. Are there any external files (references) the database requires to be present to run?
As you probably know, a missing reference in the database won't only negate the functionality referenced in that particular library, but it will break all the functionality in all the references. For that reason, it's critical to make sure all references are available, even if they aren't being used. In this scenario, you have to determine which references are being used so when you roll out the Windows upgrade, you can be sure to reinstall those particular files.
The tblReferences table contains all reference library information about the databases you had the Microsoft Access Conversion Toolkit scan including name, version, and whether the references are currently broken. This SQL statement tells you which references your Access databases use. However, when looking at your results, keep in mind that every Access database by default has multiple references, so at least some of the results will be default values.
SELECT tblReferences.ReferenceName
FROM tblReferences
GROUP BY tblReferences.ReferenceName;
Few more reminders
There are a few things to keep in mind when mining through this data, which you can use or disregard if they don't pertain to your situation.
TblTables not only contains information about the tables in your databases, it also contains information about the system tables in those same databases. Depending on the version of Microsoft Access, the number of system tables in a database can change. However, all system tables begin with the "MySys" prefix.
The toolkit scans for all databases on a system, including sample databases. Before you scan for databases, you can set an option to specify which databases are samples. The toolkit reports all the databases it finds in the scan; , however, all the databases you've specified as samples will have "Yes" checked in the IsSampleDatabase column in tblDatabases.
Before you disregard all sample databases or archive them, keep in mind that many people start out looking at a sample, then develop their own solution in that same file. It might be wise to check the file size of the sample to see if it's the same as a clean copy of the sample. Any discrepancy in the file size might be an indication that a user found it useful and has developed a custom application based on it.
"LastMicrosoft Accessed" doesn't correlate 100 percent with the concept of last used. Rather, it refers to the last time the database was physically opened. This value won't accurately reflect use for front-end or back-end databases.
If a database has a version number of zero, it means the scanning tool couldn't properly access the database for accurate information. Databases in which this might occur include secured or corrupt databases.
Queries whose name begins with a tilda (~) are internal queries Access created. This happens when an object's RecordSource property is set to a Select statement instead of a named query or when you create a field in a table using the Lookup Wizard.
When you compact and repair a database, be sure it updated to reflect the time at which you made the changes.
mmcdonal
Detect Access Version of Closed Database
