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

[bcap]

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.

 

[louisjohnphillips]

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

 

[bcap]

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".

 

[a a r o n . k e m p f]

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

 

[Larry Linson]

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

 

[Please Learn to Read]

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

 

[EagleOne]

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

 

[EagleOne]

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.

 

[EagleOne]

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

 

[EagleOne]

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

 

[David W. Fenton]

(e-mail address removed) wrote in

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

Assign the SQL string to a variable (ignoring the word wrap from my
post):

Dim strSQL As String

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

Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

The Debug statement will show you exactly what string is getting
executed. Copy it and paste into SQL View of the QBE and try running
it. Likely it will fail and then you can figure out what's wrong.

 

[Larry Linson]

Access, and Microsoft, aren't as consistent as we might like in use of the
term "Project", which is why I asked. When you close Access, what is the
file extension of the file in which your datbase is saved? Windows explorer,
etc., may hide that information from you, so you may need to go the the menu
and choose to see it.

The reason for asking is that the ADP "Project" is quite a different thing
from an Access MDB "Project". Early on, 'Softies were using "Access Data
Project" for ADP, but someone in Redmond, in their wisdom, decided just
"Project" would sound better. Of course, the Access documentation was
already riddled with uses of "project" in a generic term referring to MDB
databases.

The MDB (or, the newer ACCDB of Access 2007) actually contains Tables. An
ADP uses tables in an external database, SQL Server, or (at least in early
versions an external MDB with an appropriate ADODB data provider). The same
approach does not work for both of them, so just copying snippets of code or
SQL without knowing where and how they are intended to be used may not work
(at least not until you do a lot of sorting-out, later) for you.

Unfortunately, as you have seen, remote debugging is, at its best,
problematic. Without appropriate information, it is impossible. That's why
so many have asked you for so many clarifications; we have to rely on you to
see, read, and tell us, because, of course, we can't see your database to
determine those things for ourselves.

I point out that "learning by doing" can sometimes be the equivalent of
"self-flagellation" and it is often better to invest some time and effort
"learning by reading". A couple of really good books, depending on the
Access version you are using, are (1) Microsoft Access 2003 Inside Out, by
John Viescas, and (2) Microsoft Access 2007 Inside Out, by John Viescas and
Jeff Conrad, both published by Microsoft Press (or maybe, now, "Microsoft
Learning"?).

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

om...

No, that limit only applies to an SQL string used as a
RecordSource property or RowSource property.

The length of those properties is several times larger than 255
characters.

 

[David W. Fenton]

(e-mail address removed) wrote in

'
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

The key question:

- where is the source data?

If it's not in the new database, then you can't do it via the method
you're using.

Also, your code is terribly confused, with unused recordsets and a
database variable that is set to appAccess.CurrentDB at the same
time that you use appAccess.CurrentDB directly.

What you need, I think, is the ability to run a query in one MDB and
append its results to a table in a different MDB. You don't need to
initialize an entirely new instance of Access.

My suggestion would be something like this:

myPath = "C:\Access"
dbsfilename = "Recon.mdb"
Set dbs = DBEngine.OpenDatabase myPath & dbsfilename
strSQL = "SELECT ..."
strSQL = strSQL & " FROM CHOOSEData IN 'C:\Access\Tool.mdb'"
strSQL = strSQL & " WHERE ..."
dbs.Execute strSQL, dbFailOnError

What this does is use your Recon.mdb (the destination MDB) as the
operating database, but runs a query on Recon.mdb that will draw
data from Tool.mdb.

I think this is what you need here. It's much, much simpler than the
code you posted.

 

[David W. Fenton]

(e-mail address removed) wrote in

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.

Aaron's response was not at all relevant to your question.

 

[EagleOne]

Thank you for your time and thoughts!

(e-mail address removed) wrote in


The key question:

- where is the source data?

If it's not in the new database, then you can't do it via the method
you're using.

Also, your code is terribly confused, with unused recordsets and a
database variable that is set to appAccess.CurrentDB at the same
time that you use appAccess.CurrentDB directly.

What you need, I think, is the ability to run a query in one MDB and
append its results to a table in a different MDB. You don't need to
initialize an entirely new instance of Access.

My suggestion would be something like this:

myPath = "C:\Access"
dbsfilename = "Recon.mdb"
Set dbs = DBEngine.OpenDatabase myPath & dbsfilename
strSQL = "SELECT ..."
strSQL = strSQL & " FROM CHOOSEData IN 'C:\Access\Tool.mdb'"
strSQL = strSQL & " WHERE ..."
dbs.Execute strSQL, dbFailOnError

What this does is use your Recon.mdb (the destination MDB) as the
operating database, but runs a query on Recon.mdb that will draw
data from Tool.mdb.

I think this is what you need here. It's much, much simpler than the
code you posted.

 

[EagleOne]

The oddball spaces etc is coming from the web browser.

Thanks for your knowledge and time!

 

[EagleOne]

Excellent idea! I'll post the results tomorrow morning.

 

[EagleOne]

Very fair and logical.

This is an Access.mdb issue.

I have been able to get "*" to work, whereas the detailed fields list did not work.

Exclusive of length of the strSQL string (not the real issue) the only other issue is some hidden
character(s) in the string. That said, the working Access SQL-view information was used after copy
paste in/out of the VBA Editor.

 

[a a r o n . k e m p f]

you don't need 3 tiers of tables.

keep data where it belongs- on a db server