|
|
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?
--
Thanks,
croy
|
|
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" wrote:
[Quoted Text] > 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?
>
> --
> Thanks,
> croy
|
|
|