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

[Driss]

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.

[Paul Clement]

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)

[Driss]

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)

[Paul Clement]

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)

[Driss]

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)