Re: Create DSN-Less link to Foxpro table

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

Douglas,

Thanks for the response. I tried modifying my connection string as you
suggested 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

"Douglas J. Steele" wrote:

While I haven't used Foxpro, every reference I've seen to DSN-less
connection strings to it have had { } around the driver name:

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EAB9A947-5003-45F5-AE0E-FB941284F3C6@xxxxxxxxxxxxxxxx
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 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 using 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.

I can't believe I'm the only one to try this but I can't find anything.
Can
anyone offer a solution?

Thanks,

Kevin



.

Relevant Pages

  • 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)
  • 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: 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 in Access
    ... Dim wk As DAO.Workspace ... Dim ConnectString As String ... 'Following code is required because no primary key is defined on personne. ...
    (microsoft.public.fox.vfp.dbc)