Problem with CurrentDB

[keith.bateup]

Hi,

I have set up an access conection to a external database on a sql
server. I am trying to export a filtered subform in ms access to excel
but I understand the best way to do it is to set up a query and change
the sql of the that query to filter the subform and then export the
query.

The problem is that when I set the db = CurrentDb and try to set a
QueryDef using db it throws an error "Run-time error 91: Object
variable or With Block variable not set.

I am out of ideas, has anyone experianced this problem when connecting
to an external data source.

Code:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

' Get the existing export query, or create it if
' it doesn't exist.

On Error Resume Next
Set qdf = db.QueryDefs("qryDataExport")
If qdf Is Nothing Then
Set qdf = db.CreateQueryDef("qryDataExport")
End If

If qdf Is Nothing Then
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
Exit Sub
End If

On Error GoTo 0
'Here we have got a reference to the export query.
'Set its SQL property to the query we want to export.

qdf.SQL = sqlOutput ' sqlOutput is the query to filter the
subform.
Set qdf = Nothing ' we're done manipulating it.

DoCmd.OutputTo acOutputQuery, "qryDataExport", acFormatXLS,
"export.xls", True

 

[Brendan Reynolds]

Is this code running in the Access application, not some other application
such as Excel? If so, is the Access application an MDB, not an ADP?

 

[keith.bateup]

Running in access. (Office 2003)

It is an ADP! Is that a problem?

 

[Brendan Reynolds]

CurrentDB returns Nothing in an ADP. CurrentDB returns a reference to the
current JET database, and in an ADP there is no current JET database. The
nearest equivalent to CurrentDB in an ADP would be
CurrentProject.Connection, but it is not a one-to-one correspondence, that
is to say the two objects do not share exactly the same properties and
methods.

I'm not sure what the solution to your problem would be in an ADP. If no one
else answers here, you might want to ask the question in the ADP newsgroup,
microsoft.public.access.adp.sqlserver