Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

  1. #1
    Driss Guest

    Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

    SInce the Jet 4.0 version doesn't support any more the "Foxpro" drivers ...

    How can you link or Insert a Foxpro table (dbf), in a Access file (2000) ??

    Here is the sample i use to Insert a DBase Table in Access :

    _____________________________________

    Set oConnMDB = New ADODB.Connection

    oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\MyFile.mdb"

    StrSQL = _
    "SELECT * INTO " & _
    "[C:\MyFile.mdb].[NewTableName] " & _
    "FROM " & _
    "[dBase IV;database=C:\MyDBFPath].[MyDBFTableName] "

    oConnMDB.Execute StrSQL
    ______________________________________

    What is the technic for a FoxPro File ?? Knowing that :

    - I connect to an Access File like this:

    oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\MyFile.mdb"


    - and I connect to an Foxpro File like this ?

    oConn.Open & _
    "Driver={Microsoft FoxPro Driver};" & _
    "SourceType=DBF;" & _
    "SourceDB=C:\FoxproDBFFolder;" & _
    "Exclusive=No;"


    -> So the SQL execution string can not work ...

    I'm new in ADO ... If you have any code sample, or any help, where to start
    ? Thank's to any one ...

    Driss.





  2. #2
    Paul Clement Guest

    Re: Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

    On Mon, 15 Jul 2002 00:44:59 +0200, "Driss" <RemoveMe.ndroop@9online.fr> wrote:

    ¤ SInce the Jet 4.0 version doesn't support any more the "Foxpro" drivers ...
    ¤
    ¤ How can you link or Insert a Foxpro table (dbf), in a Access file (2000) ??
    ¤
    ¤ Here is the sample i use to Insert a DBase Table in Access :
    ¤
    ¤ _____________________________________
    ¤
    ¤ Set oConnMDB = New ADODB.Connection
    ¤
    ¤ oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    ¤ Source=C:\MyFile.mdb"
    ¤
    ¤ StrSQL = _
    ¤ "SELECT * INTO " & _
    ¤ "[C:\MyFile.mdb].[NewTableName] " & _
    ¤ "FROM " & _
    ¤ "[dBase IV;database=C:\MyDBFPath].[MyDBFTableName] "
    ¤
    ¤ oConnMDB.Execute StrSQL
    ¤ ______________________________________
    ¤
    ¤ What is the technic for a FoxPro File ?? Knowing that :
    ¤
    ¤ - I connect to an Access File like this:
    ¤
    ¤ oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    ¤ Source=C:\MyFile.mdb"
    ¤
    ¤
    ¤ - and I connect to an Foxpro File like this ?
    ¤
    ¤ oConn.Open & _
    ¤ "Driver={Microsoft FoxPro Driver};" & _
    ¤ "SourceType=DBF;" & _
    ¤ "SourceDB=C:\FoxproDBFFolder;" & _
    ¤ "Exclusive=No;"
    ¤
    ¤
    ¤ -> So the SQL execution string can not work ...
    ¤
    ¤ I'm new in ADO ... If you have any code sample, or any help, where to start
    ¤ ? Thank's to any one ...

    See if the following helps:

    http://support.microsoft.com/default...;EN-US;q165492

    In addition, I believe that if you have Visual FoxPro installed there is an OLEDB driver available.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  3. #3
    Driss Guest

    Re: Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

    Thank you Paul for your answer,

    Not really what i need, but a good sample to know how to connect to Visual
    Foxpro DBF/DBC file.

    -> My problem is how can I Link or Insert a Visual Foxpro Table in a
    MSAccess file, not 'only' connect to a Visual Foxpro Table.

    => But i think it is not possible to link a Visual Foxpro Table or Foxpro
    Table in MSAccess with Visual Basic using SQL and ADO. Right ? :-(

    I have to open 2 connections, one for MSAccess using JET, and the other one
    using Foxpro Drivers, and create a new Table in MSAccess, then go thru all
    the rows of the Foxpro Table, to create a copy of each row, one by one, to
    the new MSAccess Table.

    No other way right ?

    Driss.


    What is

    "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> a écrit dans le
    message de news: gck5jukmei3u6ri0677nr3gqb2bfdcgtnn@4ax.com...
    > On Mon, 15 Jul 2002 00:44:59 +0200, "Driss" <RemoveMe.ndroop@9online.fr>

    wrote:
    >
    > ¤ SInce the Jet 4.0 version doesn't support any more the "Foxpro" drivers

    ....
    > ¤
    > ¤ How can you link or Insert a Foxpro table (dbf), in a Access file (2000)

    ??
    > ¤
    > ¤ Here is the sample i use to Insert a DBase Table in Access :
    > ¤
    > ¤ _____________________________________
    > ¤
    > ¤ Set oConnMDB = New ADODB.Connection
    > ¤
    > ¤ oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    > ¤ Source=C:\MyFile.mdb"
    > ¤
    > ¤ StrSQL = _
    > ¤ "SELECT * INTO " & _
    > ¤ "[C:\MyFile.mdb].[NewTableName] " & _
    > ¤ "FROM " & _
    > ¤ "[dBase IV;database=C:\MyDBFPath].[MyDBFTableName] "
    > ¤
    > ¤ oConnMDB.Execute StrSQL
    > ¤ ______________________________________
    > ¤
    > ¤ What is the technic for a FoxPro File ?? Knowing that :
    > ¤
    > ¤ - I connect to an Access File like this:
    > ¤
    > ¤ oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    > ¤ Source=C:\MyFile.mdb"
    > ¤
    > ¤
    > ¤ - and I connect to an Foxpro File like this ?
    > ¤
    > ¤ oConn.Open & _
    > ¤ "Driver={Microsoft FoxPro Driver};" & _
    > ¤ "SourceType=DBF;" & _
    > ¤ "SourceDB=C:\FoxproDBFFolder;" & _
    > ¤ "Exclusive=No;"
    > ¤
    > ¤
    > ¤ -> So the SQL execution string can not work ...
    > ¤
    > ¤ I'm new in ADO ... If you have any code sample, or any help, where to

    start
    > ¤ ? Thank's to any one ...
    >
    > See if the following helps:
    >
    > http://support.microsoft.com/default...;EN-US;q165492
    >
    > In addition, I believe that if you have Visual FoxPro installed there is

    an OLEDB driver available.
    >
    >
    > Paul ~~~ pclement@ameritech.net
    > Microsoft MVP (Visual Basic)




  4. #4
    Paul Clement Guest

    Re: Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

    On Mon, 15 Jul 2002 21:15:43 +0200, "Driss" <RemoveMe.ndroop@9online.fr> wrote:

    ¤ Thank you Paul for your answer,
    ¤
    ¤ Not really what i need, but a good sample to know how to connect to Visual
    ¤ Foxpro DBF/DBC file.
    ¤
    ¤ -> My problem is how can I Link or Insert a Visual Foxpro Table in a
    ¤ MSAccess file, not 'only' connect to a Visual Foxpro Table.
    ¤
    ¤ => But i think it is not possible to link a Visual Foxpro Table or Foxpro
    ¤ Table in MSAccess with Visual Basic using SQL and ADO. Right ? :-(
    ¤
    ¤ I have to open 2 connections, one for MSAccess using JET, and the other one
    ¤ using Foxpro Drivers, and create a new Table in MSAccess, then go thru all
    ¤ the rows of the Foxpro Table, to create a copy of each row, one by one, to
    ¤ the new MSAccess Table.
    ¤
    ¤ No other way right ?
    ¤
    ¤ Driss.

    The only way I know of to do this is with DAO. If you have the FoxPro OLEDB driver (included with
    Visual FoxPro) you might be able to do it with ADOX (Microsoft ADO 2.x for DDL and Security), but I
    can't verify this. I know that it will not work with the FoxPro ODBC driver:

    Sub CreateAttachedFoxProDAO()

    Dim tdfLinked As TableDef

    ' Create a new TableDef, set its Connect and
    ' SourceTableName properties based on the passed
    ' arguments, and append it to the TableDefs collection.
    Set tdfLinked = CurrentDb.CreateTableDef("FoxProTable")

    tdfLinked.Connect = "FoxPro 3.0;DATABASE=D:\My Documents\FoxPro"

    tdfLinked.SourceTableName = "FPDB#dbf"
    CurrentDb.TableDefs.Append tdfLinked

    End Sub


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  5. #5
    Driss Guest

    Re: Linking Foxpro Table in Access file with ADO & Jet OLE 4.0 : Please help !!

    Thank you Paul again for your answer,

    I know how to do that that with DAO but :

    - First, since you lost 'Foxpro Drivers' with DAO when you update to Jet
    4.0. ... :-( ... Programs developped with Jet 3.51, still not work when you
    update to Jet 4.0 (This is a really problem)
    - Second, seeing that Microsoft is burying DAO, i don't want to go back to
    it, but keep on learning on ADO.


    I have seen a sample given by 'Val Mazur', on a SQL Problem, here is the
    solution that he gave for an other problem :

    _________________________________________________________>

    ** .. Create a new table in Access and insert in this new table data from a
    SQL Server ..

    You could use different approaches to do that** . First is to use DTS COM
    model, which comes with SQL Server. Second is to use OLEDB provider for Jet.
    Here is an small example with Jet

    Dim loConnection As ADODB.Connection
    Dim lcSQL As String

    Set loConnection = New ADODB.Connection
    loConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=place path to mdb file here;"


    lcSQL = "SELECT * INTO Products_Backup FROM [odbc;Driver={SQL
    Server};Server=(local);"
    lcSQL = lcSQL & "Database=northwind;Uid=sa;Pwd=;].Products"

    loConnection.Execute lcSQL, , adExecuteNoRecords
    loConnection.Close

    --
    Val Mazur
    Microsoft MVP

    <_________________________________________________________


    So, i have tried with ODBC Foxpro Drivers to do like this to create a Table
    in Access :

    _________________________________________________________>

    Set oConnMDB = New ADODB.Connection

    oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\MyFile.mdb"

    oConnMDB.Execute _
    "SELECT * INTO " & _
    "[C:\MyFile.mdb].[NewTableName] " & _
    "FROM " & _
    "[ODBC;Driver={Microsoft FoxPro Driver
    (*.dbf)};DataBase=C:\MyFoxproPath;].[FoxProTableName.dbf]"

    <_________________________________________________________

    But I get the error :

    -2147467259

    -> Not possible to use ODBC to Import, Export or Link an extern database
    Table Microsoft Jet or ISAM to your database. (some thing like that after
    traduction, it's in french)

    -> Impossible d'utiliser ODBC pour importer de, exporter vers, ou attacher
    une table de base de données externe Microsoft Jet ou ISAM à votre base de
    données.


    So, Paul, or anyone ... have you got an idea ?

    Thank's

    Driss



    "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> a écrit dans le
    message de news: 5rb8juge2k1f71pfqqgv7sn6frdrkblsj2@4ax.com...
    > On Mon, 15 Jul 2002 21:15:43 +0200, "Driss" <RemoveMe.ndroop@9online.fr>

    wrote:
    >
    > ¤ Thank you Paul for your answer,
    > ¤
    > ¤ Not really what i need, but a good sample to know how to connect to

    Visual
    > ¤ Foxpro DBF/DBC file.
    > ¤
    > ¤ -> My problem is how can I Link or Insert a Visual Foxpro Table in a
    > ¤ MSAccess file, not 'only' connect to a Visual Foxpro Table.
    > ¤
    > ¤ => But i think it is not possible to link a Visual Foxpro Table or

    Foxpro
    > ¤ Table in MSAccess with Visual Basic using SQL and ADO. Right ? :-(
    > ¤
    > ¤ I have to open 2 connections, one for MSAccess using JET, and the other

    one
    > ¤ using Foxpro Drivers, and create a new Table in MSAccess, then go thru

    all
    > ¤ the rows of the Foxpro Table, to create a copy of each row, one by one,

    to
    > ¤ the new MSAccess Table.
    > ¤
    > ¤ No other way right ?
    > ¤
    > ¤ Driss.
    >
    > The only way I know of to do this is with DAO. If you have the FoxPro

    OLEDB driver (included with
    > Visual FoxPro) you might be able to do it with ADOX (Microsoft ADO 2.x

    for DDL and Security), but I
    > can't verify this. I know that it will not work with the FoxPro ODBC

    driver:
    >
    > Sub CreateAttachedFoxProDAO()
    >
    > Dim tdfLinked As TableDef
    >
    > ' Create a new TableDef, set its Connect and
    > ' SourceTableName properties based on the passed
    > ' arguments, and append it to the TableDefs collection.
    > Set tdfLinked = CurrentDb.CreateTableDef("FoxProTable")
    >
    > tdfLinked.Connect = "FoxPro 3.0;DATABASE=D:\My Documents\FoxPro"
    >
    > tdfLinked.SourceTableName = "FPDB#dbf"
    > CurrentDb.TableDefs.Append tdfLinked
    >
    > End Sub
    >
    >
    > Paul ~~~ pclement@ameritech.net
    > Microsoft MVP (Visual Basic)




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