ADO to Select INTO a VFP .dbf from an Access table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: ADO to Select INTO a VFP .dbf from an Access table

  1. #1
    David Guest

    ADO to Select INTO a VFP .dbf from an Access table


    Can anyone help me with this?
    I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual foxpro
    database container to expose a .dbf file the other using the access provider
    to open an access database to expose a table. I want to use a Select...Into
    string to insert all the the data from the access table into the fox pro
    table..... What is my syntax...? Here is what I have:

    cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB

    cnn.Execute "Insert Into vfpfile Select * From accesstable;"
    thanks in advance
    D-

  2. #2
    Vikas Guest

    Re: ADO to Select INTO a VFP .dbf from an Access table


    Try this out:
    "select * from accesstable insert into vfptable"

    I haven't tried this out but it should work.

    Bye





    "David" <david@cdsconsulting.mailbox.as> wrote:
    >
    >Can anyone help me with this?
    >I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual foxpro
    >database container to expose a .dbf file the other using the access provider
    >to open an access database to expose a table. I want to use a Select...Into
    >string to insert all the the data from the access table into the fox pro
    >table..... What is my syntax...? Here is what I have:
    >
    > cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    > cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
    >
    > cnn.Execute "Insert Into vfpfile Select * From accesstable;"
    >thanks in advance
    >D-



  3. #3
    David Guest

    Re: ADO to Select INTO a VFP .dbf from an Access table


    No, this doesn't work.... syntax error generated


    "Vikas" <dvikas@sify.com> wrote:
    >
    >Try this out:
    >"select * from accesstable insert into vfptable"
    >
    >I haven't tried this out but it should work.
    >
    >Bye
    >
    >
    >
    >
    >
    >"David" <david@cdsconsulting.mailbox.as> wrote:
    >>
    >>Can anyone help me with this?
    >>I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual

    foxpro
    >>database container to expose a .dbf file the other using the access provider
    >>to open an access database to expose a table. I want to use a Select...Into
    >>string to insert all the the data from the access table into the fox pro
    >>table..... What is my syntax...? Here is what I have:
    >>
    >> cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    >> cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
    >>
    >> cnn.Execute "Insert Into vfpfile Select * From accesstable;"
    >>thanks in advance
    >>D-

    >



  4. #4
    David Guest

    Re: ADO to Select INTO a VFP .dbf from an Access table


    WIth a few changes this ALMOST works.... BUT all it does is create a new table
    in my cnn2|rs2 called DavidPHCPROM.... it doesn't append the tblImport into
    my cnn|rs table DavidPHCPROM that already exists....


    Set cnn = New ADODB.Connection
    Set cnn2 = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    strFox = dirDirectory.Path & "\" & dbc
    strMDB = App.Path & "\" & mdb
    strText = fle
    strBilldate = dte

    cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    rs.Open "DavidPHCPROM", cnn, adOpenDynamic, adLockOptimistic, adCmdText

    cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
    rs2.Open "tblImport", cnn2, adOpenKeyset, adLockOptimistic, adCmdTable
    cnn2.Execute "Update tblImport Set tblImport.BillDate = " & dte & ";"
    cnn2.Execute "Select * Into [DavidPHCPROM] from tblImport;"


    "Vikas" <dvikas@sify.com> wrote:
    >
    >Try this out:
    >"select * from accesstable insert into vfptable"
    >
    >I haven't tried this out but it should work.
    >
    >Bye
    >
    >
    >
    >
    >
    >"David" <david@cdsconsulting.mailbox.as> wrote:
    >>
    >>Can anyone help me with this?
    >>I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual

    foxpro
    >>database container to expose a .dbf file the other using the access provider
    >>to open an access database to expose a table. I want to use a Select...Into
    >>string to insert all the the data from the access table into the fox pro
    >>table..... What is my syntax...? Here is what I have:
    >>
    >> cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    >> cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
    >>
    >> cnn.Execute "Insert Into vfpfile Select * From accesstable;"
    >>thanks in advance
    >>D-

    >



  5. #5
    marc Guest

    Re: ADO to Select INTO a VFP .dbf from an Access table


    David

    INSERT INTO [;DATABASE=database directory].table1 ( fields )
    SELECT fields FROM table2;

    You can also build the query in query designer, in Access 97, select append
    query then right click where the tables are placed in the query design screen
    and select properties. Under properties you will see source and destination
    databases, just select the appropriate databases. I am sure it similiar
    in 2000.

    Marc


  6. #6
    David Guest

    Re: ADO to Select INTO a VFP .dbf from an Access table


    Thanks for your suggestion but Iím not using access to do this. Iím using
    Visual Basic 6 to open 2 ADO connections:


    cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
    cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB


    then executing this query with the connection object:

    cnn.Execute "Insert Into vfpfile Select * From accesstable;"

    The connection objects donít understand that the source data and the destination
    table are in 2 different ADO connectionsÖ.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center