Locate Database through VBA 3

[marina9]

Hi everyone,

Is there a way to programmatically locate a database in a directory through VBA? For example, I have two Access 97 databases (front end and back end) that will be converted to XP shortly. As a process of the conversion, my company wants to eliminate any hard coded drive paths in the code so during deployment the package can deposit the databases to another drive location. In the code for the front end database it references compacting the back end database. For example:

DBEngine.CompactDatabase "W:\Recovery\Data\BackEndDB.mdb", "W:\Recovery\Data\db1.mdb"
Kill "W:\Recovery\Data\BackEndDB.mdb"
DBEngine.CompactDatabase "W:\Recovery\Data\db1.mdb", "W:\Recovery\Data\BackEndDB.mdb"
Kill "W:\Recovery\Data\BackEndDB.mdb"

The new location for the BackEndDB may not be in the data folder and certainly will no longer be in the W:\ drive. However, both the front end and back end databases will be in the same drive. Is there a way to search for a specific database name in a specific directory through vba? Thanks!

 

[Blorf]

I am not positive I understand, but if you want to locate the path of a mdb file, you can use something like this:

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.FileName = "*.MDB"
.MatchTextExactly = True
End With

With Application.FileSearch
If .Execute() > 0 Then
DO SOMETHING OR OTHER
End If
End With

Hope that works out for you.

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[marina9]

Blorf, you are a genius! Thank you so much for you help on this matter. That is exactly what I need! Have a star.

 

[RickSpr]

Here's another approach: Extract the path of the back-end database from the Connect property of a TableDef object for any table in the back-end, using DAO. The Connect string looks something like this:
...;DATABASE=[back end path]...
That is, it's preceded by ";DATABASE=" and followed by another semicolon or the end of the string. Simply extract the path name using Instr() and Mid$(). This approach has the advantage that it will work regardless of what drive the back end database is on.

This assumes that your back-end is already linked up, either by using the Linked Table Manager or VBA code (RefreshLink method of the DAO TableDef object).

BTW, your compaction code is rather risky. If anything goes wrong with the first CompactDatabase, this code will delete the only copy. Also, the second CompactDatabase and Kill (I assume the Kill should have been for db1.mdb) can be replaced with a Name...As statement to just rename the database.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[marina9]

Thanks for the suggestion Rick! I will definitely take it into consideration.

 

[Blorf]

Glad to help.

If your application is front/back specific, Rick's suggestion may run quite a bit quicker, especially if you have to scan a large drive.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert