[AccessD] Import from FoxPro

If all else fails there is the VFP 9.0 OLE DB Provider to get at the dbc 
file or directory of tables via ADO

I tried downloading the old ODBC Ver 6.0 driver from the main MS Foxpro 
page but that doesn't work
and maybe bad to install on WinXP or Win2000, Microsoft is unclear on this.


This code will read the VFP 9.0 Northwinds Foxpro Sample database that 
comes with the provider


'With the release of Microsoft Visual FoxPro 9.0,
'a new version of the Microsoft OLE DB Provider for Visual FoxPro is now 
available.
'The OLE DB provider is installed when Visual FoxPro 9.0 is installed
'and is typically saved in the C:\Program Files\Common Files\System\Ole 
DB folder.
'It is a single file that is named VfpOleDb.dll and is version 9.0.0.2412.
'To download the OLE DB provider for free, visit the following Microsoft 
Web site:
'http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
Sub testvfp()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
oConn.ConnectionString = "provider=vfpoledb.1;" & _
   "data source=C:\Program Files\Microsoft Visual FoxPro OLE DB 
Provider\Samples\Northwind\northwind.dbc"
  
' ConnStr = "Driver=Microsoft Visual Foxpro Driver; " + _
'   "UID=;SourceType=DBC;SourceDB=C:\VFP\Samples\Tastrade\Data\Tastrade.dbc"
oConn.Open

'Make sure the connection opened:
If Not CBool(oConn.State And adStateOpen) Then
  'Could not open the connection:
  MsgBox "Could not open connection..."
  Set oConn = Nothing
    Set oRS = Nothing
  Exit Sub
Else
  'Process data as usual here...

        Dim oCat As New ADOX.Catalog
        Dim oTbl As ADOX.Table
         Dim oTblc As ADOX.Table
         Dim oname As ADOX.DataTypeEnum
        Dim oCol As Column
       
            oCat.ActiveConnection = oConn

                Debug.Print "TableName   number of columns"
            For Each oTbl In oCat.Tables
                Debug.Print oTbl.Name & "   " & oTbl.Columns.Count
            Next

            Set oTblc = oCat.Tables("Customers")
             Debug.Print ADOX.DataTypeEnum.adChar; ADOX.DataTypeEnum.adChar

            For Each oCol In oTblc.Columns
                Debug.Print oCol.Name & ": " & oCol.DefinedSize & _
                          ": " & oCol.Type
            Next

oRS.Open "SELECT * FROM Customers", oConn

With oRS
  .MoveFirst
  Debug.Print oRS.Fields.Item(0), oRS.Fields.Item(1)
End With
End If
Dim rsSchema As ADODB.Recordset
Dim fld As ADODB.Field
Dim rCriteria As Variant
'Pass in the table name to retrieve index info. The other
'array parameters may be defined as follows:
'    TABLE_CATALOG  (first parameter)
'    TABLE_SCHEMA   (second)
'    INDEX_NAME     (third)
'    TYPE           (fourth)
'    TABLE_NAME     (fifth, e.g. "employee")

rCriteria = Array(Empty, Empty, Empty, Empty, "Customers")

Set rsSchema = oConn.OpenSchema(adSchemaIndexes, rCriteria)

Debug.Print "Recordcount: " & rsSchema.RecordCount

While Not rsSchema.EOF
      Debug.Print "==================================================="

   For Each fld In rsSchema.Fields
      Debug.Print fld.Name & ": " & fld.Value
      'Debug.Print "------------------------------------------------"
   Next
   rsSchema.MoveNext
Wend
rsSchema.Close
Set rsSchema = Nothing
 Set oConn = Nothing
End Sub

Joe Hecht wrote:

>I have had to through Excel sometimes for FP Tables.
>
>Check your properties after importing to Access
>
>Joe Hecht
>Los Angeles CA
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis,Virginia
>Sent: Friday, June 24, 2005 4:40 AM
>To: accessD at databaseadvisors.com
>Subject: [AccessD] Import from FoxPro
>
>Ok, what is the secret to importing from FoxPro to Access 2003? It is
>not in the list of types anymore. I looked through the KB articles but
>still have problems.
>
> 
>
>I pulled the tables up in Visual FoxPro, saved them out as dBase IV
>files, and then tried to import them into Access 2003.  I got the
>message:
>
> 
>
>"Unexpected Error from External Database Driver (8961)"
>
> 
>
>I was able to create an ODBC datasource based on a FoxPro driver that
>was already on my computer.  I tried to import the tables, and it looked
>like it was going to do it, but then I got an error, "Decimal field
>precision too small...
>
> 
>
>I'm running out of ideas.
>
> 
>
>Virginia
>
> 
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada