DoCmd Transfer from ACCESS to Excel

[LDA via AccessMonster.com]

Hello,
I have a query that searchers for empty parameter results. I would like for
the results to be automatically transferred to the common server in a
standard location as a xls file


I tired looking at past post and afound this code, however am getting
invalid
procedure at set dbs=currentdb. I tried wipping entire line out and inserted
the db name SMV8 However recieved same error.

code:

Option Compare Database

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String

'set dbs = CurrentDb
' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT DISTINCT Results.OrderID, Results.SampleNumber,
Duke_Samples_Coal.BottleNumber, OrderDetails_1.Site FROM OrderDetails,
OrderDetails AS OrderDetails_1 INNER JOIN (Duke_Samples_Coal INNER JOIN
Results ON (Duke_Samples_Coal.OrderID = Results.OrderID) AND
(Duke_Samples_Coal.SampleNumber = Results.SampleNumber)) ON (OrderDetails_1.
OrderID = Results.OrderID) AND (OrderDetails_1.SampleNumber = Results.
SampleNumber)"
WHERE (((Results.Test) = "ADLOSS") And ((Results.ResultStatus) < 1))

strQDF = "_TempQuery_"
Set qdfTemp = SMv8.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for
the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF, "C:\Documents and Settings\ldanaga\Desktop\TrialTransfer.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

thanks,

lda

 

[Ken Snell MVP]

Open the References (Visual Basic Editor). Make sure that the reference to
the Microsoft DAO 3.x Object Library (where x is a number) is checked.