SQL INSERT statement in VB

[CharonErebus]

What I'm trying to do is query an MS Access database and display all the results in a datagrid. That part is working fine, but I also want to insert the results into a table to keep separately from the original table. I'm having trouble with my SQL statement and keep getting errors. What I need to know is the syntax for using the SQL in VB. Should I declare a new variable as an ADODB.Recordset and use .Open? If so, what would be the syntax for it? Or is there another way to do this that I'm not seeing?

--CharonErebus

 

[zemp]

Do a search for the insert statement of this forum and forum222.

If you get stuck again post the relevant code. Thanks and Good Luck!

zemp

 

[CharonErebus]

My problem isn't really with the Insert statement so much as how to get my point across to VB I'm not sure if I should use an ADODB.Connection, ADODB.Recordset, ADODB.Command, etc. And after I Dim my variable as one of the above, would it be easier to use .Open, .Execute, etc. to send the SQL statement to the Access database? Sorry for the confusion.

--CharonErebus

 

[zemp]

I usually use the insert statement with an ADODB recordset object and its .open method.

rs.Open strSQL, CONN, adOpenKeyset, adLockOptimistic

Where strSQL is the Insert statement built in code and CONN is a valid Connection object open to the datbase. Thanks and Good Luck!

zemp

 

[CharonErebus]

strX = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cdlOpen.FileName & ";Persist Security Info=False"
SelectString = "SELECT * FROM Subscribers WHERE " & lstQuery.List(lstQuery.ListIndex) & " = '" & txtQuery.Text & "'"
TableCreate = "INSERT INTO Billing (Last_Name, First_Name, Address_1, Address_2, City, State, Country, Zip_Code, Zip_Zone, First_Name_(Billing), Last_Name_(Billing), Address_1_(Billing), Address_2_(Billing), City_(Billing), State_(Billing), Country_(Billing), Zip_Code_(Billing), Zip_Zone_(Billing), Subscription_Date, Expiration_Date, Permanent, Permanent_Reason) VALUES (" & SelectString & ")"
rstY.Open TableCreate, strX

I'm getting an error that says "Syntax Error in INSERT INTO Statement" I don't see the error...does anyone else?

--CharonErebus

 

[zemp]

Your values have to correspond to the fields listed. The insert statement will not evaluate your select statement contained in your 'SelectString' variable.

I would create a recordset from the first select and then use that recordsets field values in the insert statement to get the actual values in.

rs1.open SelectString, Conn,...

Place values into Insert statement

TableCreate = "INSERT INTO Billing (Last_Name,First_Name, Address_1, Address_2, City, State, Country, Zip_Code, Zip_Zone, First_Name_(Billing), Last_Name_(Billing), Address_1_(Billing), Address_2_(Billing), City_(Billing), State_(Billing), Country_(Billing), Zip_Code_(Billing), Zip_Zone_(Billing), Subscription_Date, Expiration_Date, Permanent, Permanent_Reason) VALUES ('" & rs1![Field1] & "','" & rs1![Field2] & "','" etc. ..... "')"

rs1![Field1]=Last_Name, rs1![Field2]=First_Name, etc.

Make sure that string values in the Values section of the insert statement are surrounded by single quotes.

You can also add the following statement after the insert statement has been created to double check exactly what your statement is.

Debug.Print TableCreate

Thanks and Good Luck!

zemp