ADO and SQL help

[oli82]

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

 

[boblarson]

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.

 

[oli82]

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

 

[boblarson]

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 & "');"

[COLOR="Red"]CurrentDb.Execute strSQL, dbFailOnError[/COLOR]          
  
strSQL = strSQL + "INSERT INTO tblContacts(" & _
            "txtLastName, txtFirstName, txtTitle) " & _
            "VALUES (" & _
            "'" & Me.LN & "', " & _
            "'" & Me.FN & "', " & _
            "'" & Me.ttl & "'); "

[COLOR="red"]CurrentDb.Execute strSQL, dbFailOnError[/COLOR]