SQL issue

[Guest]

Hello,

I need to append records to two tables at the same time. I know how to do
this for one table, but how do I do it for a second one?
I've been playing around with this for a while, and I get it to work for
either one table, or not at all with errors.

What's the correct way of doing this?

Thank you.

 

[Guest]

Have you tried something like this? By the way, it looks like your WHERE
clauses are cartesian products. Is that your intention?


Private Sub CIKID_AfterUpdate()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strSQL = "INSERT INTO tblAnswers (RSCID, QuestionID)
SELECT tblRSC.RSCID, tblQuestions.QuestionID
FROM tblQuestions, tblRSC
WHERE [RSCID] = " & Me.[RSCID]

cmd.CommandText = strSQL

cmd.Execute

strSQL = "INSERT INTO tblFees (RSCID, FeeTypeID)
SELECT tblRSC.RSCID, tblFeeType.FeeTypeID
FROM tblFeeType, tblRSC
WHERE [RSCID] = " & Me.[RSCID]

cmd.CommandText = strSQL

cmd.Execute

Set cmd = Nothing


End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

Hello,

I need to append records to two tables at the same time. I know how to do
this for one table, but how do I do it for a second one?
I've been playing around with this for a while, and I get it to work for
either one table, or not at all with errors.

What's the correct way of doing this?

Thank you.

You do need to run two queries, but you can run them both from the
same subroutine or form event.

John W. Vinson[MVP]

 

[Guest]

Hello Tom,

Thank you for you solution, that was exactly what I was looking for.
I had suspected I could do something like that but I didn't know which part
of the code to copy, so I had duplicated the cmd.xxx statements as well,
which wasn't such a great idea.

As for my Where statement, it's doing precisely what I want it to do, so no
matter what it is [called], I'm fine with it.

 

[Guest]

Ok, thanks.
I think Tom provided the solution for that.

Btw, is there a way to ensure that this is run only once, and never again?
Maybe the After Update event isn't such a great idea. After the initial
selection of a value the combo box shouldn't be changed anymore, but maybe
somebody makes a mistake... that could mess things up pretty badly.

 

[John Vinson]

Ok, thanks.
I think Tom provided the solution for that.

Btw, is there a way to ensure that this is run only once, and never again?
Maybe the After Update event isn't such a great idea. After the initial
selection of a value the combo box shouldn't be changed anymore, but maybe
somebody makes a mistake... that could mess things up pretty badly.

Well, you have a vulnerability then.

The only solution that comes immediately to mind is that the same
module that runs the queries should store a value in a table (perhaps
name the table starting with the letters USYS, so it will be treated
as a "User System Table" and won't be shown in the tables list unless
you dig for it). Store a record in the table indicating that - and
when - the queries have been run. Check this table prior to running
the queries.

John W. Vinson[MVP]

 

[Chuck Grimsby]

John, rather then storing the value in a table (with all the overhead
that requires), it might be more effective to store it as a "property"
of the access database.

If memory serves, Tony's AutoFE Updater uses this method to save
version numbers.

 

[John Vinson]

John, rather then storing the value in a table (with all the overhead
that requires), it might be more effective to store it as a "property"
of the access database.

Thanks for the idea, Chuck - I haven't tried that but it indeed sounds
useful (for this and other things!)

John W. Vinson[MVP]

 

[Tony Toews]

If memory serves, Tony's AutoFE Updater uses this method to save
version numbers.

No. I save the date/time of the files on the server folder in an INI
file on each workstation. Then if the files on the server have a
changed date and time I copy them down again and update that INI file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Chuck Grimsby]

No. I save the date/time of the files on the server folder in an INI
file on each workstation. Then if the files on the server have a
changed date and time I copy them down again and update that INI file.

Opps! Sorry about that.... It's been quite some time since I looked
at that utility.

Stephen Lebans' MonthCal class shows how to save a setting as a
property of the database itself however.

 

[Tony Toews]

Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Reminds me of one of my sayings. Some computers are like some
criminals in that they can only be reformed with a shotgun.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm