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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visualbasicAuthor Commented:
Thanks Tim
        I was wondering was there an sql which could do the same function using ADO??
Cheers T
0
TimCotteeHead of Software ServicesCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.