We help IT Professionals succeed at work.

How to Link to a Foxpro 2.5 file?

rrr333222
rrr333222 asked
on
284 Views
Last Modified: 2012-06-07
I would like to Link (GetExternal Data/Link) to a Foxpro dbf file. But I don't see a category for Fox. When I use the dBaseIII category it does not seem to recognize the .cdx index file.

how do i do this??
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hello rrr333222,

you mean you don't have a filter for Foxpro that you could use to link tables?

In your Office CD there are additional data access filters you could install.

Run this program:

\VALUPACK\dataacc\dataacc.exe

Regards,
paasky
Perhaps you could rename the foxpro file to .dbf, so that Access treats is like a dBase III file...

Are you saying that you get an error, because of the .cdx, or that the .cdx is not controlloing behavior as you would like it to?

Brian

Author

Commented:
paasky -

I don't find Valupack on the office cd. Which CD is it on?



BrianWren -
I need the CDX file to be recognized too

Author

Commented:
paasky -

I don't find Valupack on the office cd. Which CD is it on?



BrianWren -
I need the CDX file to be recognized too

Author

Commented:
Paasky -
i scanned the four Office 2000 CDs for a file called dataacc.exe and it came up with nothing
Is there any reason that you can't import the table, then index it in Access?

Brian
can you run Office 2000 setup, and use that to install the converter?

Brian

Author

Commented:
I can't index it in Access as it's very large, and a live File from another system.

You mean i should re-install Access 2000?
You can use setup to  _modify_  yur setup.  For each element there are usually many components that can be removed or added.

For instance, Addins allows you to include different addins or remove them--the files, that is.

You can add/remove ActiveX comonents.

You can remove the 'tmplate' databases, (*.mdz).

Possibly the support for FoxPro can be found there.  

Excuse me for not reading a little more carefully.  I re-read now that you said 'Link,' not 'Import.'  Sorry...

Brian

Commented:
FoxPro 2.5 files have the extension .dbf as do FoxPro 2.6 files so there is no need to rename them.

In Access 97, there is a category in Files of type: Microsoft FoxPro (*.dbf) that I use to link to FoxPro 2.6 tables. When you do the link, it asks you for the associated .cdx file

Is FoxPro support really missing from Access 2000? I would find this strange since FoxPro is a Microsoft product.

Richard

Commented:
Adendum:

When connecting to Visual FoxPro tables you now need to set up a DSN to the table, then link to the table from the ODBC link in the link box. Although I don't have Access 2000, I suspect that the same procedure needs to be followed with 2.x tables.

The old FoxPro driver is not being supported for ODBC connections, so you have to use the Visual FoxPro driver to get to FoxPro 2.x tables. Whereas in the past when you directly linked to FoxPro 2.x tables you were asked for the corresponding CDX file, this is no longer the case when using the Visual FoxPro driver. You will be asked for the fields that constitute a unique record identifier (see below)

You can get the latest version of MDAC (where the Microsoft ODBC drivers are kept) from:
http://officeupdate.microsoft.com/2000/articles/AcFoxPro.htm

The link to the MDAC exe file is on that page

Download and install it then:

I don't have Access 2000 but the following steps work with Access 97 to to a DSN link to FoxPro 2.6 tables and you should be close enough for you to figure things out.

1. Start Access
2. File | Get External Data | Link Tables...
3. Files of type ODBC Databases()
4. Machine Data Source tab
5. Highlight FoxPro Files then click New
6. System Data Source radio button
7. Next
8. Microsoft Visual FoxPro Driver then click Next
9. Click Finish
10. Enter a name for the Data Source Name (DSN)
11. Select the Free Table Directory radio button
12. Click Browse and navigate to the directory containing your FoxPro tables
13. Click OK
14. Highlight the table(s) you want to link to
15. Click OK
16. Select the fields in the table(s) that constitute the unique record identifier (up to 10 can be picked per table)

Your table will now be linked to  your application and you can manipulate it as needed.

Hope this helps

Richard

BTW, if you need to set up DSN's on your client machines, it will probably be easier to use the 32 bit ODBC administrator (in the control panel) to create them rather than do them from access as I have described above. You can obviously do this on your development machine as well. In any case, you will probably need to install the latest version of MDAC on the clients.


CERTIFIED EXPERT

Commented:
rrr333222, sorry I assumed you're using Access 97 and the valupack comes only with 97 installation CD.

There's no option in Access 2000 to import or link foxpro tables using OleDB "native" drivers. However it's possible to use foxpro ODBC drivers to "work-around" this issue like repstein has suggested.

There is an article about this in Microsoft Knowledge Base. There are detailed instructions how to do the procedure.

http://support.microsoft.com/support/kb/articles/Q225/8/61.ASP?LNG=ENG&SA=ALLKB&FR=0

It's really weird that M$ is not providing foxpro filters with A2000. Maybe Access and FoxPro team have something against each other???

Regards,
Paasky
CERTIFIED EXPERT

Commented:
You will find additional information, drivers, patches, links etc. from MS Universal Data Access Web Site: http://www.microsoft.com/data/

Paasky

Author

Commented:
repstein  -
You hit the nail on its head.
So this then would mean that I would have to set up a DNS entry on each PC using this program?
Is there a way of making putting one MDB as as server to all others? so then I only have to link to this MDB?

Commented:
I have been using the ODBC Administrator on each PC to setup the file DNS. This only takes a minute or so. I give the DNS the same same as on the develoment machine, which preserves the table links on the mdb or mde file that is distributed.

I am pretty sure that you need to have the DNS on the machine. Even if the MDB is on the server, it is going to run locally on the workstation and the table link needs to have a local DSN to the data source. I've tested this on my network at home, and I get an ODBC error when I try to run the mdb from another workstation that links to a data source on a third workstation. If I add the DNS to the local machine, everything works fine. I haven't tested this on a formal server topology (my network at home is peer to peer) , however.

There may be a way to setup the DSN from code that some of the other experts can elucidate, but I haven't tried this myself.

You are probably going to want to map a drive on each workstation to the server directory, so a visit to each workstation may be needed anyway. When you setup the DSN you will be confirming that the link works, which is probably not a bad idea either.

If you have a limited number of workstations to configure, this will probably work ok. If you have lots of workstations, then I can look into this further for you, perhaps as a separate question.

Richard



CERTIFIED EXPERT

Commented:
Hello!

Here's two sample codes how to add/update DSN. The first function makes System DSN (uses external ODBC dll) and the other User DSN.

Note that parameters vary depending the ODBC driver you're using. Perhaps it would be a good idea to create entry manually and then check from registry what parameters there are. New registry data for System DSN should be written in key folder  \HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI


You should change the driver name to foxpro, look its exact name from ODBC Administrator's Drivers tab page.

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
    (ByVal hwndParent As Long, _
    ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Long
..
..
..
Public Sub AddSystemDsn()
Dim LngResult As Long

LngResult = SQLConfigDataSource(0, _
   4, _
   "Microsoft Access Driver (*.mdb)", _
   "DSN=Test" & Chr(0) & _
   "DBQ=g:\exchange\sample1.mdb" & Chr(0) & _
   "Description=My database description" & Chr(0) & Chr(0))

    Debug.Print LngResult

End Sub


----------------------------------------------------------

This code is almost the same what's in Access Help:

Sub RegisterDatabaseX()

    Dim dbsRegister As Database
    Dim strDescription As String
    Dim strAttributes As String
    Dim errLoop As Error

    ' Build keywords string.
    strDescription = InputBox("Enter a description " & _
        "for the database to be registered.")
    strAttributes = "Database=MyDatabase2" & _
        vbCr & "Description=" & strDescription & _
        vbCr & "DBQ=G:\Data\Customers.mdb"

    ' Update Windows Registry.
    On Error GoTo Err_Register

DBEngine.RegisterDatabase "MyDatabase", "Microsoft Access Driver (*.mdb)", _
        True, strAttributes
    On Error GoTo 0

    MsgBox "Use regedit.exe to view changes: " & _
        "HKEY_CURRENT_USER\" & _
        "Software\ODBC\ODBC.INI"

    Exit Sub

Err_Register:

    ' Notify user of any errors that result from
    ' the invalid data.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & _
                vbCr & errLoop.Description

Next errLoop
    End If

    Resume Next

End Sub

Hope this helps,
Paasky

Commented:
to paasky
Thanks for the help. I will try this too, if I can get it past my system administrators. They generally take a dim view of writing to registry settings on managed, imaged workstations. In fact, workstation policies prevent users from running regedit.

to rrr333222
I tried setting up a table on one workstation linked over the network to a FoxPro table on another workstation, then linking to the linked table on a server from a third workstation. Alas, Access wouldn't let me do this double link.

What does work is the following (if you really want to avoid managing DSN's on the user machines). This assumes that you want data to flow from the FoxPro table to the users.

1. Create a small server mdb that links to the FoxPro table
2. Create a local access table on the server with the same structure as the linked table (a MakeTable query is the easiest way to do this)
3. In the server mdb, have it periodically run an update query in which the local table values will be updated with new data from the FoxPro table, and any new data will be added to the local table (I prefer doing this in code using recordsets, but the update strategy is your choice)
4. Link your users to the local access table on the server

Since you can connect directly from access to the server side access table, there would be no need for a DNS or an ODBC connection.


This is not a "beautiful" solution, but if the FoxPro table doesn't change all that frequently, it should work just fine.

Richard

Commented:
to paasky

Is it advisable (or a really bad idea) to create an ODBC link directly in the registry (using SaveSetting) and bypass the ODBC administrator? I believe your code does the same thing, but this approach does give some extra flexibility and provides clearer documentation in the source code as to exactly what is being setup.

What I am thinking of specifically is a quick way to move a DSN from a test platform to production, where the only thing that is going to change is the SourceDB string in the registry. Or is it better to use your method first, and save the SaveSetting method for such changes. Or am I just out of my mind?


As an example, I did this (manually) to create a new FoxPro DNS in

HKEY_Local_Machine\Software\ODBC\ODBC.INI

by creating a new key
FoxPro New Link

and assigning values:

Default           {value not set}
BackgroundFetch   "Yes"
Collate           "Machine"
Description       ""
Driver            "c:\winnt\system32\vfpodbc.dll"
Exclusive         "No"
SetNoCountOn      "No"
SourceDB          "<the FoxPro database directory>"
SourceType        "DBF"

{these are the same keys as in a DSN I created using the ODBC Administrator}

and by creating a new key in
HKEY_Local_Machine\Software\ODBC\ODBC.INI\ODBC Data Sources

FoxPro New Link    "Microsoft Visual FoxPro Driver"

This seems to work just fine.

Ricahrd



CERTIFIED EXPERT

Commented:
repstein,

This is how I use the add DSN code with Oracle ODBC (an example).

When the application is installed into new workstation and started first time, the person who's installing the product is prompted by the form where (s)he must enter DSN name, Oracle TNSName (~ database name) and description. After pressing Continue "Add DSN" code is executed and application starts  linking external tables. If that fails, the error message appears and person is returned to DSN setting screen.

Later, that same form can be opened from application settings if there's a need to change DSN settings.

There are two reasons why to use this method instead of starting ODBC Administrator:

1. If you have, for eg. 25 workstations or more to install, it's much faster because you don't have to start two programs to connect ODBC tables.
2. There are also lots of extra settings which should be left as they are and which could confuse novice IT person in ODBC Administrator. Now (s)he needs just to press Continue (I've pre-set default values to form) in my application.

Regards,
Paasky

Commented:
paasky

Thanks for the info.

Richard
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Hello rrr333222, any news here?
CERTIFIED EXPERT

Commented:
rrr333222, any comments?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.