By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

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

P: 38
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim oRS As ADODB.Recordset
  4. Dim DSN As String
  5. Dim connection
  6. Dim sql, sconstring
  7.  
  8.  
  9. DSN = "TEST"
  10.  
  11. sql = "SELECT LineTypeID FROM SaleLines"
  12.  
  13. sconstring = "Driver:={MYOB ODBC};DSN=" & DSN & ";"
  14.  
  15. Set connection = CreateObject("ADODB.Connection")
  16.  
  17. connection.Open (sconstring)
  18. Set oRS = connection.Execute(sql)
  19.  
  20.  
  21. While Not oRS.EOF
  22.  
  23. DoCmd.RunSQL "INSERT into Test (LineTypeID) VALUES(" & oRS("LineTypeID") & ")"
  24.  
  25. oRS.MoveNext
  26. Wend
  27.        MsgBox "Completed"
  28. End Sub
  29.  
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
Apr 18 '07 #1
Share this Question
Share on Google+
2 Replies


P: 38
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???
Apr 18 '07 #2

P: 1
right before you start your inserts issue this command

DoCmd.SetWarnings False


then right after your done issue this command

DoCmd.SetWarnings True
May 24 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.