Re: Make Multiple Tables via Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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" wrote:

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" <MichaelV@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BA5564D8-1C5C-410A-A2A6-7E0D6D77F0D3@xxxxxxxxxxxxxxxx
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" wrote:

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

"Michael_V" <Michael_V@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CB2D4B74-5CC7-469F-B141-655D554C3401@xxxxxxxxxxxxxxxx
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?







.

Relevant Pages

  • Re: Query to Filter by Dates, Using Form
    ... This is the SQL that works: ... Dim strDateCondition As String ... drop the stored query and create a new one with the SQL you have built? ...
    (microsoft.public.access.queries)
  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)
  • RE: External data import
    ... 'Purpose of this VBA program is to find and list all Queries ... Dim i As Integer ... Dim strQueryParameters As String ... Dim strRangeName As String ...
    (microsoft.public.excel.misc)
  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • Re: Selecting certain items for a report
    ... I created the query called tmpSelectProducts ... > highlights the Dim qdf as DAO.QueryDef line. ... > Dim StrWhere As String ... > End Sub ...
    (microsoft.public.access.forms)