jezskill
asked on
Export Query recordset to Excel Template using ADO
I am desparately trying to export a query recordset to an excel template without success. The query is has parmeters linked to form objects, which is where I might be going wrong but need some pointers.
So in my vba code , I need to set a recordset connection to the access query "qryHFAuditsDue1"
and then create instance of excel workbook from the template and paste recordset from cell range a2 down (using 'copyfromrecordset' ?)
I have attached my code which has been round the houses and am currently getting an invalid SQL Statement Error.
Please help
So in my vba code , I need to set a recordset connection to the access query "qryHFAuditsDue1"
and then create instance of excel workbook from the template and paste recordset from cell range a2 down (using 'copyfromrecordset' ?)
I have attached my code which has been round the houses and am currently getting an invalid SQL Statement Error.
Please help
Public Sub AuditsDue1_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Excel.Application
'Dim oExcel As Object
Dim sFullPath As String
'Dim sPath As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set conn = CurrentProject.Connection
rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
' Create a new Excel instance
'Set oXL = CreateObject("Excel.Application")
Set oXL = New Excel.Application
' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0
' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\Temp\AuditsDue.xlt"
' Open it
With oXL
.Visible = True
.Workbooks.Add (sFullPath)
.Cells(2, 1).CopyFromRecordset rst
End With
rst.Close
Set rst = Nothing
ErrExit:
Set oXL = Nothing
Exit Sub
ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
That way you can set and make sure the variables you say are coming from form values actually have a value and so on.
Building the SQL dynamically is certainly an option with some advantages.
You can, of course, still use the query though.
You just need to explicitly evaluate the parameter values as the expression service can't do that for you through a data access API such as ADO.
You could just use a UDF to open your recordset for you such as shown here.
Cheers.
You can, of course, still use the query though.
You just need to explicitly evaluate the parameter values as the expression service can't do that for you through a data access API such as ADO.
You could just use a UDF to open your recordset for you such as shown here.
Cheers.
ASKER
this is my sql for the query recordset
strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpRes p.JobDescr iption, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpRes p ON tblJob.JobDesc = tblJobDescriptionLookUpRes p.JobDescr iptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport] .[Calendar 1] And [forms]![frmInvoiceReport] .[Calendar 2]) AND ((tblJobDescriptionLookUpR esp.Planne dResponsiv e)='"A"')) ;"
The vba code window gives me an error with regards to the last condition and highlights the "A" witha complie error expected: end of statement. should the A not be encapsulated in double quotes?
strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpRes
The vba code window gives me an error with regards to the last condition and highlights the "A" witha complie error expected: end of statement. should the A not be encapsulated in double quotes?
You shouldn't try and build an SQL statement like that - it's just going to be a nightmare to debug.
Also, I'm a little confused by this, there is no SQL statement in the code you posted.
I'm also not sure that that query would work using ADO.
That's because of the parameters, if you were to use that SQL for a query or report in Access you would at least have to have the form 'frmInvoiceReport' open.
Anyway, try something like this for the SQL, it will at least make it easier to debug.
Also, I'm a little confused by this, there is no SQL statement in the code you posted.
I'm also not sure that that query would work using ADO.
That's because of the parameters, if you were to use that SQL for a query or report in Access you would at least have to have the form 'frmInvoiceReport' open.
Anyway, try something like this for the SQL, it will at least make it easier to debug.
Dim strSQL As String
strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
strSQL = strSQL & " FROM "
strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between [forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"
You had a query - but you've moved to dynamically built SQL??
Not a problem in itself - but not necessary, bearing in mind what's been said already about parameter evaluation.
Naturally the forms would need to be open.
If the parameters need to be supplied regardless then you'd need to use a user defined function to supply the criteria instead.
If you're going with SQL in VBA then just concatenate the values in.
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & [forms]![frmInvoiceReport] .[Calendar 1] & " AND " & [forms]![frmInvoiceReport] .[Calendar 2] & ") AND (tblJobDescriptionLookUpRe sp.Planned Responsive ='A')"
Cheers
Not a problem in itself - but not necessary, bearing in mind what's been said already about parameter evaluation.
Naturally the forms would need to be open.
If the parameters need to be supplied regardless then you'd need to use a user defined function to supply the criteria instead.
If you're going with SQL in VBA then just concatenate the values in.
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & [forms]![frmInvoiceReport]
Cheers
ASKER
Thanks for the responses, I shall try your methods over th weekend and respond accordingly.
Ah, just noticed "Calendar1". So these are dates?
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([forms]![frmInvoice Report]![C alendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([forms]![frmInvoice Report].[C alendar2], "\#yyyy\-mm\-dd\#") & ") AND (tblJobDescriptionLookUpRe sp.Planned Responsive ='A')"
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([forms]![frmInvoice
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok , this is the current code I have, unfortunately I am still getting an invalid SQL statement error. The form is open which references the date values (which have no time values) . Not sure what I should try now?
Public Sub AuditsDue1_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Excel.Application
'Dim oExcel As Object
Dim sFullPath As String
'Dim sPath As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set conn = CurrentProject.Connection
'strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport].[Calendar1] And [forms]![frmInvoiceReport].[Calendar2]) AND ((tblJobDescriptionLookUpResp.PlannedResponsive)='A'));"
Dim strSQL As String
strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
strSQL = strSQL & " FROM "
strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#") & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"
rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
' Create a new Excel instance
'Set oXL = CreateObject("Excel.Application")
Set oXL = New Excel.Application
' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0
' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\Temp\AuditsDue.xlt"
' Open it
With oXL
.Visible = True
.Workbooks.Add (sFullPath)
.Cells(2, 1).CopyFromRecordset rst
End With
rst.Close
Set rst = Nothing
ErrExit:
Set oXL = Nothing
Exit Sub
ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
The syntax looks fine.
I'd suggest the old standard, to print out the SQL (Debug.Print strSQL) and execute that definition in a new query window.
See if it parses. Nothing stands out to me.
I'd suggest the old standard, to print out the SQL (Debug.Print strSQL) and execute that definition in a new query window.
See if it parses. Nothing stands out to me.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Apologies for delay, I am still trying to carve out some time to test.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
strSQL = "Select............" '(whatever your sql statement should be)
rst.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly