Help using recordset to insert data into a table **URGENT**

[tezza98]

Hi im creating a function to insert data from a MYOB file into an Access database. what im trying to do is copy entire tables from MYOB into ACCESS.
Im using a simple Access Database with so there is no back end, the table are part of the front end

i have a button to run this code

Private Sub Command0_Click()
 
Dim oRS As ADODB.Recordset
Dim DSN As String
Dim connection
Dim sql, sconstring
 
 
DSN = "TEST"
 
sql = "SELECT LineTypeID FROM SaleLines"
 
sconstring = "Driver:={MYOB ODBC};DSN=" & DSN & ";"
 
Set connection = CreateObject("ADODB.Connection")
 
connection.Open (sconstring)
Set oRS = connection.Execute(sql)
 
 
While Not oRS.EOF
 
DoCmd.RunSQL "INSERT into Test (LineTypeID) VALUES(" & oRS("LineTypeID") & ")"
 
oRS.MoveNext
Wend
       MsgBox "Completed"
End Sub
 

The Problem is that when i run it, from a form an input box appears with the title input paramater value
and a D in the grey area above the input field. the actual value of LineTypeID is D, and i want this inserted into the table, but the input box appears. Does anybody know why it does this and more importantly, how to make it work properly???

This is really a pain, to insert this way, if someone know how to use bulkinsert from from MYOB that would help healp

[tezza98]

i feeel likea real idiot now, i solved the problem by binding the oRS value to a variable, and that fixed the problem,(its always the last thing you try)the only problem i have now is that for every row this yes/no message appears

waring you are about to append 1 row(s)

anybody know how to remove it???

[neoabacus]

right before you start your inserts issue this command

DoCmd.SetWarnings False


then right after your done issue this command

DoCmd.SetWarnings True