Loop through access databases and get table defintions for all using vba code

[tuandelia]

Hi, I need help.
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?

Thank You.

 

[Mihail]

Has your DB's paths a template in order to access its ?
Has your DB's names a template in order to open its in a Do-Loop cycle ?

So, the main question for now is:
How you plan to open this DBs ?

 

[jdraw]

This can be done. I have done this is the past. The general approach was as follows:

1-get a list of all databases (in my case it was .mdb) on a per drive basis C: D: E: (I used a dos command IIRC) to specific files

I just tried this with DOS command

DIR C:\*.mdb /o/s/w >C:\Temp\AllMDBonC.txt

see attached info for output
You will need a program to read and parse the directories and related .mdb names
2-create a database with a table to record
- drive, path, db name, date of run
3-populate the database with the info from 1 (import as csv)
4-I have a routine that gets field names and description etc from tables in a database (deletes old version, creates new and parses to get names, descriptions(if they exist), ordinal position and store in the newly created table)
5-Set up to open a second database, and go through the table in 3, opening each database in turn, then use the routine in 4 to get and record the info
6-repeat step 5 until you have completed for each database on each drive.

I'm sure there are other approaches, but that's what I recall. Hope it's useful to you.

NOTE: I do not have all the procedures on this laptop.


This is the first part of the output based on DOS command on current laptop.

 Directory of c:\A2K

db1New2009_aug2012.mdb
db1New2010_withIssuesOct2013.mdb
Image Manipulation.mdb
RawLoaderStandardTemplate_v2_Backup.mdb
               4 File(s)    207,724,544 bytes

 Directory of c:\A2K\neatcdFiles

Neatcd97.mdb   
               1 File(s)        792,576 bytes

 Directory of c:\backup

db1New2009 2, 05-07-2012.mdb   
               1 File(s)    132,751,360 bytes

 Directory of c:\Program Files\Microsoft Office2003\Visio11\1033

DBSAMPLE.MDB   
               1 File(s)        483,328 bytes

 Directory of c:\Users\Jack\A2K

911Inventory.mdb
BPSv2jd.mdb
db1_warrior.mdb
db1New.mdb
db1New2009.mdb
db1New2009_Backup.mdb
db1New2009Copy01Nov2011.mdb
db1New2010copyfromAMDOct13_2013.mdb
db1New2010copyfromAMDOct13_2013_Backup.mdb
Example Stock Control.mdb
FullCopyToNewFeb052014.mdb
Get_Coordinates.mdb
jDatabase.mdb
jed_NewFeb172014.mdb
jed_NewFeb172014_Backup.mdb
jVolunteer.mdb
ListBoxMove2000.mdb
MouseOverPopups.mdb
MultiLingualSample.mdb
MyForumsAndPwd.mdb
ProgressMeter.mdb
RawLoaderStandardTemplate_v2_Backup.mdb
SampleDB_V11.mdb
TestDBAWFAug24.mdb

 

[tuandelia]

Thank you very much.
I have a file listing of the databases. Now I need to loop through the databases and obtain the table and column names for each local table (not system or linked tables) so that I can determine what needs to be redacted.

 

[jdraw]

Attached is a database mdb format that has

- 1 table data_dictionary
- 1 module with 2 procedures you'll need
-----DocumentTables
-----FieldType

Look at table data_dictionary to see the kind of info recorded.

To make this work
Open the module, run DocumentTables
it will delete the table and build it from scratch.
It calls fieldtype to get descriptive names.

NOTE: This is for tables in the current database. I don't have the code for opening a second database, getting the
table and field names and putting those in a table that includes dirve/folder info and database name on this laptop.

Good luck

 

[tuandelia]

Thank you very much. I'm up and running now.

 

[jdraw]

Glad you have it working.

If you have the original location of the databases in your list, you can changed the data_dictionary table to allow for the location field. This would be helpful if you have multiple tables with the same names. And if soome tables with same names have different fields.

Anyway, good luck with the project.