insert query + DAO module

[maravinds]

Hallo Everyone,

In the following code i extract a column from one table and and i am inserting data to the other value.

the two msgbox used display the right value but when i try to insert it via insert query the value is not getting updated.






Private Sub Command0_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdef As DAO.QueryDef



Dim temp As String

Dim strsql As String

Set db = DBEngine(0)(0)


Set rs = db.OpenRecordset( _
"SELECT * FROM Table1", _
Type:=dbOpenDynaset)


While Not rs.EOF

'MsgBox rs!Name

temp = rs!Name

'MsgBox temp


strsql = "Insert into [Table2](name) values (temp);"

DoCmd.RunSQL (strsql)



rs.MoveNext

Wend
rs.Close
Set rs = Nothing

End Sub




with regards,
aravind.s

 

[MarkK]

You must evaluate the variable outside the string.

strsql = "INSERT INTO Table2 ( name ) values (" & temp & ");"

or simplify the whole procedure to

Private Sub cmd0_Click()
  CurrentDb.Execute _
    "INSERT INTO table2 ( name ) " & _
    "SELECT name " & _
    "FROM table1;"
End Sub