Hi,

Thanks for your help in advance, its really appreciated.

I am using ADO with SQL in Access VBA,

I want to essentially devise a form whereby I can enter using the SQL insert statement more than one record of data through the same form.


strSQL = "INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtTitle & "');"
"INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.LN & "', " & _
"'" & Me.FN & "', " & _
"'" & Me.ttl & "'); "

When I run this I get an error saying extra text after end of line, refereing i think to the second insert command -
is it not possible to run more than one insert cmd per go? I thought this was not the case.

The end result would be to auto build a string of SQL to populate a number of tables.

Thanks for the help or any good online references to ADO SQL etc would be great.

Many Thanks,

Oliver

You would need to execute one Insert first and then assign the next Insert to strSQL and then execute it. You can't do "two-in-one" like that.

Bob,

Many thanks for the help would it look like this then:

strSQL = "INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtTitle & "');"

strSQL = strSQL + "INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.LN & "', " & _
"'" & Me.FN & "', " & _
"'" & Me.ttl & "'); "

Appologies for my lack of understanding on this I am new to ADO. Are there any good pages you could point me to that would help.

I am basically trying to append data to a number of tables at the same time using one front end form, I cant use a query before hand because I need to add a number of records in some cases to the same table, as well as other tables!

hope that made sense.

Cheers,

Oliver

Actually, you don't need ADO to do the insert queries. You just execute them:

strSQL = "INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtTitle & "');"

CurrentDb.Execute strSQL, dbFailOnError

strSQL = strSQL + "INSERT INTO tblContacts(" & _
"txtLastName, txtFirstName, txtTitle) " & _
"VALUES (" & _
"'" & Me.LN & "', " & _
"'" & Me.FN & "', " & _
"'" & Me.ttl & "'); "

CurrentDb.Execute strSQL, dbFailOnError