Help with Select ... Into in VBA

R

RocketDude

Hi,

I want to create a table in a subroutine in VBA using the Select ... Into
construct, but I keep getting "Run-time error '3067': Query input must
contain at least one table or query" for the following setup:

Public Sub DoSQL()

Dim SQL As String

SQL = "SELECT [NetSales].Brand, [NetSales].Distributor," & _
"([NetSales]![Jan02]+[NetSales]![Feb02]+[NetSales]![Mar02]+[NetSales]![Apr02]+[NetSales]![May02]+[NetSales]![Jun02]+[NetSales]![Jul02]+[NetSales]![Aug02]+[NetSales]![Sept02]+[NetSales]![Oct02]+[NetSales]![Nov02]+[NetSales]![Dec02])
AS 2002," & _
"([NetSales]![Jan03]+[NetSales]![Feb03]+[NetSales]![Mar03]+[NetSales]![Apr03]+[NetSales]![May03]+[NetSales]![Jun03]+[NetSales]![Jul03]+[NetSales]![Aug03]+[NetSales]![Sept03]+[NetSales]![Oct03]+[NetSales]![Nov03]+[NetSales]![Dec03])
AS 2003," & _
"([NetSales]![Jan04]+[NetSales]![Feb04]+[NetSales]![Mar04]+[NetSales]![Apr04]+[NetSales]![May04]+[NetSales]![Jun04]+[NetSales]![Jul04]+[NetSales]![Aug04]+[NetSales]![Sept04]+[NetSales]![Oct04]+[NetSales]![Nov04]+[NetSales]![Dec04])
AS 2004" & _
" INTO tblNetSalesYr" & _
"FROM (Distributor INNER JOIN (BrandCodes INNER JOIN [NetSales]
ON BrandCodes.Brand = [NetSales].Brand) ON Distributor.Distributor =
[NetSales].Distributor) INNER JOIN NetSales ON (BrandCodes.Brand =
NetSales.Brand) AND (Distributor.Distributor = NetSales.Distributor);"

DoCmd.RunSQL SQL

End Sub

"NetSales", "Distributor", "BrandCodes" are all tables.

The above SQL works correctly if I run it directly in Access as a query. All
help figuring out what is causing the above error will be greatly
appreciated.


TIA,

Matthew
 
K

Ken Snell [MVP]

I think you're missing a space at the end of this string fragment:
" INTO tblNetSalesYr" & _

change it to
" INTO tblNetSalesYr " & _
 
R

RocketDude

Ken,

Thanks so much! That fixed the problem.

Quick follow-up question, how do I suppress the "You are about to paste XXX
rows into a new table?" message? As well as any messages if the table
already exists?

Thanks so much,

--
Matthew

Ken Snell said:
I think you're missing a space at the end of this string fragment:
" INTO tblNetSalesYr" & _

change it to
" INTO tblNetSalesYr " & _

--

Ken Snell
<MS ACCESS MVP>

RocketDude said:
Hi,

I want to create a table in a subroutine in VBA using the Select ... Into
construct, but I keep getting "Run-time error '3067': Query input must
contain at least one table or query" for the following setup:

Public Sub DoSQL()

Dim SQL As String

SQL = "SELECT [NetSales].Brand, [NetSales].Distributor," & _

"([NetSales]![Jan02]+[NetSales]![Feb02]+[NetSales]![Mar02]+[NetSales]![Apr02]+[NetSales]![May02]+[NetSales]![Jun02]+[NetSales]![Jul02]+[NetSales]![Aug02]+[NetSales]![Sept02]+[NetSales]![Oct02]+[NetSales]![Nov02]+[NetSales]![Dec02])
AS 2002," & _

"([NetSales]![Jan03]+[NetSales]![Feb03]+[NetSales]![Mar03]+[NetSales]![Apr03]+[NetSales]![May03]+[NetSales]![Jun03]+[NetSales]![Jul03]+[NetSales]![Aug03]+[NetSales]![Sept03]+[NetSales]![Oct03]+[NetSales]![Nov03]+[NetSales]![Dec03])
AS 2003," & _

"([NetSales]![Jan04]+[NetSales]![Feb04]+[NetSales]![Mar04]+[NetSales]![Apr04]+[NetSales]![May04]+[NetSales]![Jun04]+[NetSales]![Jul04]+[NetSales]![Aug04]+[NetSales]![Sept04]+[NetSales]![Oct04]+[NetSales]![Nov04]+[NetSales]![Dec04])
AS 2004" & _
" INTO tblNetSalesYr" & _
"FROM (Distributor INNER JOIN (BrandCodes INNER JOIN
[NetSales] ON BrandCodes.Brand = [NetSales].Brand) ON
Distributor.Distributor = [NetSales].Distributor) INNER JOIN NetSales ON
(BrandCodes.Brand = NetSales.Brand) AND (Distributor.Distributor =
NetSales.Distributor);"

DoCmd.RunSQL SQL

End Sub

"NetSales", "Distributor", "BrandCodes" are all tables.

The above SQL works correctly if I run it directly in Access as a query.
All help figuring out what is causing the above error will be greatly
appreciated.


TIA,

Matthew
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Put this step in your code just before you run the query:
DoCmd.SetWarnings False

Then put this step in your code just after you run the query:
DoCmd.SetWarnings True

Note that, if you use an error handler, and it is possible for the error
handler to send your code to a place other than the next step after the
query is run, then put the DoCmd.SetWarnings True step in the error handler
or the "exit" routine within the code. Otherwise, the warnings are turned
off and won't show again until you turn them back on in code.

--

Ken Snell
<MS ACCESS MVP>

RocketDude said:
Ken,

Thanks so much! That fixed the problem.

Quick follow-up question, how do I suppress the "You are about to paste
XXX rows into a new table?" message? As well as any messages if the table
already exists?

Thanks so much,

--
Matthew

Ken Snell said:
I think you're missing a space at the end of this string fragment:
" INTO tblNetSalesYr" & _

change it to
" INTO tblNetSalesYr " & _

--

Ken Snell
<MS ACCESS MVP>

RocketDude said:
Hi,

I want to create a table in a subroutine in VBA using the Select ...
Into construct, but I keep getting "Run-time error '3067': Query input
must contain at least one table or query" for the following setup:

Public Sub DoSQL()

Dim SQL As String

SQL = "SELECT [NetSales].Brand, [NetSales].Distributor," & _

"([NetSales]![Jan02]+[NetSales]![Feb02]+[NetSales]![Mar02]+[NetSales]![Apr02]+[NetSales]![May02]+[NetSales]![Jun02]+[NetSales]![Jul02]+[NetSales]![Aug02]+[NetSales]![Sept02]+[NetSales]![Oct02]+[NetSales]![Nov02]+[NetSales]![Dec02])
AS 2002," & _

"([NetSales]![Jan03]+[NetSales]![Feb03]+[NetSales]![Mar03]+[NetSales]![Apr03]+[NetSales]![May03]+[NetSales]![Jun03]+[NetSales]![Jul03]+[NetSales]![Aug03]+[NetSales]![Sept03]+[NetSales]![Oct03]+[NetSales]![Nov03]+[NetSales]![Dec03])
AS 2003," & _

"([NetSales]![Jan04]+[NetSales]![Feb04]+[NetSales]![Mar04]+[NetSales]![Apr04]+[NetSales]![May04]+[NetSales]![Jun04]+[NetSales]![Jul04]+[NetSales]![Aug04]+[NetSales]![Sept04]+[NetSales]![Oct04]+[NetSales]![Nov04]+[NetSales]![Dec04])
AS 2004" & _
" INTO tblNetSalesYr" & _
"FROM (Distributor INNER JOIN (BrandCodes INNER JOIN
[NetSales] ON BrandCodes.Brand = [NetSales].Brand) ON
Distributor.Distributor = [NetSales].Distributor) INNER JOIN NetSales ON
(BrandCodes.Brand = NetSales.Brand) AND (Distributor.Distributor =
NetSales.Distributor);"

DoCmd.RunSQL SQL

End Sub

"NetSales", "Distributor", "BrandCodes" are all tables.

The above SQL works correctly if I run it directly in Access as a query.
All help figuring out what is causing the above error will be greatly
appreciated.


TIA,

Matthew
Click to expand...
Click to expand...
Click to expand...
 
R

RocketDude

Ken,

Thanks again.

--
Matthew

Ken Snell said:
Put this step in your code just before you run the query:
DoCmd.SetWarnings False

Then put this step in your code just after you run the query:
DoCmd.SetWarnings True

Note that, if you use an error handler, and it is possible for the error
handler to send your code to a place other than the next step after the
query is run, then put the DoCmd.SetWarnings True step in the error
handler or the "exit" routine within the code. Otherwise, the warnings are
turned off and won't show again until you turn them back on in code.

--

Ken Snell
<MS ACCESS MVP>

RocketDude said:
Ken,

Thanks so much! That fixed the problem.

Quick follow-up question, how do I suppress the "You are about to paste
XXX rows into a new table?" message? As well as any messages if the table
already exists?

Thanks so much,

--
Matthew

Ken Snell said:
I think you're missing a space at the end of this string fragment:
" INTO tblNetSalesYr" & _

change it to
" INTO tblNetSalesYr " & _

--

Ken Snell
<MS ACCESS MVP>

Hi,

I want to create a table in a subroutine in VBA using the Select ...
Into construct, but I keep getting "Run-time error '3067': Query input
must contain at least one table or query" for the following setup:

Public Sub DoSQL()

Dim SQL As String

SQL = "SELECT [NetSales].Brand, [NetSales].Distributor," & _

"([NetSales]![Jan02]+[NetSales]![Feb02]+[NetSales]![Mar02]+[NetSales]![Apr02]+[NetSales]![May02]+[NetSales]![Jun02]+[NetSales]![Jul02]+[NetSales]![Aug02]+[NetSales]![Sept02]+[NetSales]![Oct02]+[NetSales]![Nov02]+[NetSales]![Dec02])
AS 2002," & _

"([NetSales]![Jan03]+[NetSales]![Feb03]+[NetSales]![Mar03]+[NetSales]![Apr03]+[NetSales]![May03]+[NetSales]![Jun03]+[NetSales]![Jul03]+[NetSales]![Aug03]+[NetSales]![Sept03]+[NetSales]![Oct03]+[NetSales]![Nov03]+[NetSales]![Dec03])
AS 2003," & _

"([NetSales]![Jan04]+[NetSales]![Feb04]+[NetSales]![Mar04]+[NetSales]![Apr04]+[NetSales]![May04]+[NetSales]![Jun04]+[NetSales]![Jul04]+[NetSales]![Aug04]+[NetSales]![Sept04]+[NetSales]![Oct04]+[NetSales]![Nov04]+[NetSales]![Dec04])
AS 2004" & _
" INTO tblNetSalesYr" & _
"FROM (Distributor INNER JOIN (BrandCodes INNER JOIN
[NetSales] ON BrandCodes.Brand = [NetSales].Brand) ON
Distributor.Distributor = [NetSales].Distributor) INNER JOIN NetSales
ON (BrandCodes.Brand = NetSales.Brand) AND (Distributor.Distributor =
NetSales.Distributor);"

DoCmd.RunSQL SQL

End Sub

"NetSales", "Distributor", "BrandCodes" are all tables.

The above SQL works correctly if I run it directly in Access as a
query. All help figuring out what is causing the above error will be
greatly appreciated.


TIA,

Matthew
Click to expand...
Click to expand...
Click to expand...
 
Top