How do I export access schema info from the command line?

[Guest]

I am trying to scan network drives for all potential access databases. Once
I locate a database I want to programmatically export the metadata for
analysis. I would like to write a batch routine that would search the
drives, list all found mdb files, query the metadata for tables, fields,
types, comments, and relationships. Does anyone have experience or know a
way to get metadata in an access database from the command line?

 

[Guest]

Just out of curiousity, is there any legitimate reason for doing this?
Especially when scanning across network drives?

 

[dbahooker]

well if you use SQL Server Access Data Projects; it would be quite easy

otherwise you could write a VBS script for doing this.. that would be
easy

more importantly though.. i would just run some access automation to
programmatically-- from one access database-- open all the
accessdatabases you want to look at

and then you're probably just concerned with the MsysObject tables;
things along those lines

you should be able to just export that to a centralized place pretty
easily..

or if msysobjects is all you need; then you woudln't even need to open
them all programmatically; you could just import docmd.TransferDatabase
for example

i usually go the route with access automation

Dim AccApp as new Access.Application
AccApp.Visible = True
AccApp.OpenAccessDatabase(path)

etc something along those lines

 

[Guest]

I am trying to identify access databases within an organization. Once I can
find them I want to determine whether there are identical data elements
within different databases. Additionally we would like to find link
information to determine whether source are the same. Not sure if this is
legitamate approach but would hopefully give us some insight to the volumes
of replication. Any suggestions are welcome. Thanks for your reply.

Rob

 

[TC]

Do not use the MSys tables. That is poor advice. Instead, use the
normal object model methods & properties to get what you want.

For example, this VBScript will display the names of all tables in the
specified database:

(untested)

dim acc, db, td
set acc = createobject ("access.application")
set db = acc.opendatabase ("C:\blah.mdb")
for each td in db.tabledefs
msgbox td.name
next
set db = nothing
set acc = nothing

HTH,
TC [MVP Access]

 

[dbahooker]

tc why not msysobjects? im just curious

 

[TC]

Hi Aaron

I recommend not using the MSys tables, for the following reasons:
1/ they're not documented,
2/ they can change from version to version,
3/ you can't get everything you might want, from them, and
4/ the code you write to access them, is a bit hard to understand -
because of point /, I guess.

In contrast, the object model (tabledefs, fields, & so on):
1/ is fully documented (including all changes from version to version),
2/ provides all of the information that there is to get, about each
object, and
3/ is easier to undertand IMHO.

So, considering all that, I feel the object model methods & properties
are the way to go.

Cheers,
TC [MVP Access]

 

[aaron.kempf]

wow you're crazy i think lol

i mean.. undocumented?? they can change?

i just swear

SELECT *
FROM Msysobjects IN 'C:\mydatabase.mdb';

that's just a whole lot easier than looping through all your DAO
garbage and having hangs and all that.. i mean.. do you really still
use DAO for anything?

NO WONDER YOU HATE ADP

ADP and ADO rock buddy

 

[dbahooker]

oh gosh

now i understand what you mean; so msysobject is changing drastically
in the next version? is that what you're telling me?

 

[trollstomper]

"oh gosh...." gee whiz golly shucks!


dbahooker / aaron strikes again. hey, why are the dates of your troll junk postings so far in the future????? 09/01/06 ?????
And why fill up junk on this forum with your useless junk?!?!