Problems with Foxpro dsnless linked tables (1 Viewer)

[daniel.nicolae.stoica]

Hello!
I have a problem when i try to connect two different Foxpro table which are located in different folders. The first link goes fine but when i try to link the second table although in the ConnectionString I specify the path correctly (witch is different from the first path) i get an error. Apparently the ConnectionString in the moment when is appended to TableDef becomes the path of the first table (where the second table it is not).
Bellow is the code witch i used for dsnless link the table. I am pretty sure thet is correct (because the first table apear correct linked and opes in database window).

Set db = CurrentDb
Set tdf = db.CreateTableDef([table_name])
tdf.Connect = "ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=[path]" & _ ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
tdf.SourceTableName = [source_table_name]
db.TableDefs.Append tdf

What is wrong? What i can do to correct the error and to link multiple tables from diffrent locations?
Any help will be appreciated.
Thanks a lot in advance for your help.!

 

[arnelgp]

did you try to change the Path also on the connection string?

 

[daniel.nicolae.stoica]

Of course.

Set db = CurrentDb
Set tdf = db.CreateTableDef([second_table_name])
tdf.Connect = "ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=[different_path]" & _ ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
tdf.SourceTableName = [second_source_table_name]
db.TableDefs.Append tdf

When i debug the tdf.connect appear correct but after the error when i test tdf.connect has the connection string of the table allready linked. I don't get it...

 

[arnelgp]

sorry if not helpful, i don't have visual foxpro on my machine.
can you just create the connection manually using odbc, then when finished, copy the content of the .dsn
and just concatenate the text in there.

 

[daniel.nicolae.stoica]

I don't think is a problem with the code with witch i make the link: first linked table is OK. I don't understand why when i try to link the second table (and
when i specifically set the correct string to tdf.connect) takes the connection string from the first table...

 

[arnelgp]

have you ever tried to link the two different tables (on different folders) Manually and see what Connection string it produced?

 

[daniel.nicolae.stoica]

You mean with different DSN?

 

[arnelgp]

you can create different dsn for each table you wish to link and see the resulting .dsn text file created.

 

[Gasman]

I think I have VFP on my laptop, I can try when I get back.
Have you tried putting the second table into the first folder?
I will be relying on the code you supplied to do this.

 

[daniel.nicolae.stoica]

I think I have VFP on my laptop, I can try when I get back.
Have you tried putting the second table into the first folder?
I will be relying on the code you supplied to do this.

In this case works normal. One solution is to put all my tables in one folder (witch i rather not, there are other people who are familiar with the actual structure).
I still hope that someone brighter and with better skills then me will save me...

 

[daniel.nicolae.stoica]

you can create different dsn for each table you wish to link and see the resulting .dsn text file created.

With different user DSN I managed to link different tables... Well, a long time ago in a app that I wrote I create with vba a DSN. I don't remember if i had to run Access as Administrator...

 

[Gasman]

Please show me the values that you are passing for each variable?
I get this

ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=[path];SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

from

Sub AddFPTable(strTable As String, strPath As String, strFTPTable As String)
Dim db As Database
Dim path As String
Dim tdf As DAO.TableDef

Set db = CurrentDb
path = strPath


Set tdf = db.CreateTableDef(strTable)
tdf.Connect = "ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=[path]" & _
    ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
tdf.SourceTableName = strFTPTable
db.TableDefs.Append tdf
End Sub

? strpath
C:\Program Files (x86)\Microsoft Visual FoxPro 7\Wizards\Template\Address Book\Data\

Concatenating the path into the connect string does not work either?

ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB="C:\Program Files (x86)\Microsoft Visual FoxPro 7\Wizards\Template\Address Book\Data\";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

 

[Gasman]

I was trying to find the OBDC driver and I found this?
I only have VFP7

• Visual FoxPro ODBC Driver
  The VFPODBC driver is no longer supported. We strongly recommend using the Visual FoxPro OLE DB provider as a replacement. Please refer to the following article for more information and related links to issues when using the VFPODBC driver: https://support.microsoft.com/kb/277772.

 

[daniel.nicolae.stoica]

ODBC drivers.

 

[daniel.nicolae.stoica]

OLEDB drivers.

 

[Gasman]

Thanks, I downloaded the OLEDB drivers and was able to link to a foxpro dbf file just as I would an Access table.
I will try the OBDC driver and see what happens.

As I have never done this before, you say the code you supplied works as is?, even with [path] in the string as just that?
I would have thought that you would need to concatenate the value of path?

Edit: I am getting failed all the time.

Can you please supply valid values for the code?
ATM I am concatenating the path into the connection string?

addfptable "annuity","D:\PAUL\Documents\Annuity.dbf","Annuity.dbf"
? tdf.Connect
ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB="D:\PAUL\Documents\Annuity.dbf";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

Sub AddFPTable(strTable As String, strPath As String, strFPTable As String)
Dim db As Database
Dim path As String
Dim tdf As DAO.TableDef

Set db = CurrentDb
path = strPath


Set tdf = db.CreateTableDef(strTable)
tdf.Connect = "ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=" & Chr(34) & path & Chr(34) & _
    ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
tdf.SourceTableName = strFPTable
db.TableDefs.Append tdf
End Sub

My dbf file is D:\PAUL\Documents\Annuity.dbf
We will call is Annuity in Access

Once that works, I will try from a different folder.

 

[daniel.nicolae.stoica]

Thanks, I downloaded the OLEDB drivers and was able to link to a foxpro dbf file just as I would an Access table.
I will try the OBDC driver and see what happens.

As I have never done this before, you say the code you supplied works as is?, even with [path] in the string as just that?
I would have thought that you would need to concatenate the value of path?

Of course I concatenate; is an AfterUpdate event triggered by a combobox. The connect looks like
db.TableDefs(rst!TableName).Connect = "ODBC;DRIVER={Microsoft FoxPro VFP Driver (*.dbf)};SourceDB=" & _
rst!drive & "\transmisii\" & Forms!frmah!cboDateReferinta.Column(2) & "\" & FormatareLuna(Forms!frmah!cboDateReferinta.Column(1)) & _
"\AH\" & ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
But I need to link different Foxpro tables from different paths; then the problem arises...
Strange is that I'm not experience the same problem with (many) Microsoft SQL tables...
I found a thred who makes me wonder if Access doesn't cache somehow the connection string.
Anyway I start to build a routine who will create a different DSN for every table. If anyone find a different solution the help will be appreciated!

 

[Gasman]

Ok, whilst I was waiting for you to come back, I tried this
Link to a dbf file via OBDC and that came in fine.
Link to another file in another folder, and that appeared to come in fine. However when I went to browse it, it said it could not find the table, despite the path it shows is actually there?
If I move the dbf file to same place as the other file, it works. So I believe your assumptions are correct.

Thanks for the OBDC driver BTW.

 

[daniel.nicolae.stoica]

I am starting to think that dsnless linking of foxpro tables from different folders is not possible and that the only solution is to create DSN for each path. Of course Access will have to be run as an administrator. As soon as I finish writing and checking the code for creating the DSN and linking the tables I'll post it here in the unlikely event that someone using Access and foxpro needs it..

 

[Gasman]

Well I would be interested.
Foxpro is far faster cataloging all my music than Access, and if I could link to those two dbf files, I could create reports in Access.

I am having trouble just linking atm. I successfully linked test dbs files. Now I want the real thing, I keep getting only offered music_app.dbf from this list

I select mp3 and then get

No idea as to what I have done to change it. :-(

Edit: Found out, I had to delink them from the project I created yesterday.