Link to home
Start Free TrialLog in
Avatar of vaidisok
vaidisok

asked on

Link to FoxPro (DBF): Acc97 -> Acc2K ?..

Hello everybody,

I have MS Access'97 database which has links to different external files: CSV, TXT, DBF (DOS dBase and Windows FoxPro). All those files are stored on local network (\\server\share\foler\file). I've tried to upgrade it to MS Access'2000 and everything works fine, except of external FoxPro DBF tables - direct liks are not supported in new Access versions (I need to use ODBC).

How should I set-up this ODBC connection to make this new MS Access'2000 database work on all network computers in our office (and I do not want to do any changes/setups on every individual PC which uses this database).

P.S. I can't use dBase drivers to link those FoxPro tables as in such case I see wrong national characters (DOS/Windows encoding)... :(

Any ideas?
--
Thanks in advance - Vaidis OK
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Do you still actively use FoxPro?  If not, consider permanently importing the data into Access.  Otherwise, use the ODBC drivers installed with Windows.  You can visit this page for a look at how to create a connection with using a DSN file:

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

There is not a specific example for FoxPro, but it should give you a firm idea of how to proceed.  If you cannot use a DSN-less connection, you will have to create a DSN file and copy it to each system needing connectivity.
Avatar of vaidisok
vaidisok

ASKER

I do not use FoxPro at all. But unfortunatelly our data providers (we have aprox 30 different data providers) are sending files in different formats.
New files we receive every month. So I just replace old files by new ones and run queries in my database to collect data from all linked tables.

Everything worked fine until I've tried to convert from Acc'97 to Acc'2000...

I will look at your recommendation (maybe it will be possible to set-up ODBC connection using VB code in Access database?).
What I want to have is independent MS Access database which could be started without any additional set-ups from any PC (this works fine with Acc'97).
Setting up the ODBC connection in VB is exactly where I was pointing you, and should work perfectly for what you need to do.

I recently had to work with a project remarkably similar to this.  A stock market trend analysis firm received stock and fund data monthly in the form of a FoxPro database file.  The provider had meddled with the file to ensure it could not be opened alone with FoxPro, and you were forced to use their software to open it.  That did not work for my project, since I needed access to raw (vs interpreted) data.  So I found the FoxPro file format on the web, wrote a procedure to import the tables manually, and went on my happy little way.  It worked exceedingly well, and I'm pretty sure I still have a copy of the routine somewhere...at the very least, it is with the client I created it for.

Hello once again,
I've tried to understand how I should use code, provided in your link, but it seems that I need a litle bit more support...
Could you, please, give me some example (it doesn't need to be FoxPro, can be other Access db etc.) how to use it?

What I know is path and names of FoxPro DBF files, what I want to have is linked tables in Access Tables tab.

I mean - how to use such connection code:
  oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
             "SourceType=DBF;" & _
             "SourceDB=" & MyDataPath & ";" & _
             "Exclusive=No"

What type of object is oConn?

Thanks!
Vaidis OK
oConn can be an ADODB.Connection object.  That's probably the part you were missing?  :)
sorry, I still do not understand...
could you, please, give me some working code example?

P.S. I've solved my problem by linking FoxPro DBF as dBase IV DBF and then created national character conversion function. After this problem was solved I got next problem - one function generated MaxLocksPerFile limit error (again - it has worked fine on Access'97, but after converted to Access'2K/2K3 - error). This problem I've solved by adding BeginTrans / CommitTrans after every 1000 updated records.

But still - it would be very usefull to understand how could I solve my problem using ODBC.
Sure thing.  Straight from the Access VBA help file.  This example is for SQL Server, but you can change the connection string to work for FoxPro/dBase.  The other link I posted originally will help with that.

-----------------------------------------------------------
    Dim cnn1 As ADODB.Connection

    ' Open a connection without using a Data Source Name (DSN).
    Set cnn1 = New ADODB.Connection
    cnn1.ConnectionString = "driver={SQL Server};" & _
        "server=srv;uid=sa;pwd=pwd;database=Pubs"
    cnn1.ConnectionTimeout = 30
    cnn1.Open
-----------------------------------------------------------
Hi Routinet,
your module gives me next compile error: "user-defined type not defined" (for Dim cnn1 As ADODB.Connection)
Does this mean that I need to add some new references? Then it means that I will need to do this on every PC...

BTW, Access'2003 has different national charaxcter conversion also for original linked DOS dBase DBF files... :(
This means that I need to prepare special conversion tools also for all other linked DBF tables (not only for FoxPro DBF).... So I have just moved back to MS Access'97 and run required calculations on old database. But some day I will need to go for new Access version...


Do you think it is worth to discuss this question further or should I close my question?
ASKER CERTIFIED SOLUTION
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, Routinet.