How can i import data from a Visual FoxPro 6.0 to an Access 2003 D

[Guest]

I have been trying to import Data from a Visual FoxPro DB and be able to
manipulate the data with a RecordSet, i have tried using connection strings
with the ODBC provider and with the OleDB provider, in both cases i get that
the record set is empty with normal "SELECT * FROM table" statement, i have
made sure that the VBA editor has reference to the Visual FoxPro provider
(vfpoledb.dll), but until this moment no luck, the recordset allways come
empty.

Help on this will be greatly appreciated. Thank you in advance for your
feedback.

 

[Cindy Winegarden]

Hi Ricardo,

It really helps if you post your connection string and the code you're
using.

 

[Guest]

i'm trying to connect to a visual foxPro DB and a recordSet to it, but i
haven't been able to do it, i've tried with both providers the ODBC and the
OLEDB, here's the code so that you have a better idea :

*** START ***
Private Sub btnRetrievePay_Click()

Dim conn As ADODB.connection
Dim rsADO As ADODB.Recordset
Dim strConnect As String
Dim strSql As String

strSql = "SELECT * FROM checks"
' It is made sure that the check mark is selected for further process
If (CheckMark.Value = -1 And txtProvNum.Value <> "") Then

strConnect = "Provider=MSDASQL.1;Persist Security
Info=False;Extended Properties=""DSN=Visual FoxPro
Tables;UID=;SourceDB=E:\CHECKS.DBF;SourceType=DBF;Exclusive=No;
BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"""

MsgBox strConnect

Set conn = New ADODB.connection
conn.Open strConnect

Set rsADO = New ADODB.Recordset
rsADO.Open strSql, conn, adOpenForwardOnly, adLockReadOnly

If rsADO.AbsolutePosition > -1 Then
MsgBox "Contains Records"
Else
MsgBox "Does not contain Records"
End If

Else
MsgBox "Please make sure the Check Box has been selected"
End If
End Sub
*** END ***

With this
connectString = "Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=""DSN=Visual FoxPro
Tables;UID=;SourceDB=E:\CHECKS.DBF;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"""

Tells me that the file checks.dbf, i'm assuming that doesn't work with
visual FoxPro
--------------------------------------------------------------------------------------------------------
With this
ConnectString= "Provider=vfpoledb;Data Source=E:\CHECKS.DBF"

Access Hangs and ShutDown giving me a Bug error if i want to send the error
information to microsoft.

 

[Cindy Winegarden]

Hi Ricardo,

There are two varieties of Fox data - "free" tables and those associated
with a Database Container or DBC. The DBC has metadata about the DBFs it
"contains" and also houses stored procedures.

When you're connecting to a DBC you use ConnectString=
"Provider=vfpoledb;Data Source=E:\MyDbc.dbc" and the OLE DB data provider
will find your Checks.dbf from the metadata in the DBC.

When you're connecting to free tables (a DBC is not present) you only use
the path and the OLE DB data provider looks in the indicated directory for
any table named in your query: ConnectString= "Provider=vfpoledb;Data
Source=E:\"



--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

strConnect = "Provider=MSDASQL.1;Persist Security
Info=False;Extended Properties=""DSN=Visual FoxPro
Tables;UID=;SourceDB=E:\CHECKS.DBF;SourceType=DBF;Exclusive=No;
BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"""

 

[Guest]

I did tried what you told, and thank you for your response, but i still have
the same problem in both situation that the recordset is not getting the
information.

 

[Cindy Winegarden]

Hi Ricardo,

I ran your code in VB6 and got the "Does not contain Records" message
(rsADO's AbsolutePosition property was adPosUnknown in the Locals window).
However, the following code works in VB6:

Public Sub Main()

Dim conn As ADODB.Connection
Dim rsADO As ADODB.Recordset
Dim strConnect As String
Dim strSql As String

strSql = "SELECT * FROM checks"

strConnect = "Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=" & _
"DSN=Visual FoxPro
Tables;UID=;SourceDB=C:\;SourceType=DBF;Exclusive=No;" & _
"BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

Set conn = New ADODB.Connection
conn.Open strConnect

Set rsADO = New ADODB.Recordset
rsADO.Open strSql, conn, adOpenForwardOnly, adLockReadOnly

' My test table has 2 rows
MsgBox (rsADO.Fields(0).Name & " " & rsADO.Fields(0).Value)
rsADO.MoveNext
MsgBox (rsADO.Fields(0).Name & " " & rsADO.Fields(0).Value)

Stop ' to check values in the Locals window

End Sub