A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #22  
Old August 10th, 2008, 09:20 PM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 87
Default SQL code works in Access SQL window but not in VBA SQL code

On Aug 10, 1:10*pm, "bcap" wrote:
"Michael Gramelspacher" wrote in message

...





On Sun, 10 Aug 2008 18:44:52 +0100, "bcap" wrote:


"Michael Gramelspacher" wrote in message
. ..
On Sun, 10 Aug 2008 09:30:25 -0700 (PDT),
wrote:


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'));"


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


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


Indeed not difficult to correct (sorry, I didn't actually look at your
string closely enough to realise that *only* the "SELECT" was missing).
Unfortunately, the OP's reply to you suggests that he tried to execute the
code *exactly* as you posted it.- Hide quoted text -

- Show quoted text -


Do I understand this correctly?


The user has opened Tool.mdb.

The ChooseData table exists ChooseData in Recon.

You want to dynamically create ChooseRev in Recon.

Within the Tools.mdb, please try this model:


Sub CopyData()

Dim sInsert As String
Dim sPathToRecon As String

sPathToRecon = "C:\TEMP\Recon.mdb"

sInsert = "SELECT ChooseData.* " _
& "INTO ChooseRev in " _
& Chr(34) & sPathToRecon & Chr(34) _
& "FROM ChooseData in " _
& Chr(34) & sPathToRecon & Chr(34)

DoCmd.SetWarnings 0
DoCmd.RunSQL (sInsert)
DoCmd.SetWarnings 1


End Sub
  #23  
Old August 10th, 2008, 09:55 PM posted to microsoft.public.access
bcap[_2_]
external usenet poster
 
Posts: 260
Default SQL code works in Access SQL window but not in VBA SQL code

There is no way to take a recordset and save it as a table in one command.
You would need to first create a new, empty table, and then iterate through
the recordset saving each record in turn.

But, this is completely unnecessary. What you are saying is that this query
works:

SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'))

In which case, this code will work:

dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where
(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))"

There are three possible reasons why the query I gave you earlier didn't
work:

1. There was a line wrap in your newsreader. In your code, every line
except the last one should end with a line continuation character, an
underscore. If this isn't the case, then use the VBA editor to delete the
spurious linewraps caused by your newsreader.

2. The query you are constructing in VBA is just plain different to the
one that works when you run it in the SQL window. Please make sure that the
query in the SQL window still works (without prompting for parameters), and
then post it's SQL.

3. When you ran the query in the SQL window, you erroneously did so in
Tools.mdb, not in Recon.mdb, with the result that the query in the SQL
window and the query in the VBA are running against *different tables* in
*different databases*.


Finally, I'm concerned that you say "I have set the Currentdb to
"Recon.mdb"". This is impossible: CurrentDb is not an object variable, it
is a method, and you cannot set it to anything. It might be a good idea for
you to show us the code you are using to create and set the object variable
"dbs".


wrote in message
...
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

"bcap" wrote:

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'));"



  #24  
Old August 10th, 2008, 10:44 PM posted to microsoft.public.access
a a r o n . k e m p f @ g m a i l . c o m
external usenet poster
 
Posts: 1,108
Default SQL code works in Access SQL window but not in VBA SQL code

maybe you should use a database engine with a future?

SQL Server typically has _MUCH_ better errors returned.. from the
debug message that you are seeing it is hard to determine what is
really happening.
yet another reason to move to SQL Server--

(you could capture via SQL Profiler and then compare the real strings
if you needed to)

Soudns to me like you're just constantly frustrated by the limitations
of JET.
Do I really need to say it here?

MOVE TO SQL SERVER, THINGS JUST WORK.
You would have gotten a real error message if you used SQL Server.

Sorry-- those are the facts

-Aaron



On Aug 10, 11:58*am, wrote:
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.)

Ken Sheridan wrote:
* *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


  #25  
Old August 11th, 2008, 12:24 AM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default SQL code works in Access SQL window but not in VBA SQL code

I don't know what you are trying to accomplish, but if the Project you are
talking about is an Access ADP, they don't support internal Tables, they use
Tables from SQL Server (or, if you go back to the proper back-level of
ADODB, an external Access MDB). If you are asking questions about an ADP
Project, it's a really good idea to say so, up front...

If not, I don't understand what you mean by your statement.

Larry Linson
Microsoft Office Access MVP

wrote in message
...
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

John Spencer wrote:

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
'=============================================== =====


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



  #26  
Old August 11th, 2008, 12:35 AM posted to microsoft.public.access
Please Learn to Read[_2_]
external usenet poster
 
Posts: 13
Default SQL code works in Access SQL window but not in VBA SQL code

Please learn to read. The original question was about an SQL statement
executed from VBA. It did not remotely resemble a question asking what
database engine to use. You will find basic literacy training is available at
no or low cost from local welfare and educational organizations for
illiterate individuals, as you clearly seem to be.

That's not to excuse the original poster for beginning vast projects with
half-vast understanding of his tools, but the answer to his question is not
to convert to a different database engine, either local or remote,
file-server or server.

Sorry, but THESE are the facts.

2008 Learn-to-Read Campaign

"a a r o n . k e m p f @ g m a i l . c o" wrote:

maybe you should use a database engine with a future?

SQL Server typically has _MUCH_ better errors returned.. from the
debug message that you are seeing it is hard to determine what is
really happening.
yet another reason to move to SQL Server--

(you could capture via SQL Profiler and then compare the real strings
if you needed to)

Soudns to me like you're just constantly frustrated by the limitations
of JET.
Do I really need to say it here?

MOVE TO SQL SERVER, THINGS JUST WORK.
You would have gotten a real error message if you used SQL Server.

Sorry-- those are the facts

-Aaron


  #27  
Old August 11th, 2008, 01:20 AM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

Sub CreateReconFile()
'
' The purpose of Tools.mdb is to create 172 ReconXXX.mdb files monthly
' this sub is in Tools.mdb
' up to this point all prior code lines involved Tools.mdb
' next is the creation of Recon/mdb
'
myPath = "C:\Access"

dbsfilename = "Recon.mdb"

Set appAccess = CreateObject("Access.Application")
appAccess.NewCurrentDatabase myPath & dbsfilename
Set dBs = appAccess.CurrentDb
' at this Currentdb.Name returns Recon.dbf

dBs.Execute "SELECT * INTO STARSData FROM [Text;FMT= _
Fixed;HDR=No;DATABASE=" & myPath & ";].[STARSData#txt];", dbFailOnError
Set rs = dBs.OpenRecordset("STARSData")
Set rs = Nothing
dBs.Execute "SELECT * INTO CHOOSEData FROM [Text;FMT= _
Delimited;HDR=No;DATABASE=" & myPath & ";].[CHOOSEData#txt];", dbFailOnError
Set rs = dBs.OpenRecordset("CHOOSEData")
' The code in the next "With ....." works fine
With appAccess.CurrentDb
.Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_BFY VarChar(5);"
.Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_AAA VarChar(7);"
.Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_REG VarChar(5);"
.Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_DOV VarChar(9);"
.Execute "ALTER TABLE CHOOSEData ADD COLUMN AMT_Rev VarChar(5);"
.Execute "ALTER TABLE CHOOSEData ADD COLUMN CONCACT VarChar(25);"
.Execute "Update CHOOSEData Set LTrim_BFY=IIf(Left([BFY],1)=""0"",Mid([BFY],2,1),[BFY]);"
.Execute "Update CHOOSEData Set _
LTrim_AAA=IIf(Len([AAA_UIC])=6,Trim(Mid([AAA_UIC],2,6)),[AAA_UIC]);"
.Execute "Update CHOOSEData Set _
LTrim_REG=IIf(Left([REG_NUMB],1)=""0"",Mid([REG_NUMB],2,1),[REG_NUMB]);"
.Execute "Update CHOOSEData Set _
LTrim_DOV=IIf(Left([DOV_NUM],4)=""0000"",Mid([DOV_NUM],5,4)," _
& " IIf(Left([DOV_NUM],3)=""000"",Mid([DOV_NUM],4,5)," _
& " IIf(Left([DOV_NUM],2)=""00"",Mid([DOV_NUM],3,6)," _
& " IIf(Left([DOV_NUM],1)=""0"",Mid([DOV_NUM],2,7),[DOV_NUM]))));"
.Execute "Update CHOOSEData Set AMT_Rev=[AMT]*-1;"
.Execute "Update CHOOSEData Set CONCACT=CHOOSEData.LTrim_BFY & CHOOSEData.APPN_SYMB " _
& "& CHOOSEData.SBHD & CHOOSEData.BCN & CHOOSEData.SA_SX & CHOOSEData.TRAN_TYPE " _
& "& CHOOSEData.AMT_Rev;"
End With

' Now the problem code
'
With appAccess.CurrentDb
.Execute "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 " & Chr(10) _
& "FROM CHOOSEData" & Chr(10) _
& " WHERE (((TRAN_TYPE) In ('1K','2D')) And ((LTrim_REG)'7'));"
End With
'
' Other code lines
End sub



"bcap" wrote:

There is no way to take a recordset and save it as a table in one command.
You would need to first create a new, empty table, and then iterate through
the recordset saving each record in turn.

But, this is completely unnecessary. What you are saying is that this query
works:

SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'))

In which case, this code will work:

dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where
(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))"

There are three possible reasons why the query I gave you earlier didn't
work:

1. There was a line wrap in your newsreader. In your code, every line
except the last one should end with a line continuation character, an
underscore. If this isn't the case, then use the VBA editor to delete the
spurious linewraps caused by your newsreader.

2. The query you are constructing in VBA is just plain different to the
one that works when you run it in the SQL window. Please make sure that the
query in the SQL window still works (without prompting for parameters), and
then post it's SQL.

3. When you ran the query in the SQL window, you erroneously did so in
Tools.mdb, not in Recon.mdb, with the result that the query in the SQL
window and the query in the VBA are running against *different tables* in
*different databases*.


Finally, I'm concerned that you say "I have set the Currentdb to
"Recon.mdb"". This is impossible: CurrentDb is not an object variable, it
is a method, and you cannot set it to anything. It might be a good idea for
you to show us the code you are using to create and set the object variable
"dbs".


wrote in message
.. .
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

"bcap" wrote:

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'));"


  #28  
Old August 11th, 2008, 01:24 AM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

The original poster is learning. We all started from zero. Please consider that fact and consider
posting a solution instead of perpetuating an out-of-control flammer.



Please Learn to Read wrote:

Please learn to read. The original question was about an SQL statement
executed from VBA. It did not remotely resemble a question asking what
database engine to use. You will find basic literacy training is available at
no or low cost from local welfare and educational organizations for
illiterate individuals, as you clearly seem to be.

That's not to excuse the original poster for beginning vast projects with
half-vast understanding of his tools, but the answer to his question is not
to convert to a different database engine, either local or remote,
file-server or server.

Sorry, but THESE are the facts.

2008 Learn-to-Read Campaign

"a a r o n . k e m p f @ g m a i l . c o" wrote:

maybe you should use a database engine with a future?

SQL Server typically has _MUCH_ better errors returned.. from the
debug message that you are seeing it is hard to determine what is
really happening.
yet another reason to move to SQL Server--

(you could capture via SQL Profiler and then compare the real strings
if you needed to)

Soudns to me like you're just constantly frustrated by the limitations
of JET.
Do I really need to say it here?

MOVE TO SQL SERVER, THINGS JUST WORK.
You would have gotten a real error message if you used SQL Server.

Sorry-- those are the facts

-Aaron


  #29  
Old August 11th, 2008, 01:28 AM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

The actual Access SQL-view code is:

SELECT CHOOSE_Add_Fields.BFY, CHOOSE_Add_Fields.APPN_SYMB, CHOOSE_Add_Fields.SBHD,
CHOOSE_Add_Fields.BCN, CHOOSE_Add_Fields.SA_SX, CHOOSE_Add_Fields.AAA_UIC, CHOOSE_Add_Fields.ACRN,
CHOOSE_Add_Fields.AMT, CHOOSE_Add_Fields.DOC_NUMBER, CHOOSE_Add_Fields.FIPC,
CHOOSE_Add_Fields.REG_NUMB, CHOOSE_Add_Fields.TRAN_TYPE, CHOOSE_Add_Fields.DOV_NUM,
CHOOSE_Add_Fields.PAA, CHOOSE_Add_Fields.COST_CODE, CHOOSE_Add_Fields.OBJ_CODE,
CHOOSE_Add_Fields.EFY, CHOOSE_Add_Fields.REG_MO, CHOOSE_Add_Fields.RPT_MO,
CHOOSE_Add_Fields.EFFEC_DATE, CHOOSE_Add_Fields.Orig_Sort, CHOOSE_Add_Fields.LTrim_BFY,
CHOOSE_Add_Fields.LTrim_AAA, CHOOSE_Add_Fields.LTrim_REG, CHOOSE_Add_Fields.LTrim_DOV,
CHOOSE_Add_Fields.AMT_Rev, CHOOSE_Add_Fields.LTrim_BFY & CHOOSE_Add_Fields.APPN_SYMB &
CHOOSE_Add_Fields.SBHD & CHOOSE_Add_Fields.BCN & CHOOSE_Add_Fields.SA_SX &
CHOOSE_Add_Fields.TRAN_TYPE & CHOOSE_Add_Fields.AMT_Rev AS CONCACT INTO CHOOSERev
FROM CHOOSE_Add_Fields
WHERE (((CHOOSE_Add_Fields.TRAN_TYPE) In ('1K','2D')) AND ((CHOOSE_Add_Fields.LTrim_REG)'7'));

I am not sure what my web browser did to the above code; that said, it is strictly copy/paste

"bcap" wrote:

There is no way to take a recordset and save it as a table in one command.
You would need to first create a new, empty table, and then iterate through
the recordset saving each record in turn.

But, this is completely unnecessary. What you are saying is that this query
works:

SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'))

In which case, this code will work:

dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where
(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))"

There are three possible reasons why the query I gave you earlier didn't
work:

1. There was a line wrap in your newsreader. In your code, every line
except the last one should end with a line continuation character, an
underscore. If this isn't the case, then use the VBA editor to delete the
spurious linewraps caused by your newsreader.

2. The query you are constructing in VBA is just plain different to the
one that works when you run it in the SQL window. Please make sure that the
query in the SQL window still works (without prompting for parameters), and
then post it's SQL.

3. When you ran the query in the SQL window, you erroneously did so in
Tools.mdb, not in Recon.mdb, with the result that the query in the SQL
window and the query in the VBA are running against *different tables* in
*different databases*.


Finally, I'm concerned that you say "I have set the Currentdb to
"Recon.mdb"". This is impossible: CurrentDb is not an object variable, it
is a method, and you cannot set it to anything. It might be a good idea for
you to show us the code you are using to create and set the object variable
"dbs".


wrote in message
.. .
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

"bcap" wrote:

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'));"


  #30  
Old August 11th, 2008, 01:37 AM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

Larry,

I know that I am out on a long limb. I have no idea where I am at in ADP, ADODB or SQL Server or
even plain ol'e Access! I am just a everyday accountant trying to accomplish a task.

Yes, I am grabbing at code pieces I get from the Web. Actually, I have gotten quite far. I am 95%
through this "Project."

What I need is a solution as to why I cannot seem to use this dbs.Execute command.

We all learn while doing. Thanks

"Larry Linson" wrote:

I don't know what you are trying to accomplish, but if the Project you are
talking about is an Access ADP, they don't support internal Tables, they use
Tables from SQL Server (or, if you go back to the proper back-level of
ADODB, an external Access MDB). If you are asking questions about an ADP
Project, it's a really good idea to say so, up front...

If not, I don't understand what you mean by your statement.

Larry Linson
Microsoft Office Access MVP

wrote in message
.. .
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

John Spencer wrote:

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
'============================================== ======


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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.