Set DB to a different DB

[doulostheou]

I am trying to write some code that will automate the update of a number of similar databases. I need to delete a table from each database and then import a table.

I believe I would have to Set DB to the path of the database I am trying to manipulate. I am using the following:

Set Db = "T:\logsheets\TestLogsheets\Employees\abartlett.mdb"

I get a type mismatch error. The only examples I find set the DB to CurrentDB. How do I reference another database?

 

[pdx_man]

Look up CurrentDb under Help.

[This message has been edited by pdx_man (edited 05-13-2002).]

 

[doulostheou]

Thank you. I wouldn't have guessed that the solution to working with another DB would be found under CurrentDB. The syntax for anyone who stumbles accross this in the future is:

Set Db = DBEngine.Workspaces(0).OpenDatabase("DatabasePath.mdb")

This solution only allowed me to get to the next line of code which did not work either.

I want to delete a table from the database that I am specifying with the OpenDatabase method and then delete it.

Just using DoCmd.DeleteObject acTable, "Storage" did not work as it was still looking for Storage in the Current DB. I tried a few different things based on previous posts made here. I tried specifying that the "Storage" table was in a different Database by using:
Db.TableDefs("Storage")
This did not work so I tried indicating that I was doing a command in a separate database:

Db.DoCmd....

This did not work either.

How can I delete a table from another database and then import a table back into that same database?

 

[chrismcbride]

Just a thought, but might you be able to use...

DoCmd.SelectObject

then your delete?
Chris

 

[doulostheou]

Didn't work. SelectObject also defaults to working with the CurrentDB and I don't know how to properly reference the table in the other database.

 

[doulostheou]

MHM's post in the following article provided the key to the answer:
http://www.access-programmers.co.uk/ubb/Forum7/HTML/001344.html

There was line of syntax missing from the sample code which took me awhile to find. I'll post the full solution here.

dim oAcc2 AS Access.Application
set oAcc2=createObject(,"Access.Application")
oAcc2.OpenCurrentDatabase ("DatabasePath.mdb") 'This was the line that was missing
oAcc2.DoCmd.DeleteObject etc... 'Now simply prefacing your docmd with oAcc2 will allow you to perform necessary alterations in that database

I have finally been able to automate updates to 180 logsheets!