Import or link to VisualFoxpro using OLE DB instead of ODBC

[InstructionWhich7142]

I've used ODBC for most of the tables but there are some that use VFP9.0 features that aren't support by ODBC (vfp6), the suggestion is to use OLE DB, I can get a connection string to execute like this:

Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "Provider=vfpoledb;Data Source=\\CompZ\Compop_Z.dbc"
con.Open

but I don't know what to do from there, even if I get it as a recordset that still doesn't help as I don't know how to get a recordset with unknown field names into a table?
I want to write this as a function that'd work for any table, not a specific table (also most tables have 30+ fields so manual isn't really an option anyway)
Thanks

 

[Ranman256]

in excel, you can get the recordset then paste it

run this code in excel, it will produce the fieldnames and data.
then import it into access.

Public Sub CopyRST()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB
Dim vProvid
Dim fld
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

DB = "C:\folder\genericDB.mdb"
vProvid = "Microsoft.Jet.OLEDB.4.0" ' or for Sqlsvr: "SQLOLEDB"


With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Data Source=" & DB & ";Jet OLEDB"
End With

Set rs = con.Execute("qsNames_Xmen")
Range("a1").Select
For Each fld In rs.Fields
   ActiveCell.Value = fld.Name
   ActiveCell.Offset(0, 1).Select 'next column
Next

ActiveWorkbook.Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
rs.Close
con.Close
End Sub

 

[InstructionWhich7142]

Hi, using excel sounds like a bit of a bodge, surely there's risks to numeric data where it could end up with the wrong formatting, same with dates etc?

Ideally I'd like to do it directly to tables within Access with ADODB somehow? surely it must be possible if you can get it into a recordset

If I must buhtch (bodge but amusing) then I can do that with foxpro to copy the tables into an older DBF format that Access can read with ODBC