[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

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
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

Open in new window

0
jezskill
Asked:
jezskill
  • 5
  • 4
  • 2
  • +3
3 Solutions
 
shaydieCommented:
Have you tried building your sql statement in the code rather than use the query?

strSQL = "Select............" '(whatever your sql statement should be)
rst.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
0
 
shaydieCommented:
That way you can set and make sure the variables you say are coming from form values actually have a value and so on.
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jezskillAuthor Commented:
this is my sql for the query recordset

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"'));"

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?
0
 
NorieVBA ExpertCommented:
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.
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')"

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
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].[Calendar1] & " AND " & [forms]![frmInvoiceReport].[Calendar2] & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Cheers
0
 
jezskillAuthor Commented:
Thanks for the responses, I shall try your methods over th weekend and respond accordingly.
0
 
Leigh PurvisDatabase DeveloperCommented:
Ah, just noticed "Calendar1". So these are dates?

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')"

0
 
Leigh PurvisDatabase DeveloperCommented:
Which is often preferably created as

strSQL = strSQL & " WHERE (tblJob.DateScheduled >= " & Format([forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND tblJob.DateScheduled < " & Format([forms]![frmInvoiceReport].[Calendar2] + 1, "\#yyyy\-mm\-dd\#")  & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

If your DateScheduled may contain time component parts too.
0
 
jezskillAuthor Commented:
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

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
NorieVBA ExpertCommented:
One thing I would definitely check is the parantheses.

I don't know where the SQL came from but there seems to be quite a few of them kicking about.

I'm sure most of them are needed but there are times when you copy SQL that's been generated it includes some you don't need.

That's usually not a problem if you are just going to use the SQL as is without adding criteria or values.

The best way to test it is to do what LPurvis suggests.

You might want to add another couple of variables for the dates.
Dim strSQL As String
Dim strStartDate As String
Dim strEndDate As String
 
strStartDate = Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#")
strenddatee Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#")
 
    
   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 " & strStartDate & " AND " & strEndDate & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Open in new window

0
 
jezskillAuthor Commented:
Apologies for delay, I am still trying to carve out some time to test.
0
 
nathanielIT ConsultantCommented:
When I tried reconstructing the SQL string (i tested the content of the SQL string from the immediate pane), I think one of the errors are missing parenthesis (after Between and last line):

It should be:

WHERE (tblJob.DateScheduled Between ([forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A'))
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now