Make Multiple Tables via Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've tweaked some vb code which streamlines repetitive tasks. Essentially it
takes Table1 and parses it into individual tables based upon a list in Table2
then exports each new table to excel.

How do I replicate this process without making the tables?
 
Michael

If you have queries that are being used to "make tables", those are "Make
Table" queries, right? You could change them to simple Select queries.

Then, in your code, you could "export" the queries (what actually happens is
the records returned by the query is what is exported).

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for the quick response. I actually have a shart vb code doing a loop
which I've tweaked over time. How would I modify this? Everything is based
upon two tables but could be 1 table and 2 queries. Query1 provides the list
for parsing and Query2 the data to be parsed via 1 table.

Here's the code for a better view.

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "INTO [" & rstHQ.Fields("LVL3") & "] " _
& "FROM 000_Metastorm_Assignments " _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName, strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub
 
Michael

The SQL statement uses "SELECT INTO". This is a make table query, in SQL
form.

Try creating a new query, first in design view, then changing the view to
the SQL. If you create a simple Select query, the SQL will start out
"SELECT ... FROM ..." (no "INTO")

Can you get done what you need by exporting the new query (i.e., the new SQL
statement)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
Jeff,

Thanks for the quick response. I actually have a shart vb code doing a
loop
which I've tweaked over time. How would I modify this? Everything is based
upon two tables but could be 1 table and 2 queries. Query1 provides the
list
for parsing and Query2 the data to be parsed via 1 table.

Here's the code for a better view.

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "INTO [" & rstHQ.Fields("LVL3") & "] " _
& "FROM 000_Metastorm_Assignments " _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName,
strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

Jeff Boyce said:
Michael

If you have queries that are being used to "make tables", those are "Make
Table" queries, right? You could change them to simple Select queries.

Then, in your code, you could "export" the queries (what actually happens
is
the records returned by the query is what is exported).

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Jeff,

In theory that would work but I'm not getting it right. Apparently I do not
have the correct syntax - got error code 3142 Characters found after the end
of the SQL statement. What am I missing or adding?

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "SELECT [000_Metastorm_Assignments].*" _
& "FROM 000_Metastorm_Assignments;" _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName, strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

Jeff Boyce said:
Michael

The SQL statement uses "SELECT INTO". This is a make table query, in SQL
form.

Try creating a new query, first in design view, then changing the view to
the SQL. If you create a simple Select query, the SQL will start out
"SELECT ... FROM ..." (no "INTO")

Can you get done what you need by exporting the new query (i.e., the new SQL
statement)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
Jeff,

Thanks for the quick response. I actually have a shart vb code doing a
loop
which I've tweaked over time. How would I modify this? Everything is based
upon two tables but could be 1 table and 2 queries. Query1 provides the
list
for parsing and Query2 the data to be parsed via 1 table.

Here's the code for a better view.

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "INTO [" & rstHQ.Fields("LVL3") & "] " _
& "FROM 000_Metastorm_Assignments " _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName,
strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

Jeff Boyce said:
Michael

If you have queries that are being used to "make tables", those are "Make
Table" queries, right? You could change them to simple Select queries.

Then, in your code, you could "export" the queries (what actually happens
is
the records returned by the query is what is exported).

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've tweaked some vb code which streamlines repetitive tasks.
Essentially
it
takes Table1 and parses it into individual tables based upon a list in
Table2
then exports each new table to excel.

How do I replicate this process without making the tables?
Click to expand...
Click to expand...
Click to expand...
 
Although I've changed the code I now get (Error 3065) You tried to use the
Execute method with a select query. The Execute method is valid only with
action queries. Select queries contain a SELECT statement and can return
records; action queries do not.

This all leads back to my original question, can I use the
DoCmd.TransferSpreadsheet on queries? I probably can but fail to get the code
right.

Other suggestions?


Michael_V said:
Jeff,

In theory that would work but I'm not getting it right. Apparently I do not
have the correct syntax - got error code 3142 Characters found after the end
of the SQL statement. What am I missing or adding?

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "SELECT [000_Metastorm_Assignments].*" _
& "FROM 000_Metastorm_Assignments;" _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName, strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

Jeff Boyce said:
Michael

The SQL statement uses "SELECT INTO". This is a make table query, in SQL
form.

Try creating a new query, first in design view, then changing the view to
the SQL. If you create a simple Select query, the SQL will start out
"SELECT ... FROM ..." (no "INTO")

Can you get done what you need by exporting the new query (i.e., the new SQL
statement)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
Jeff,

Thanks for the quick response. I actually have a shart vb code doing a
loop
which I've tweaked over time. How would I modify this? Everything is based
upon two tables but could be 1 table and 2 queries. Query1 provides the
list
for parsing and Query2 the data to be parsed via 1 table.

Here's the code for a better view.

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstHQ.EOF

'Make tables
strSQL = "SELECT *" _
& "INTO [" & rstHQ.Fields("LVL3") & "] " _
& "FROM 000_Metastorm_Assignments " _
& "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstHQ.Fields("LVL3") & "' "
CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName,
strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

:

Michael

If you have queries that are being used to "make tables", those are "Make
Table" queries, right? You could change them to simple Select queries.

Then, in your code, you could "export" the queries (what actually happens
is
the records returned by the query is what is exported).

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've tweaked some vb code which streamlines repetitive tasks.
Essentially
it
takes Table1 and parses it into individual tables based upon a list in
Table2
then exports each new table to excel.

How do I replicate this process without making the tables?
Click to expand...
Click to expand...
Click to expand...
 
This all leads back to my original question, can I use the
DoCmd.TransferSpreadsheet on queries?
Click to expand...

Yes... but you don't execute the query. Just transfer it:

Dim db As DAO.Database
Dim qd As DAO.Querydef
strSQL = <your SQL string here, without an INTO>

Set db = CurrentDb
Set qd = db.CreateQuerydef("ExcelExport", strSQL) ' create a Query
' named ExcelExport
DoCmd.TransferSpreadsheet acExport, 8, "ExcelExport", strExcelFileName


John W. Vinson[MVP]
 
Thanks John, but now I'm more confused and frustrated than before.

I understand the concept but the execution is not there. I can name the
query ExcelExport (or anything I want) but where is the loop to make multiple
queries? Based upon which list of fields? Am I adding this to my existing
code or replacing it?

You've answered the question very efficiently but my meager experience is
far shy from understanding where to insert or modify the code.

Can you elaborate a bit more on your explanation?
 
Michael

I'll presume to jump back in. The code John offered handles exporting one
query (the rows it returns).

If you want to have that same code run multiple times, you'd need to embed
it (John's code) inside a loop that feeds the different parameters to the
SQL string used. Didn't your original code include just such a loop?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff - thanks for jumping back in. Yes, I agree and feel better that I
understood his response only handled one table - I have macros for that type
of export. Thanks again for your help, I know this is tricky because I
understand the data and this is just code with no meaning or definitions
behind it.

Okay - here's the code with my comments in [].

Private Sub cmdMakeTablesDOS_Click()

Dim strTableName As String
Dim strExcelFileName As String
Dim strSQL As String
[ADD] Dim db As DAO.Database
[ADD] Dim qd As DAO.Querydef
Dim rstHQ As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the ACM names
strSQL = "Select LVL3 from 000_DOSs"
Set rstHQ = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
[ADD] Set db = CurrentDb
[ADD] Set qd = db.CreateQuerydef("[my query name to export]", strSQL) '

Do Until rstHQ.EOF

[REMOVE] 'Make tables
[REMOVE] strSQL = "SELECT *" _
[REMOVE] & "INTO [" & rstHQ.Fields("LVL3") & "] " _
[REMOVE] & "FROM 000_Metastorm_Assignments " _
[REMOVE] & "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
[REMOVE] rstHQ.Fields("LVL3") & "' "
[REMOVE] CurrentDb.Execute strSQL

'Export to Excel
strTableName = rstHQ.Fields("LVL3").Value
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strTableName, strExcelFileName

'Do it again
rstHQ.MoveNext
Loop

End Sub

Does that look right? I'm no programmer so that might be a bit of a
hinderance - i know enough to be dangerous (ha! understatement) but can read
and understand what is supposed to happen...for the most part. Comments?
Suggestions?
 
Jeff,

I started over. The following code works well to export 1 query. Can you
assist me on how export specific chunks of data based upon a specific field?
I've commented out several lines. Thanks.

Private Sub cmdExportToExcel_Click()

Dim strQueryName As String
Dim strExcelFileName As String
Dim strSQL As String
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

' Open a recordset of all the ACM names
' strSQL = "Select LVL3 from LVL3_List"
' Set rstList = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

' Do Until rstList.EOF

strSQL = "SELECT [Metastorm_Assignments].*" _
& "FROM Metastorm_Assignments "

'Make tables
' strSQL = "SELECT *" _
' & "INTO [" & rstList.Fields("LVL3") & "] " _
' & "FROM 000_Metastorm_Assignments " _
' & "WHERE [000_Metastorm_Assignments].[LVL3] = '" &
rstList.Fields("LVL3") & "' "
' CurrentDb.Execute strSQL

' 'Export to Excel
' strQueryName = rstList.Fields("LVL3").Value
strQueryName = "Metastorm_Assignments"
strExcelFileName = SaveToDir & " Metastorm Assignments " &
Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strQueryName, strExcelFileName

' 'Do it again
' rstList.MoveNext
' Loop

End Sub
 
This works BUT it is not qualifying on each name in my LVL3_List. I get an
export of all names on each list. It is not recognizing my Where clause.

Suggestions?

Private Sub cmdExportToExcel_Click()

Dim strQueryName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstList As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the DOS names
strSQL = "Select LVL3 from LVL3_List"
Set rstList = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstList.EOF

strSQL = "SELECT [Metastorm_Assignments].*" _
& "FROM Metastorm_Assignments " _
& "WHERE [Metastorm_Assignments].[LVL3] = '" &
rstList.Fields("LVL3") & "' "

strTableName = rstList.Fields("LVL3").Value
strQueryName = "Metastorm_Assignments"
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strQueryName, strExcelFileName

'Do it again
rstList.MoveNext
Loop

End Sub
 
Michael

I don't see where, inside your loop, you actually set the query to the SQL
statement. I see that you have a variable "strQueryName", but where do you
tell Access to create that query with that name?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That's part of my problem, how do i do that without making a table?

Any suggested code?

Jeff Boyce said:
Michael

I don't see where, inside your loop, you actually set the query to the SQL
statement. I see that you have a variable "strQueryName", but where do you
tell Access to create that query with that name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
This works BUT it is not qualifying on each name in my LVL3_List. I get an
export of all names on each list. It is not recognizing my Where clause.

Suggestions?

Private Sub cmdExportToExcel_Click()

Dim strQueryName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstList As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the DOS names
strSQL = "Select LVL3 from LVL3_List"
Set rstList = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstList.EOF

strSQL = "SELECT [Metastorm_Assignments].*" _
& "FROM Metastorm_Assignments " _
& "WHERE [Metastorm_Assignments].[LVL3] = '" &
rstList.Fields("LVL3") & "' "

strTableName = rstList.Fields("LVL3").Value
strQueryName = "Metastorm_Assignments"
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strQueryName,
strExcelFileName

'Do it again
rstList.MoveNext
Loop

End Sub
Click to expand...
Click to expand...
 
Michael

Try searching on "MS Access create querydef" at Google or such.

Here's a code segment snipped out of an article from the nice folks at FMS,
Inc.:

Private Sub ParameterQuery (strState As String)
' Input : strState Name of state to select records
Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset
Const strQueryName = "Advanced: Parameters"
Set db = CurrentDB()
Set qry = db.OpenQueryDef(strQueryName)
qry.parameters("Enter the State where Patients Live") = strState
Set rs = qry.OpenRecordset() ' Open recordset on the query
Do While Not rs.eof
Debug.Print ("ID: " & rs![ID] & " State: " & rs![State])
rs.MoveNext
Loop
rs.Close
qry.Close
db.Close
End Sub

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
That's part of my problem, how do i do that without making a table?

Any suggested code?

Jeff Boyce said:
Michael

I don't see where, inside your loop, you actually set the query to the
SQL
statement. I see that you have a variable "strQueryName", but where do
you
tell Access to create that query with that name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
This works BUT it is not qualifying on each name in my LVL3_List. I get
an
export of all names on each list. It is not recognizing my Where
clause.

Suggestions?

Private Sub cmdExportToExcel_Click()

Dim strQueryName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstList As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the DOS names
strSQL = "Select LVL3 from LVL3_List"
Set rstList = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstList.EOF

strSQL = "SELECT [Metastorm_Assignments].*" _
& "FROM Metastorm_Assignments " _
& "WHERE [Metastorm_Assignments].[LVL3] = '" &
rstList.Fields("LVL3") & "' "

strTableName = rstList.Fields("LVL3").Value
strQueryName = "Metastorm_Assignments"
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strQueryName,
strExcelFileName

'Do it again
rstList.MoveNext
Loop

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Okay - I've got it working via a simpiler code AND added two items 1) in the
form i have a combo box linked to my list of qualifiers and 2) in the query
the criteria filters on the value of the combo box.

Private Sub cmdExportACM_Click()

Dim strFileName As String
Dim i As Integer
i = 0

'MsgBox Me.ComboLVL4.ListCount

strFileName = " Metastorm Assignments "
Do While i < Me.ComboLVL4.ListCount

Me.ComboLVL4.Value = Me.ComboLVL4.ItemData(i)
'MsgBox Me.ComboLVL4.Value
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Metastorm_Assignments_ACM", "C:\Data\" & Me.ComboLVL4.ItemData(i) &
strFileName & Format(Date, "yyyy-mm-dd"), True
i = i + 1

Loop
MsgBox "Completed", vbInformation, ""

End Sub

I appreciate all that helped and provided input.

Jeff Boyce said:
Michael

Try searching on "MS Access create querydef" at Google or such.

Here's a code segment snipped out of an article from the nice folks at FMS,
Inc.:

Private Sub ParameterQuery (strState As String)
' Input : strState Name of state to select records
Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset
Const strQueryName = "Advanced: Parameters"
Set db = CurrentDB()
Set qry = db.OpenQueryDef(strQueryName)
qry.parameters("Enter the State where Patients Live") = strState
Set rs = qry.OpenRecordset() ' Open recordset on the query
Do While Not rs.eof
Debug.Print ("ID: " & rs![ID] & " State: " & rs![State])
rs.MoveNext
Loop
rs.Close
qry.Close
db.Close
End Sub

Regards

Jeff Boyce
Microsoft Office/Access MVP

Michael_V said:
That's part of my problem, how do i do that without making a table?

Any suggested code?

Jeff Boyce said:
Michael

I don't see where, inside your loop, you actually set the query to the
SQL
statement. I see that you have a variable "strQueryName", but where do
you
tell Access to create that query with that name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This works BUT it is not qualifying on each name in my LVL3_List. I get
an
export of all names on each list. It is not recognizing my Where
clause.

Suggestions?

Private Sub cmdExportToExcel_Click()

Dim strQueryName As String
Dim strExcelFileName As String
Dim strSQL As String
Dim rstList As DAO.Recordset
Const SaveToDir = "C:\Documents and Settings\bb2130\Desktop\Sheets\"

'Open a recordset of all the DOS names
strSQL = "Select LVL3 from LVL3_List"
Set rstList = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstList.EOF

strSQL = "SELECT [Metastorm_Assignments].*" _
& "FROM Metastorm_Assignments " _
& "WHERE [Metastorm_Assignments].[LVL3] = '" &
rstList.Fields("LVL3") & "' "

strTableName = rstList.Fields("LVL3").Value
strQueryName = "Metastorm_Assignments"
strExcelFileName = SaveToDir & strTableName & " Metastorm
Assignments " & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, strQueryName,
strExcelFileName

'Do it again
rstList.MoveNext
Loop

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top