Append using Ado

Hello
I need to append the contents of a table "Temp" in a borland db to a table "Temp" in an access database using ADO
The source Database will be assigned from a file so I am only specifying Borland for this Question.The source could be any database .If answered could you show the code from connecting to closing.
Regards T
visualbasicAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Commented:
Here is an example of using a foxpro table to an access table there are only two fields, but you can easily change this to however many you want.

You will need references to microsoft activex data objects etc, and a command button on the form.

Ideally you should also set the recordset and connection objects to Nothing, probably in the form_unload event.

Dim WithEvents adoPrimaryRS As Recordset
Dim WithEvents adoSecondaryRS As Recordset
Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean

Private Sub Command1_Click()
    With adoPrimaryRS
        .MoveFirst
        Do
            With adoSecondaryRS
                .AddNew
                !Item = adoPrimaryRS!Item
                !Class = adoPrimaryRS!Class
                .Update
            End With
            .MoveNext
        Loop Until .EOF
    End With
End Sub

Private Sub Form_Load()
  Dim db As Connection
  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDASQL;dsn=Visual FoxPro Tables;uid=;pwd=;"

  Set adoPrimaryRS = New Recordset
  adoPrimaryRS.Open "select item,class from temp", db, adOpenStatic, adLockOptimistic


  mbDataChanged = False
 
  Dim dba As Connection
  Set dba = New Connection
  dba.CursorLocation = adUseClient
  dba.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=c:\testbed\cremosa.mdb;"

  Set adoSecondaryRS = New Recordset
  adoSecondaryRS.Open "select class,item from Temp", dba, adOpenStatic, adLockOptimistic


End Sub
0
 
visualbasicAuthor Commented:
Thanks Tim
        I was wondering was there an sql which could do the same function using ADO??
Cheers T
0
 
TimCotteeCommented:
I don't think that there is as the two recordsets are on different connections. The only way you could do that would be to add the non-access temp table as a linked odbc table in the access database then you probably could do an INSERT INTO ACCESSTEMP SELECT * FROM NON-ACCESSTEMP type SQL statement.

0
 
visualbasicAuthor Commented:
Thanks Tim
        I was wondering was there an sql which could do the same function using ADO??
Cheers T
0
All Courses

From novice to tech pro — start learning today.