SQL code works in Access SQL window but not in VBA SQL code

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003

The following code works in the Access Query SQL-view window but when I try to run it in VBA, it
produces the following error (#3601):
"Too few parameters. Expected 1"


dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _
& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _
& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _
& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _
& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _
& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _
& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _
& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _
& " INTO CHOOSERev" _
& Chr(10) & "FROM CHOOSEData" & Chr(10) _
& " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)<>'7'));"


I copied the Access query which works! exactly from the SQL window (Except I had to parse the lines
with &, " etc.

I guess the syntax I am using is incorrect after the .Execute command. (Note the . INTO CHOOSERev
10th line

Any help greatly appreciated!

EagleOne
 
Bottom line, I am attempting to create, in "dbs" project, a new table CHOOSERev from CHOOSEData
 
Bottom line, I am attempting to create, in "dbs" project, a new table CHOOSERev from CHOOSEData











- Show quoted text -
Click to expand...



Sometimes Access gives strange error messages.

I would look at the following:

--There is no space after " INTO CHOOSERev".
--There is a line-feed character before and after "FROM CHOOSEData".
--There is a semi-colon embedded in the sql string.
--There is a limit to the length of an sql statement and you are
wasting
part of that limited number of characters by repeating
"CHOOSEDATA.".
( I think the limit is 255 characters )
 
Try replacing Chr(10) with VbCRLF
or with
Chr(13) & Chr(10)


dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _
& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX,
CHOOSEData.AAA_UIC, " _
& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _
& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _
& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _
& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _
& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, "
& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _
& " INTO CHOOSERev" _
& vbcrlf & "FROM CHOOSEData" & vbcrlf _
& " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)<>'7'));"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Sometimes Access gives strange error messages.

I would look at the following:

--There is no space after " INTO CHOOSERev".
--There is a line-feed character before and after "FROM CHOOSEData".
--There is a semi-colon embedded in the sql string.
--There is a limit to the length of an sql statement and you are
wasting
part of that limited number of characters by repeating
"CHOOSEDATA.".
( I think the limit is 255 characters )
Click to expand...

try this

dbs.Execute _
" a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _
" a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _
" a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _
" a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _
" a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _
" INTO [CHOOSERev] FROM [CHOOSEData]" & _
" WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _
" AND ((a.LTrim_REG) <> '7'));"
 
Louis,

I made all of your suggested changes and I still get the same error.

A Da! epiphany may have occured in my mind.

I bet that a table is a RecordSet in a Project? No?

If so,

Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In
('1K','2D')) And ((CHOOSEData.LTrim_REG)<>'7'))") 'may be a solution?

To be more clear:

The VBA module is in "Tools.mdb"

So I am in Tools.mdb and have set currentdb to "Recon.mdb"

In Recon.mdb I have previously created a table "ChooseData"

I want to use "ChooseData" to create a new "ChooseRev" table in Recon.mdb

Then I close Recon.mdb and stay in "Tools.mdb" to create another Recon.mdb as Recon2.mdb.
 
I made all of your suggested changes and I still get the same error.

A Da! epiphany may have occured in my mind.

I bet that a table is a RecordSet in a Project? No?

If so,

Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In
('1K','2D')) And ((CHOOSEData.LTrim_REG)<>'7'))") 'may be a solution?

To be more clear:

The VBA module is in "Tools.mdb"

So I am in Tools.mdb and have set currentdb to "Recon.mdb"

In Recon.mdb I have previously created a table "ChooseData"

I want to use "ChooseData" to create a new "ChooseRev" table in Recon.mdb

Then I close Recon.mdb and stay in "Tools.mdb" to create another Recon.mdb as Recon2.mdb.

Any other thoughts?

What about
 
Louis,

I made all of your suggested changes and I still get the same error.

A Da! epiphany may have occured in my mind.

I bet that a table is a RecordSet in a Project? No?

If so,

Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In
('1K','2D')) And ((CHOOSEData.LTrim_REG)<>'7'))")            'may be a solution?

To be more clear:

The VBA module is in "Tools.mdb"

So I am in Tools.mdb and have set currentdb to "Recon.mdb"

In Recon.mdb I have previously created a table "ChooseData"

I want to use "ChooseData" to create a new "ChooseRev" table in Recon.mdb

Then I close Recon.mdb and stay in "Tools.mdb" to create another Recon.mdb as Recon2.mdb.








- Show quoted text -
Click to expand...


OK. I remember. You want to copy a table from one database to
another. You were given several suggestions as how to do this.

You were not given the menu-driven suggestion:

Open the receiving database. From the File Menu, choose Get
External data. A dialog box will open. You want to import data from
the sending database. Navigate in the file find dialog box until you
see the originating database. Select it. See the table names in the
database. Choose CHOOSEData. Click OK.

CHOOSEDATA will appear in the receiving database if none had existed
before. If a CHOOSEDATA exists, the table will appear named
CHOOSEDATA1.

In the Database Tables menu, highlight the "CHOOSEDATA" table and
right mouse click. You will be allowed to rename the table to
CHOOSERev.

I am surprised the Execute method failed.

The other option I can think of is to use the: DoCmd.RunSql sqlStmt

But really, I think you are working too hard to copy a table from one
MDB to another.
 
Louis,

My goal is to do the task in VBA so it is repeatable and the "query" is imbedded in the VBA and not
in a Query which can be changed by other OPs.

If it were a One-time issue, you are correct.

Thanks EagleOne
 
I guess it's Sunday and everyone's a bit sleepy, but you've been given some
pretty weird suggestions so far.

One problem seems blindingly obvious to me: I can't imagine why you'd want
to put a line-feed character in the middle of an SQL string. Replacing it
with vbCRLF is just as bizarre. Try this:

dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _
& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX,
CHOOSEData.AAA_UIC, " _
& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _
& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _
& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _
& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _
& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _
& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _
& " INTO CHOOSERev FROM CHOOSEData WHERE " & _
& "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)<>'7'));"
 
Sometimes Access gives strange error messages.
Click to expand...
I would look at the following:
Click to expand...
--There is no space after " INTO CHOOSERev".
--There is a line-feed character before and after "FROM CHOOSEData".
Click to expand...

This is the nub of the problem. All that is needed is a space before and
after.
--There is a semi-colon embedded in the sql string.
Click to expand...

It's the statement terminator, which is fine. You don't have to put one,
but it's OK if you do.
--There is a limit to the length of an sql statement and you are
wasting
part of that limited number of characters by repeating
"CHOOSEDATA.".
( I think the limit is 255 characters )
Click to expand...

No, that limit only applies to an SQL string used as a RecordSource property
or RowSource property.
 
Michael Gramelspacher said:
try this

dbs.Execute _
" a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _
" a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _
" a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _
" a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _
" a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _
" INTO [CHOOSERev] FROM [CHOOSEData]" & _
" WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _
" AND ((a.LTrim_REG) <> '7'));"
Click to expand...

How can he execute that string, it isn't even a statement? There's no
SELECT...
 
Hi John,

The line feed character shouldn't be there at all (a linefeed in the middle
of an SQL statement???), adding a carriage return to it isn't going to help!
 
Michael Gramelspacher said:
try this

dbs.Execute _
" a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _
" a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _
" a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _
" a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _
" a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _
" INTO [CHOOSERev] FROM [CHOOSEData]" & _
" WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _
" AND ((a.LTrim_REG) <> '7'));"
Click to expand...

How can he execute that string, it isn't even a statement? There's no
SELECT...
Click to expand...

Yes, of course. Just a clerical error on my part, but not hard to correct now that the error has
been pointed out.
 
Hi John,

The line feed character shouldn't be there at all (a linefeed in the middle
of an SQL statement???), adding a carriage return to it isn't going to help!








- Show quoted text -
Click to expand...

bcap:

Sorry if I misled the group. The limitation on the length of a string
comes from the limitation on continuation characters.

Sub Main()

Dim sqlStmt As String

sqlStmt = String(80, "A") & _
String(80, "B") & _
String(80, "C") & _
String(80, "D") & _
String(80, "E") & _
String(80, "F") & _
String(80, "G") & _
String(80, "H") & _
String(80, "B") & _
String(80, "I") & _
String(80, "J") & _
String(80, "K") & _
String(80, "L") & _
String(80, "M") & _
String(80, "N") & _
String(80, "O") & _
String(80, "P") & _
String(80, "Q") & _
String(80, "R") & _
String(80, "S") & _
String(80, "T") & _
String(80, "U") & _
String(80, "V") & _
String(80, "W")


MsgBox (sqlStmt)
End Sub

Try to add an "X" string and an error will occur. My recollection
about the allowable length of an SqlStatement was faulty.
 
The Chr(10) line-feed characters serve no purpose here. Just use a space
character. As you are only returning columns from one table there is no need
to qualify the column names with the table names, so:

Dim dbs as DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT " & _
" BFY, APPN_SYMB, SBHD, BCN, SA_SX," & _
" AAA_UIC, ACRN, AMT, DOC_NUMBER, FIPC," & _
" REG_NUMB, TRAN_TYPE, DOV_NUM, PAA," & _
" COST_CODE, OBJ_CODE, EFY, REG_MO, RPT_MO," & _
" EFFEC_DATE, Orig_Sort, LTrim_BFY, LTrim_AAA," & _
" LTrim_REG, LTrim_DOV, AMT_Rev, CONCACT" & _
" INTO CHOOSERev FROM CHOOSEData" & _
" WHERE TRAN_TYPE IN (""1K"",""2D"")" & _
" AND LTrim_REG <> ""7"""

dbs.Execute strSQL

I'm assuming 'CONCACT' is not a typo.

Ken Sheridan
Stafford, England
 
Same error! error (#3601): Too few parameters. Expected 1"

Trust me I am, at least, just as frustrated. What I believe is that the issue is so obvious that we
are all walking right past it.

Folks, please do not give up!

**************** Please Continue Below ***************************************

The VBA module from which this VBA is invoked, is in "Tools.mdb"

While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb"

In Recon.mdb, I have previously created a table "ChooseData"

I want to use Recon.mdb's "ChooseData" table to create a new table called "ChooseRev" in Recon.mdb

!!!! PROGRESS !!!! NOTE: The following seems to create something (it does not bomb!)

Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In
('1K','2D')) And ((CHOOSEData.LTrim_REG)<>'7'))") 'may be a solution?

If it produces CHOOSERev, a "RecordSet" then what command can I use to save it in Recon.mdb (alias
dbs and not in Tools.mdb?)

EagleOne
 
Ken, thanks for weighing in. I still get the same error message.

Please read my last posting (previous) above.

I am concerned that the error message is incorrect as to the real reason.

I am beginning to feel that attempting to establish a new table in project 2 from a table currently
in table 2) from project 1's VBA module may be an issue that is masking the real error.

Also, look at my comment about the successful SET (RecordSet) command.

I am relatively new to VBA SQL Access.

Therefore, if I have a RecordSet "CHOOSERev" from a Project "Recon.mdb" table "CHOOSEData", then how
do I save the RecordSet "CHOOSERev" into Project "Recon.mdb" table "CHOOSERev"?

(Again I am executing this code from Project Tools.mdb.)
 
Back
Top