Saving queries to backend database

[Rob Parker]

Running Access 2003 SP2 (11.6566.8132) under WinXP SP2, with Jet
4.0.8618.0

I have the following code, to export queries to a linked database:

Public Sub ExportQuery()
Dim dbs As DAO.Database
Dim dbsOthr As DAO.Database
Dim qry As DAO.QueryDef
Dim accApp As New Access.Application

'GetDatabase returns the full path/filename of backend file

accApp.OpenCurrentDatabase GetDatabase, False
Set dbs = DBEngine(0)(0)
Set dbsOthr = accApp.DBEngine(0)(0) 'Error at this line

Set qry = dbs.QueryDefs("qryFoundEffortByMonth_BasedonNOW")
If bQryExists(dbsOthr, qry.Name) Then
dbsOthr.QueryDefs.Delete qry.Name
End If
dbsOthr.CreateQueryDef qry.Name, qry.SQL
...

dbs.Close
dbsOthr.Close
Set qry = Nothing
Set dbs = Nothing
Set dbsOthr = Nothing
accApp.CloseCurrentDatabase
accApp.Quit
Set accApp = Nothing
End Sub

This gives a runtime error at the line shown, with the error message
"Method 'DBEngine' of object '_Application' failed".

This code previously ran perfectly well. However, it hasn't been used
for a couple of years - and it may last have been used when running
Access 2000, rather than A2003.

Any ideas on why this now fails, and how to fix it, would be
gratefully received.

TIA,

Rob

 

[Ken Snell \(MVP\)]

Don't have an answer for you, Rob, as to what might be wrong with that line.
But when copying objects from one ACCESS db to another, it's much easier to
use TransferDatabase method.

 

[Rob Parker]

Don't have an answer for you, Rob, as to what might be wrong with that line.
But when copying objects from one ACCESS db to another, it's much easier to
use TransferDatabase method.

Thanks for the suggestion, Ken.

I'll have a look at that, and see if I can eliminate this failing
code.

Rob

 

[Rob Parker]

Thanks for the suggestion, Ken.

I'll have a look at that, and see if I can eliminate this failing
code.

Rob

Thanks muchly, Ken.

Works beautifully. And it goes from a huge chunk of code to three
lines (one for each query I need to export). And it doesn't even get
hassled, or append something like a (1) to each query, if there's a
previous version there. Exactly what I needed.

Rob