Using Access VBA with SQL and Excell

[JimReid]

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.

[CJ_London]

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

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

[JimReid]

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.

[CJ_London]

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

[JimReid]

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.

[CJ_London]

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

[davegri]

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:

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

[ssanfu]

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

[JimReid]

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.

[JimReid]

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.

[davegri]

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.

[Micron]

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.

[JimReid]

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.

[CJ_London]

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?

[JimReid]

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