Re: Create DSN-Less link to Foxpro table in Access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Fred/Craig,

Thanks for the response. As for the OLEDB provider, I'm not sure what I
need to do differently to use it. However, I do have it installed (ver. 9)
and the help file makes it sound like it should be able to use ODBC.

The Visual FoxPro OLE DB Provider includes the following:
*Support for most Open Database Connectivity (ODBC) driver functionality
from earlier versions of FoxPro

I tried modifying my connection string as suggested on another forum but
still no luck. I also tried using the resolution shown here
http://support.microsoft.com/kb/285345/en-us ; but I don't know if I'm going
about it the right way. I don't want to open the Foxpro database, just link
to one of it's tables.

Here is the modified code.

Note that it fails on the line where I try to append to TableDefs -
db.TableDefs.Append td 'Fails here


Public Sub DSNLessFP()

Dim wk As DAO.Workspace
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ConnectString As String

' Create an ODBCDirect workspace. Until you create
' Microsoft Jet workspace, the Microsoft Jet database
' engine will not be loaded into memory.
Set wk = DBEngine.CreateWorkspace("ODBCWorkspace", "admin", _
"", dbUseODBC)

Set db = CurrentDb

ConnectString = "ODBC;Driver={Microsoft Visual Foxpro
Driver};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 'Fails here

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"

db.Execute strSQL, dbFailOnError

db.Close
wk.Close

End Sub

Kevin


"Craig Berntson" wrote:

One problem you may be having is that DAO uses ODBC. The VFP ODBC driver has
not been updated since VFP 6.0 (VFP 9.0 is current) and does not support
features added after that. However the OLEDB Provider is current. You may
want to try that instead.

--
----
Craig Berntson
MCSD, Visual FoxPro MVP
Salt Lake City Fox User Group
"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C2E3D816-AE47-4C89-9E19-DC4F8288C4FB@xxxxxxxxxxxxxxxx
I posted this in the access.exteranldata forum but thought I would put it
here as well hoping that Foxpro users might have the answer. 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 FPadmin 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("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

I have tried changing up the connection string to something like the
following.

ConnectString = "ODBC;Driver=Microsoft Visual FoxPro
Driver;SourceType=DBC;SourceDB=E:\admin.dbc;Exclusive=No"

But then I get an error -
Run-time error '3000';
Reserved error (-7778); there is no message for this error

That took me to this article http://support.microsoft.com/kb/285345/en-us

But that shows opening the database. I want to create a link to the
table.

Can
anyone offer a solution? I started out using DAO but if this can be done
some other way, through ADO for example, that's fine.

Thanks,

Kevin



.

Relevant Pages

  • Re: Create DSN-Less link to Foxpro table
    ... I'm afraid I have absolutely no experience using FoxPro, ... Dim wk As DAO.Workspace ... Dim ConnectString As String ...
    (microsoft.public.access.externaldata)
  • Re: Create DSN-Less link to Foxpro table
    ... Dim wk As DAO.Workspace ... Dim ConnectString As String ... 'Following code is required because no primary key is defined on personne. ...
    (microsoft.public.access.externaldata)
  • Re: multi thread vb app to pull data from webservice
    ... Public Function getXMLCallUp(ByVal userID As String) As ... Dim xmlDoc As New XmlDocument ... connectString As String) As DataTable ... Dim conn As New SqlConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: multi thread vb app to pull data from webservice
    ... Public Function getXMLCallUp(ByVal userID As String) As ... Dim xmlDoc As New XmlDocument ... connectString As String) As DataTable ... Dim conn As New SqlConnection ...
    (microsoft.public.dotnet.languages.vb)
  • multi thread vb app to pull data from webservice
    ... Public Function getXMLCallUp(ByVal userID As String) As ... Dim xmlDoc As New XmlDocument ... connectString As String) As DataTable ... Dim conn As New SqlConnection ...
    (microsoft.public.dotnet.languages.vb)