Running a query in a different DB

[aziz rasul]

What code can you use to run a query that's in a different ms access database?

 

[KenHigg]

Dim DB As Database
Set DB = OpenDatabase("MyDataBase.mdb")
DB.Execute ("MyQuery")
DB.Close

 

[aziz rasul]

Thanks for that Ken.

The query I wanted to run was a make table query, which created 'tblTemp'. When I ran your code it came with an error saying that 'tblTemp' already exists. How do I get around that?

 

[KenHigg]

Dim DB As Database
Set DB = OpenDatabase("MyDataBase.mdb")
DB.TableDefs.Delete ("tbltemp")
DB.Execute ("MyQuery")
DB.Close

???

 

[aziz rasul]

Thanks Ken.

 

[ecawilkinson]

Does tblTemp already exist in the other database at the time you run the query? If it does then of course you will get the error, you could check if it exists first, then delete it if it does:

Dim DB As Database
dim tdf as tabledef
Set DB = OpenDatabase("MyDataBase.mdb")

on error resume next
set tdf = db.tabledefs("tblTemp")

if not tdf is nothing then
    db.tabledefs.delete "tblTemp"
end if
on error goto 0

DB.Execute ("MAKE tblTemp query")
DB.Close

HTH,
Chris

 

[KenHigg]

Glad to help. And Chris's error checking stuff is a good idea -

 

[aziz rasul]

Good idea Chris.