INSERT INTO causing syntax error

[biffer999]

I'm having trouble copying data from one Access table to another. Any help would be appreciated.

Private Sub Command1_Click()
Set m_conn = New ADODB.Connection
Set IMHIST = New ADODB.Recordset
m_conn.ConnectionString = ...
m_conn.Open
With m_IMHIST
.ActiveConnection = m_conn
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Source = "INSERT INTO IMHIST(SELECT * FROM IMLN)"
.Open
End With
End Sub

I keep getting a syntax error

 

[Schroeder]

Try it without the parentheses in your SQL statement.

 

[PatrickGreen]

biffer999:

You need to use the command object, not the recordset object. Do the following:

Private Sub Command1_Click()
Dim ado_cmd as New ADODB.Command
Dim m_conn as New ADODB.Connection

m_conn.ConnectionString = ...
m_conn.Open

With ado_cmd
.ActiveConnection = m_conn
.CommandType = adCmdText
.CommandText = "INSERT INTO IMHIST(SELECT * FROM IMLN)"
.Execute
End With
End Sub


I hope this helps,

Patrick

 

[Schroeder]

Oh yeah, that too. I still suspect you'll need to remove the parentheses. It doesn't seem like they should be a problem but when I wrote a similar test statement in Access, I got a syntax error until I removed the parentheses.

 

[SemperFiDownUnda]

Actually you can use the Recordset object to execute SQL statement....but it is as useful as using a command object if the statement isn't a prepared sql statement that takes parameters. Normally if you have a SQL statement that is not accepting parameters and not returning a result set then you just use the connection object's execute method.

 

[CCLINT]

>"INSERT INTO IMHIST(SELECT * FROM IMLN)"

If all fields from both table are identical (number of fields and names)
"INSERT INTO IMHIST SELECT * FROM IMLN"

Or

If there are fewer fields in the table on the left than the table on the right, but the matching fields names are identical
"INSERT INTO IMHIST (Field1,Field2) SELECT * FROM IMLN"

Or

If there are fewer fields in the table on the Right than the table on the Left, but the matching fields names are identical

"INSERT INTO IMHIST SELECT Field1,Field2 FROM IMLN"

Or

Field names from both tables are different
INSERT INTO IMHIST (Field1,Field2) SELECT FieldA As Field1, FieldB As Field2 FROM IMLN"

etc, etc...