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.SetWarnin gs False


then right after your done issue this command

DoCmd.SetWarnin gs True