Link to FoxPro With DSN Less Connection (2002/SP3)

Topic

New Reply

I’m so close but I just can’t find exactly what I need. I’m trying to create a dsn-less connection to a Foxpro table (through the dbc). The following code works great only it requires the DSN to be setup. How would I modify this so that the DSN is not required on the user’s computer?

Public Sub DSNFP()

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ConnectString As String
Set db = CurrentDb

ConnectString = “ODBC;DSN=FPadmin;SourceDB=E:admin.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;”

Set td = db.CreateTableDef(“dept”)
td.Connect = ConnectString
td.SourceTableName = “dept”
db.TableDefs.Append td

Set td = db.CreateTableDef(“personne”)
td.Connect = ConnectString
td.SourceTableName = “personne”

db.TableDefs.Append td

‘Following code is required because no primary key is defined on personne.
‘Without the following code, personne is read only.
Dim strSQL As String
strSQL = “CREATE INDEX pk_emp_no ON personne(emp_no) WITH PRIMARY”

Set dbs = DBEngine(0)(0)
dbs.Execute strSQL, dbFailOnError

End Sub

Thanks in advance,

Kevin

Reply | Quote

Replies

Does this work? (I can’t test it, I have no FoxPro files):

ConnectString = “ODBC;Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=E:admin.dbc;Exclusive=No”

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

No. I get an error that says

Run-time error ‘3000’;
Reserved error (-7778); there is no message for this error

I’ve found the follow article about this but can’t figure out how to apply the resolution to my case.

http://www.kbalertz.com/kb_Q285345.aspx%5B/url%5D

Any ideas?

Thanks,

Kevin

Reply | Quote

That article is about opening a FoxPro database in DAO, not about linking a FoxPro table. I’m afraid I cannot help you – I never work with FoxPro.

Reply | Quote

I sometimes work with Visual FoxPro databases (under duress) but do not know of any way to link a VFP table in Access w/o specifying an ODBC DSN (Data Source Name). One Access app I maintain needs to connect with a VFP 6.0 database on the user’s machine. To address this issue, the app allows the user to configure the DSN and VFP connection (the user only needs to specify path to .DBC file, which is usually installed in a default location). You can create the DSN programatically by using the DAO DBEngine RegisterDatabase method, which writes the necessary settings to the Registry as a new key under HKEY_CURRENT_USERSoftwareODBCODBC.INI (if DSN with same name already exists, it is updated). Once DSN created, tables in the source database can be linked via various methods (DAO or ADOX). When linking VFP tables, the “Select Unique Record Identifier” dialog may appear, requiring user to select one or more fields that act as unique index. Only way I know to avoid this is to use ADOX objects and methods to link table, and the dialog will not appear. Only catch is, the linked table will not be updatable (some VFP tables already have a unique index defined and this will not be an issue).

The attached text file (exported code module) provides some examples of code used to create DSN and link tables programatically. Code was tested in Access 2003 but same code also worked in A2K.

Only other option to avoid DSN would be to establish a connection via ADO using the Visual FoxPro OLEDB provider (vfpoledb.dll) (currently version 9.0). See TestOleDbVFP sub in attached file. However you’d have to work with recordsets, not linked tables, if using this method. The VFP 9.0 provider can be downloaded from Microsoft, see att’d file for current URL. The drawback would be, you’d have to be sure that the user has the provider installed on their system, or the code will fail. If the end user has Visual FoxPro 7.0 or later installed, they probably already have it. Otherwise, probably not. (The provider has to registered on user’s system; the MS download file (vfpoledb.exe) accomplishes this). Note, if the provider is installed, the end user does not have to have VFP itself installed.

HTH

Reply | Quote