Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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=Machi ne;"

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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;Exclus ive=No"

  3. #3
    Lounger
    Join Date
    May 2004
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    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

    Any ideas?

    Thanks,

    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •