Double Insert :: best route?

[jason]

If one is attempting to insert data into tables at the same time what is the
best way to do this. I could do it the way below - but is there any reason I
should not do it this way or perhaps follow a better route. The second
table is really an audit table to track changes made by the user



Set cnn = CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/listings.mdb") '//This one is for Access
2000/2002
cnn.Open(strCon)

SQL = "INSERT INTO tblCustomer (email_address, first_name, last_name)
VALUES("
SQL=SQL & "'" & email_address & "', "
SQL=SQL & "'" & first_name & "', "
SQL=SQL & "'" & last_name & "', "

cnn.Execute(SQL)


SQL = "INSERT INTO auditCustomer (email_address, first_name, last_name)
VALUES("
SQL=SQL & "'" & email_address & "', "
SQL=SQL & "'" & first_name & "', "
SQL=SQL & "'" & last_name & "', "

cnn.Execute(SQL)

 

[Ray at]

Since Access does not support multiple queries in one command, this is
pretty much what you'll have to do. I personally would build to SQL strings
separately and execute them right in succession, i.e.

sSQL = "INSERT INTO tblCustomer (email_address, first_name, last_name)
VALUES("
sSQL=sSQL & "'" & email_address & "', "
sSQL=sSQL & "'" & first_name & "', "
sSQL=sSQL & "'" & last_name & "', "
sSQLAudit = Replace(sSQL, "tblCustomer", "auditCustomer")

Set cnn = CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/listings.mdb") '//This one is for Access
2000/2002
cnn.Open(strCon)
cnn.Execute sSQL
cnn.Execute sSQLAudit
cnn.Close
Set cnn = Nothing


'''Note that if your SQL contains "tblCustomer" in the actual data, it'll
get replaced there as well, so perhaps you'll want to build the strings a
little differently.

Ray at home