Alright, I have a question.
At what point does the code execute the SQL behind the temporary query?
Here is the code from the website above: (I didn't put it as embedded code so I could show my thought process. You can get the code from the website above, it's the third set of code.)
The black text is the code, green is the comments from the author, red is my comments, bold dark red are my questions, bold blue is another question.
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
' filename without the .xls extension
' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)
Const strFileName As String = "PutEXCELFileNameHereWithoutdotxls" <<--sets the strFileName to be "PutEXCELFileNameHereWithoutdotxls"
Const strQName As String = "zExportQuery" <<--sets the strQName to be "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name <<--- This I'm not sure I understand. I think it's getting a list of the tables
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" <<-- I think this is selecting all the tables with a certain attribute, but i don't know what the attribute is
Set qdf = dbs.CreateQueryDef(strQName, strSQL) <<-- This creates a query named "zExportQuery" with the SQL of "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
qdf.Close <<-- this closes the query
strTemp = strQName <<-- This sets strTemp to be "zExportQuery"
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;" <<-- This changes the strSQL to select all the unique manager IDs from the EmployeesTable
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) <<-- The creates a recordset of the manager IDs
' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then <<--- this line and the next line make sure the process is starting at the beginning of the recordset
rstMgr.MoveFirst
Do While rstMgr.EOF = False <<-- Run the loop as long as the recordset is not at the end of the list
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value) <<-- this and the line above set the strMgr to equal the name of the manager in the ManagersTable based on the ManagerID
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";" <<--This and the line above change strSQL to select all the employees that have the ManagerID that is the current value in the recordset
Set qdf = dbs.QueryDefs(strTemp) <<-- I'm not entirely sure what this is doing, I think it's setting the qdf to be the query name of "zExportQuery"
qdf.Name = "q_" & strMgr <<--Again I'm not sure what this is doing, I think it's setting the qdf name to be Q {underscore} name of the Manager
strTemp = qdf.Name <<--This changes strTemp to equal the name one line above
qdf.SQL = strSQL <<-- This sets the SQL behind the qdf to be the SQL that selects the employees based on ManagerID
When I tried code that set my strSQL to update a test table, my code was:
(assume correct Dim statements)
strTemp = "123Test"
qdef was dbs.CreateQueryDef(strTemp)
qdef.Close
strSQL = **update table blah blah**
qdef.SQL = strSQL
qdef.Execute <<-- This line HAD to be in my code or the query didn't run ... it got created, but didn't run, then got deleted. So where in the code from the author is the query SQL actually run??
qdef.Close
set qdef = Nothing
dbs.QueryDefs.Delete (strTemp)
dbs.close
set dbs + Nothing
qdf.Close <<-- This closes the query
Set qdf = Nothing<<-- This changes qdf to be nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName" & strFileName & ".xls" <<--This and the line above export the query to a spreadsheet named "PutEXCELFileNameHereWithoutdotxls.xls"
rstMgr.MoveNext <<-- This moves to the next ManagerID in the recordset
Loop <<--This goes back to the DLookup line
End If <<-- This exits out of the If/Then statement when the recordset is at the end of the file
rstMgr.Close <<--Closes the recordset
Set rstMgr = Nothing <<--erases the recordset
dbs.QueryDefs.Delete strTemp <<--Deletes the query named "zExportQuery" Except I had to put my query title in parenthesis
dbs.Close
Set dbs = Nothing
Thank you very much for taking the time to help me!
Susie
Kansas