finding all .mdb databases in a directory, open it and find SIMILARLY named tables and perform action
Posted on 2006-05-11
I have a directory with lots of .mdb databases. The previous programmer used a similar method to email reports generated by these databases after exporting them. He used a distribution table located in each individual database. The problem is that most of the names in each distribution list are the same or from a compiled list. When an employee leaves and another takes his / her place its problematic to 1. FIND all of the relevant databases and 2. update each table in each database with tne new info.
1.)I want to loop over all files in that directory and find files with the .mdb extension (got this)
2.) Open that database (got this)
3.) Open the table (Problem!!!! not all of the tables are named exactly the same but all have tblDest in the name somewhere) How do I search for a tablename with a wildcard within the Set rsMyRS = dbMyDB.OpenRecordSet(searchstring, dbOpenDynaset) where searchstring is the table name?
Some tables are named tblDest, some are tbl(two digits that vary)Dest and some are tblDest(then two digits that vary)
I need to search using a wildcard within the openrecordset method but dont know how to pull this off without using a case select statement and putting in all possible names (slow and might as well export each tbldest manually but I have well over 100 databases and growing)
The intent is to import all of these tables naming them [table name ] & [database of origin name],relate these names to a master table and use a RELATIONAL DATABASE to maintain who gets what report and what the name is, changable as the employees change.
Your help would be appreciated......