Getting current DB with an adp connection

  • Thread starter Thread starter keith.bateup
  • Start date Start date
K

keith.bateup

Hi,

I have set up an access conection to a external database on a sql
server through adp. 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
 
As Mr. Yuan said, Jet or DAO are not used for accessing the SQL-Server in
an ADP project. However, if you need it, you can create a DAO database
object and use it to perform some database operations. See the function
DAODatabase in the following thread:

http://groups.google.ca/group/micro...lnk=gst&q=daodatabase&rnum=2#7fb329fc3de36201

For using this object to export to Excel, I don't know but the following
article is of some interest. For more, search Google:

http://www.thescripts.com/forum/thread347901.html
 
DAO is roadkill.

anything that you need to do in DAO is easily done in ADO via a simple
query against sysobjects

what; do you need to loop through the queries in a database?

Access Data Projects made MDB obsolete almost a decade ago

-Aaron
ADP Nationalist
 
All that I am trying to to do is export a subform which has been
filtered, to excel. (The filter is done by a drop down box to select a
transaction to show in the subform. I then change the recordsource of
the subform as per filter options).

I can export the subform, its just it returns all records in the
subform, but I want to export the filter.
 
so create a view that is like your recordsource; but it actually has
the filter hardcoded in the sql statement

do you want this to open into a new sheet; or into an existing sheet?

-Aaron
 
Back
Top