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