Append Query in VBA (?)

[croy]

I have a string of append querys that I need to run monthly.

I've been doing it by first importing the tables I need to
append, and then running the append querys. It's gotten
pretty smooth, so I thought it might be time to roll them
into code behind a form, and just run the lot of them, in a
particular sequence, from a single button.

But I've never done anything like that before.

Here's the SQL from one of the querys:

*****
INSERT INTO tblCountDetail ( CountSurvId, CountPage,
CountLine, CountTime, RiverMile, Boats, B_Anglers,
S_Anglers, NonActBoats, NonActBAnglers, NonActSAnglers,
NewRecDate, NewRecClerk, LastUpdDate, LastUpdClerk, FO,
FO_CountDetId, FO_CountSurvId, FO_YrMo, Appended )
SELECT tblCountSurv.CountSurvId, tblCountDetail1.CountPage,
tblCountDetail1.CountLine, tblCountDetail1.CountTime,
tblCountDetail1.RiverMile, tblCountDetail1.Boats,
tblCountDetail1.B_Anglers, tblCountDetail1.S_Anglers,
tblCountDetail1.NonActBoats, tblCountDetail1.NonActBAnglers,
tblCountDetail1.NonActSAnglers, tblCountDetail1.NewRecDate,
tblCountDetail1.NewRecClerk, tblCountDetail1.LastUpdDate,
tblCountDetail1.LastUpdClerk, tblCountDetail1.FO,
tblCountDetail1.FO_CountDetId,
tblCountDetail1.FO_CountSurvId, tblCountDetail1.FO_YrMo,
Now() AS ImpDate
FROM tblCountSurv INNER JOIN tblCountDetail1 ON
(tblCountSurv.FO_YrMo = tblCountDetail1.FO_YrMo) AND
(tblCountSurv.FO_CountSurvId = tblCountDetail1.CountSurvId)
WITH OWNERACCESS OPTION;

How would I go about putting this into VBA?

 

[Clifford Bass]

Hi croy,

You can do something like this:

' Import the data from a text file
DoCmd.TransferText acImportDelim, , "tblImportInto", "C:\MyFile.txt", True

' Run some queries
DoCmd.SetWarnings False
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.SetWarnings True

Check out the online help for more details/options on the
DoCmd.TransferText command or for similar transfer commands.

Clifford Bass

 

[croy]

Thanks for the reply Clifford.

But genious here forgot to mention that the data to be
appended is in another MS Access database.

I will investigate the TransferText and RunSQL
methods--thanks.

 

[Clifford Bass]

Hi Croy,

In that case you want to use the DoCmd.TransferDatabase method. Do a
search in help for that to get the details on all of the different parameters.

And, you are welcome!

Clifford Bass

 

[croy]

Thanks again Clifford.

I've got the DoCmd.TransferDatabase method working to link
the tables, and my code to get rid of the table links when
I'm done, is also working. But my code to run the append
querys is not working. I keep getting the error,

"3061 Too few parameters. Expected 2."

Here's that code for one of the querys:

CurrentDb.Execute "qryAppend_01_tblClerk1", _
dbFailOnError

After looking over the help and examples, it *seems* like
that ought to work.

 

[croy]

I forgot to mention that the querys run fine, when fired off
manually.

 

[croy]

Success!

Thanks so much for your time, Clifford.

--
croy

Hi Croy,

Try this instead:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppend_01_tblClerk1"
DoCmd.SetWarnings True

Clifford

I forgot to mention that the querys run fine, when fired off
manually.

Thanks again Clifford.

[quoted text clipped - 12 lines]

After looking over the help and examples, it *seems* like
that ought to work.

 

[Clifford Bass]

Hurray! You are welcome.

Clifford Bass