How to Link Visual FoxPro database to Access

I am unsuccessful at linking tables from a visual FoxPro database to MS Access 2010; however I have been able to import a table into Excel.

 I have download the Microsoft OLE DB Provider for Visual FoxPro 9.0 (C:\Program Files (x86)\Microsoft Visual FoxPro OLE DB Provider) and excel reconices this but access doesn't?

These are the steps I took in Access:

  • External Data
  • ODBC Database
  • Link to the data source by createing a linked table
  • Machine Data Source
  • New
  • Select a driver... Microsoft Visual FoxPro Driver
  • Finnish
  • Error You must firt install the current version of the Visual FoxPro ODBC Driver...

In Excel I have recorded the a macro which shows the steps i did with the GUI, and it works:

Sub VFP()
'
' VFP Macro

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=VFPOLEDB.1;Data Source=C:\TestNET\DATA\TestS.DBC;Mode=Share Deny None;Extended Properties="""";User ID="""";Mask Password=" _
        , _
        "False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="""";DELETED=True;CODEPAGE=1252;MVCOUNT=163" _
        , _
        "84;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("`client`")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\Dustin\Documents\My Data Sources\(Default) client.odc"
        .ListObject.DisplayName = "Table_Default__client"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

I am not good at writing code, but is there a way to link all  the VFP tables in MS Access through code, if the GUI doesn't work?

|

Hi Rusty,

 

Have you tried installing the FoxPro ODBC driver?  If not, you could try going to the link below and install the English version of VFPODBC.MSI (not VFPODBC.msm).

 

Visual FoxPro ODBC Driver - http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

 

Best Regards,

Nathan Ost

Microsoft Online Community Support

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks Nathan

I downloaded the English version of VFPODBC.MSI.

I was very excited as it got me to the point where I could see all the tables; however when I selected one I received a different error.

  • External Data
  • ODBC Database
  • Link to the data source by creating a linked table
  • Machine Data Source
  • New
  • Select a driver... Microsoft Visual FoxPro Driver
  • Configure Connection
    •  Visual FoxPro database (.DBC)
    • Path: C:\...\Data\---.DBC
  • Import Objects
    • All the tables are shown
    • Selected a tabled
  • Error
    • Could not execute query; could not find linked table.
    • [Microsoft][ODBC Visual FoxPro Driver]Not a table.(#123)

So i guess i'm still stuck.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Does this happen with any table you select or just certain tables?

 

Best Regards,
Nathan Ost
Microsoft Online Community Support

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Nathan, thanks for getting back to me again!

 

There are about 60 tables and the error (Could not execute query...) shows up with everyone except two tables named defaults and errlog. Either tables are useful to me; I don’t know VFP, but I would imagine they must be used for administrators or the program itself.

FYI - Excel can import any table I want it to one at a time as described in the first question?

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Ever find a solution to the error "Counl not execute query......."

I am stuck there also.

bob

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Please help me.
I cannot find the password of two access databases.
They were built from Visual Foxpro9.
This is its link
http://www.mediafire.com/
Thanks in advance.
*** Email address is removed for privacy ***

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Are you saying that visual foxpro built MS Access databases?  sounds wierd.

Can you open the access databases using MS Access?

Your web link is password protected.

Bob


Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I can not the access database by using  MS Access.
I mean the Programmer use Visual Foxpro 9 writing the program by using access 2007 as its database.
*** Email address is removed for privacy ***

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I have the same issue. I can import all I want in to Excel no problem but cannot for the life of me attach to or import into Access directly. Nothing but errors.

This is the .dsn created through Access which does not work:

[ODBC]
DRIVER=Microsoft Visual FoxPro Driver
UID=
Deleted=Yes
Null=Yes
Collate=Machine
BackgroundFetch=Yes
Exclusive=No
SourceType=DBC
SourceDB=F:\DataSk\Spinneret\dbfs\manex.dbc

Gives reserved error (-7778) there is no message for this error.

This is the connection string in Excel which works fine for 1 table:

DSN=Visual FoxPro Database;UID=;;SourceDB=f:\DataSk\Spinneret;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

With a query of:

SELECT * FROM `bom_det`

Microsoft are you listening?

Kim

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

It sounds like no one has found a comprehensive solution to this problem, but I might as well report what I'm experiencing anyway.

I have an Access database which has several links to Visual FoxPro tables
(free tables, not part of a database).  My office recently upgraded from
XP to Win 7, and at the same time Access was updated to Access 2010.
After this update, I could no longer link tables in Access via the dBase
driver if the DBF file name was longer than 8 characters.

After doing some research, I found that the workaround was to reference the
short file name when setting up the link.  This worked for most of my DBF
tables, but there are some that, when I try to link (and referencing the
short name), I get the error:

"External table is not in the expected format."

Upon Googling this, the only response to others asking about this was
that the error is due to the presence of memo fields.  However, NONE of
the tables I need to link to have memo fields.

So my next step was to try linking the tables via ODBC.  For the Data
Source, I choose "Visual FoxPro tables".  Although I didn't encounter any
tables that couldn't be imported this way, after importing, when trying
to open the tables, some of them gave this error:

"Could not execute query; could not find linked table.

[Microsoft][ODBC Visual FoxPro Driver]File '<table name>.dbf' does not
exist (#173)."

This error message is rather inconsistent, in that not only some tables
open and others don't, but when I quit Access and restart, the tables
that are openable and those that are not are CHANGED from the last
Access session.

What's even weirder is that, for those tables that return that error,
they CAN be opened in Design View (though of course the structure can't
be change).  What's more, for some of these tables, after opening them
in Design View I was able to open them in Datasheet View.  BUT, NOT all
of them.  There are some that, no matter what I try, keep returning the
error message above when I try to open them.

I did some more Googling, but most of the pages where this problem was
described either didn't get answers, or the answer was to make sure the
latest VFP ODBC driver was installed in the ODBC Data Source Administrator.
Based on what I saw when Googling for "Visual Fox Pro ODBC driver",
I'm quite sure I have the latest driver.

However, I also note that Microsoft is encouraging users to abandon the
ODBC driver and instead use the OLE driver.  But I have no idea what to
do with the OLE driver nor how to access it from within Access.

(BTW, Access 2010 is 32-bit, and the version of the ODBC Data Source
Administrator I'm using is also 32-bit.)

I would appreciate any help/suggestions as to what I can do to link all
of the DBF tables that need linking, whether through the dBase driver,
ODBC, or something to do with OLE.

Thanks,

Chris

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 3, 2021 Views 10,859 Applies to: