Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: ADO/VBA - Copy remote data into a local table

    Hi Folks

    I need to copy data from remote table into a local ms access (currentdb) table, I have the routines to connect to a remote PostgreSQL database and get the data into a recordset but I dont know how to insert this recordset into a local table(s), here's the code I have sofar, please help thanks.


    If PostgreSQLADOConnection() Then ' This is my global connection

    Dim sqlGetPrices As String
    Dim objPrcsADO As ADODB.Recordset
    Set cn = cPostgreSQL
    sqlGetPrices = "SELECT product_id, unit_price FROM tblprice WHERE object_type='friut'"
    Set objPrcsADO = cPostgreSQL.Execute(sqlGetPrices, , adLockReadOnly)

    With objPrcsADO

    'Here I need to add routines to insert data into a local tables

    End With

    Set objPrcsADO = Nothing
    End If

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: ADO/VBA - Copy remote data into a local table

    Here is a general routine, modify it to fit your table structures:

    Do While Not objPrcsADO.Eof

    cn.Execute "INSERT TAble1(product_id, unit_price) SELECT '" & objPrcsADO.Fields("product_id") & "' , '" & objPrcsADO.fields("unit_price") & "'"

    loop
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Re: ADO/VBA - Copy remote data into a local table

    Originally posted by SCIROCCO
    Here is a general routine, modify it to fit your table structures:

    Do While Not objPrcsADO.Eof

    cn.Execute "INSERT TAble1(product_id, unit_price) SELECT '" & objPrcsADO.Fields("product_id") & "' , '" & objPrcsADO.fields("unit_price") & "'"

    loop
    Thank !! do I have to declare "cn" for local database

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: ADO/VBA - Copy remote data into a local table

    Yes you will have to declare and open a connection to your local database.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    Hi Again

    The following code results in an indefinite loop of inserts into the table with just one record, questions :

    1) Why an indefinite loop
    2) Why just one record while in the remote table there are about a dozone records

    ---Code---

    If PostgreSQLADOConnection() Then ' This is my global connection

    Dim sqlGetPrices As String
    Dim objPrcsADO As ADODB.Recordset
    Set cn = cPostgreSQL
    Set oConn = CurrentProject.Connection
    sqlGetPrices = "SELECT product_id, unit_price FROM tblprice WHERE object_type='friut'"
    Set objPrcsADO = cPostgreSQL.Execute(sqlGetPrices, , adLockReadOnly)

    Do While Not objPrcsADO.Eof

    oConn.Execute "INSERT INTO tblTest(product_id, unit_price) SELECT '" & objPrcsADO.Fields("product_id") & "' , '" & objPrcsADO.fields("unit_price") & "'"

    loop

    Set objPrcsADO = Nothing
    Set cPostgreSQL = Nothing

    End If


    ---Code---

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Add the objPrcsADO.MoveNext in your loop so that you cycle through your recordset. i.e.

    Do While Not objPrcsADO.Eof

    oConn.Execute "INSERT INTO tblTest(product_id, unit_price) SELECT '" & objPrcsADO.Fields("product_id") & "' , '" & objPrcsADO.fields("unit_price") & "'"

    objPrcsADO.MoveNext

    loop
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •