NWILSON4
asked on
finding all .mdb databases in a directory, open it and find SIMILARLY named tables and perform action
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(searc
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......
'nix
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(searc
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......
'nix
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TornadoV's code is absolutely right.
To get all the mdb files in a particular folder, just try the following code:
Dim fso As Object
Dim fol As Object
Dim fi As Object
Set fso = CreateObject("Scripting.Fi
Set fol = fso.GetFolder("c:\")
For Each fi In fol.Files
If UCase(Right(fi.Name, 4)) = ".MDB" Then Debug.Print fi.Name
Next
Regards,
Jungle
To get all the mdb files in a particular folder, just try the following code:
Dim fso As Object
Dim fol As Object
Dim fi As Object
Set fso = CreateObject("Scripting.Fi
Set fol = fso.GetFolder("c:\")
For Each fi In fol.Files
If UCase(Right(fi.Name, 4)) = ".MDB" Then Debug.Print fi.Name
Next
Regards,
Jungle
listening
NWILSON4, I have two questions if I may,
1. Why didn't you split points between junglerover77 and me?
2. Why did you give me 'B'?
1. Why didn't you split points between junglerover77 and me?
2. Why did you give me 'B'?
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Like '*Dest*') AND ((MSysObjects.Type)=1));