Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30

    Using Access VBA with SQL and Excell

    I'm trying to the the following to work:
    1. Run internal SQL statement that create a record set
    2. Create Excel spreadsheet
    3. Steup thru record set populating Excel sheet.

    I have been able to create the SQL statement but don't know how to have the results available to step thru and add to Excell. The Excell spreadsheet was created and I can add to it but can't figure out how to step thru the results of the query. The query results should not be saved after the Excell is populated.

    Tried this:
    Sub CreateExcelFile()
    On Error GoTo ErrorHandler


    Dim XL As Excel.Application
    Dim WB As Excel.Workbook
    Dim WKS As Excel.Worksheet
    Dim i As Integer
    Dim lastrow As Integer
    Dim lastcol As Integer


    '************** get data to tbltemptest *********************
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTable As String
    strSQL = "Select * INTO " & strTable & " FROM 2021 "
    Set rst = rst.OpenRecordset(strSQL)
    strTable = "tblTempTest"
    'Delete the table if it exists
    DoCmd.DeleteObject acTable, strTable

    CurrentDb.Execute strSQL
    'Insert more code here to do something with temp table
    MsgBox rst.RecordCount
    GoTo NextStep
    ErrorHandler:
    If Err.Number = 7874 Then
    Resume Next 'Tried to delete a non-existing table, resume
    End If

    And the Set RST = throws me into the ErrorHandler.

    Anyone tried this before. Doesn't seem that difficult but I'm having a problem with it. I have to step thru the results of the query a row/record at a time.

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,842
    please use code tags to preserve indentation (highlight your code and hit the # button). It makes it easier to read

    Also if you get errors state what the error is (not just it's number)

    However I can tell you that you cannot open a recordset on an action query which is what your sql is trying to be. But as far as I can see strTable is a zls (zero length string) at the time you create sqlStr.

    Suggest your code needs to be more like

    Code:
    Dim strSQL As String
    Dim strTable As String
    
    strTable = "tblTempTest"
    strSQL = "Select * INTO " & strTable & " FROM 2021 "
    currentdb.execute(strSQL)
    or perhaps tblTempTest is a linked table to your excel file?

    In which case use the transferspreadsheet function - see this link https://docs.microsoft.com/en-us/off...ferspreadsheet

  3. #3
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    Ajax, Thanks for the reply. I'm trying to run an extract * of from table tbl2021 (some of my code was miskeyed) I thought I could put it in a temp table called "tblTempTest" and then open tblTempTest and step through the records(rows)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strTable As String
    strTable = "tblTempTest"
    strSQL = "Select * INTO " & strTable & " FROM tbl2021 "
    CurrentDb.Execute strSQL
    set rs.recordset = strTable

    Not sure what you mean about the # around the code but this code is closer to what I wanted. I would follow that by stepping through the records. The errormsg dosen't do anything with an error code just go there when there is an error.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,842
    Not sure what you mean about the # around the code
    when posting a response, on the ribbon the # is on far right just here

    with regards your code you are going about it the wrong way - look at the transferspreadsheet link I provided

  5. #5
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    The link that you sent is to transfer from excel to access. Not what I'm trying to do. I'm trying to run a vba query that extracts the data that I want and then setup through the results and populate a customer excel spreadsheet that I build based on the data in the query. i can get the query to work and I can build the spreadsheet, but I don't know how to step through the results of the query to populate the spreadsheet. I know in SQL you can create a temporary table with a & on the front of the name and it will delete when program ends so I thought maybe I could create a table from the query and then delete it when I'm done (prior to ending the program). Does that make sense? I hope I made it clear. I have a lot of manipulating to do after the spreadsheet is created so I can't just import the data I have to process it a record at a time. I appreciate your trying anyway.

    Got a little further. I got the query to work in Access. I put it in the program, and it says it can't find the database or the query and to check the names. I have a msgbox right before the execution of the query and it shows the correct database. The query name is correct also. I checked all the tables in the query, and they are correct (the query works standalone). Don't know where to look now. It looks like if I can get the query to work the rest will be a piece of cake.

    BTW I'm running Windows 11 but remoting to Windows 10 with Access 2016.
    Last edited by JimReid; 01-24-2022 at 02:16 PM. Reason: Mode some headway.

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,842
    The link that you sent is to transfer from excel to access.
    you use it form import and export. Read it properly
    I'm trying to run a vba query that extracts the data that I want and then setup through the results and populate a customer excel spreadsheet
    you can do it with one line of code

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,996
    Ajax has suggested alternate approaches, but to directly address your request to iterate thru contents of tbl2021

    There's no need to create an alternate temp table, you can iterate thru tbl2021 directly:

    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set DB = Currentdb
    set rs = db.openrecordset ("tbl2021")
    rs.movelast
    rs.movefirst
    Do While not rs.eof
    	if rs!field1 > 0 then
    		'do something
    		....
    	end if	
    	rs.movenext
    Loop
    set rs = nothing
    set db = nothing

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,603
    Quote Originally Posted by JimReid View Post
    I'm trying to the the following to work:
    1. Run internal SQL statement that create a record set
    2. Create Excel spreadsheet
    3. Steup thru record set populating Excel sheet.

    I have been able to create the SQL statement but don't know how to have the results available to step thru and add to Excell. The Excell spreadsheet was created and I can add to it but can't figure out how to step thru the results of the query. The query results should not be saved after the Excell is populated.
    <snip>

    <snip>
    Anyone tried this before. Doesn't seem that difficult but I'm having a problem with it. I have to step thru the results of the query a row/record at a time.
    See Ken Snell's site at http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    The specific page you should look at is

    Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA) -->> "Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file"


    This site has helped me a lot....


    Good luck with your project...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    I'm not looking to export or import from or to Excel. I have a sql statement that works standalone in the database but won't work in my code. Says the table(s) or query it can't find. Don't know why I can run it as a standalone SQL but can't run it within my VBA code.

  10. #10
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    The query I'm using actually hits 3 table files and creates what I want in the output. I just can't get the query to work within VBA. It actually gives me exactly what I'm looking for when I run it alone.

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,996
    The query I'm using actually hits 3 table files and creates what I want in the output. I just can't get the query to work within VBA. It actually gives me exactly what I'm looking for when I run it alone.
    If that's the problem, why didn't you say that in post #1? Post#1 queries a single table. Show the actual VBA that doesn't work.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,626
    I'm not looking to export or import from or to Excel.
    Well that's confusing...

    2. Create Excel spreadsheet
    3. Steup thru record set populating Excel sheet.

    I have been able to create the SQL statement but don't know how to have the results available to step thru and add to Excell. The Excell spreadsheet was created and I can add to it but can't figure out how to step thru the results of the query. The query results should not be saved after the Excell is populated.
    I believe you had your guidance and answer in post 2:
    However I can tell you that you cannot open a recordset on an action query which is what your sql is trying to be
    Your action query sql will run as a query; it will not run in vba and create a recordset.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    Davegri,
    I was trying to simplify the issue by only showing 1 table being used. The actual query is very complex, and I didn't want to confuse the issue. One person wrote that you cannot run a query in vba and then step through the record set. If that is true, I'm dead in the water.

    First, I tried to run the query as an 'inline' query and when that didn't work, I made it a permanent query and tried to call it. Neither seems to work so maybe it can't be done.

  14. #14
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,842
    you are not giving us anything to go with except poorly written examples and conflicting requirements.
    One person wrote that you cannot run a query in vba and then step through the record set
    of course you can - providing it is a select query, you are showing an action query. But you said the purpose was to populate an excel excel file. There is nothing I can see or you've told us that requires you to do that.

    Davegri provided example code for stepping through a recorset in post#7

    except it now appears to be not a table, but a query - does that query have any parameter prompts by any chance? if so, then that will be the reason it does not work



    The actual query is very complex, and I didn't want to confuse the issue.
    since the issue appears to be the query, don't see what else we can sat without seeing the sql - the whole sql and nothing but the sql

    I have a sql statement that works standalone in the database but won't work in my code. Says the table(s) or query it can't find.
    what is the actual error message?

  15. #15
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30
    Ajax,
    I appreciate your patience with me and trying to describe my problem. The idea of populating the Excel spreadsheet should not even be considered. The is a programming issue that I have covered before. It's not an import.

    Secondly at the end of this I will give the stored query that I'm trying to use. It works when I run it in Access but not when I try to run it in VBA (within Access). I thought that once ran I could then move to the beginning and step through the results but not sure how. I thought this would be a temporary set of records but it's possible I could (maybe?) create a permanent table of the results and then delete it when complete. Sorry for all the confusion. In my inapt way I was trying to make it simpler and I guess lost a lot in the translation. Here is the current stored query that I'm trying to use. Hope this helps.

    PARAMETERS [forms]![frmQtrSumRpt].[txtFWD] DateTime, [forms]![frmQtrSumRpt].[txtTWD] DateTime;
    SELECT tblReceipt.Route
    , tblReceipt.Driver
    , Format([tblReceipt].[date],"ddd") AS [Day]
    , Count(tblReceipt.Route) AS DCount
    , "Sweep" AS DIV
    , Sum(tblReceipt.TotalHours) AS TH
    , Sum(tblReceipt.RecActHrs) AS TR
    , [th]-[tr] AS TD
    , Sum(tblReceipt.TotalDollar) AS SumOfTotalDollar
    , Avg(tblReceipt.TotalHours) AS ATH
    , Avg(tblReceipt.RecActHrs) AS ARH
    , [td]/[dcount] AS AD
    , [sumoftotaldollar]/[th] AS APH
    FROM tblReceipt INNER JOIN
    tblRoutes ON tblReceipt.Route = tblRoutes.Route
    WHERE (((tblReceipt.Date)>=[forms]![frmRouteAvg].[txtFWD]
    And (tblReceipt.Date)<=[Forms]![frmRouteAvg].[txtTWD])
    AND ((tblReceipt.Helper1) Is Null)
    AND ((tblReceipt.Helper2) Is Null)
    AND ((tblReceipt.Training) Is Null)
    AND ((tblRoutes.Division)="sweep"))
    GROUP BY tblReceipt.Route
    , tblReceipt.Driver
    , Format([tblReceipt].[date],"ddd")
    , "Sweep"
    HAVING (((Count(tblReceipt.Route))>0))
    UNION ALL SELECT tblLIReceipt.Route
    , tblLIReceipt.Driver
    , Format([tblLIReceipt].[date],"ddd") AS [Day]
    , Count(tblLIReceipt.Route) AS DCount
    , "Interior" AS DIV
    , Sum(tblLIReceipt.TotalHours) AS TH
    , Sum(tblLIReceipt.RecActHrs) AS TR
    , [th]-[tr] AS TD
    , Sum(tblLIReceipt.TotalDollar) AS SumOfTotalDollar
    , Avg(tblLIReceipt.TotalHours) AS ATH
    , Avg(tblLIReceipt.RecActHrs) AS ARH
    , [td]/[dcount] AS AD
    , [sumoftotaldollar]/[th] AS APH
    FROM tblRoutes INNER JOIN
    tblLIReceipt ON tblRoutes.Route = tblLIReceipt.Route
    WHERE (((tblLIReceipt.Date)>=[forms]![frmRouteAvg].[txtFWD]
    And (tblLIReceipt.Date)<=[Forms]![frmRouteAvg].[txtTWD])
    AND ((tblLIReceipt.Helper1) Is Null)
    AND ((tblLIReceipt.Helper2) Is Null)
    AND ((tblRoutes.Division)="Interior"))
    GROUP BY tblLIReceipt.Route
    , tblLIReceipt.Driver
    , Format([tblLIReceipt].[date],"ddd")
    , "Interior"
    HAVING (((Count(tblLIReceipt.Route))>0));

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-06-2013, 10:43 AM
  2. Split access querry in several excell sheet
    By wcedeno in forum Queries
    Replies: 3
    Last Post: 05-26-2011, 01:31 PM
  3. Access waits for Excell
    By e.badin in forum Programming
    Replies: 3
    Last Post: 01-10-2011, 07:51 AM
  4. opening an excell file from out of access
    By FSCHAMP in forum Access
    Replies: 1
    Last Post: 01-05-2011, 10:17 AM
  5. import an excell spreadsheet in an access database
    By lmichaud in forum Import/Export Data
    Replies: 0
    Last Post: 11-03-2006, 08:29 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums