Connect String, Visual FoxPro

[JaedenRuiner]

I'm having an issue with a foxpro dbase III database, that I'm trying to connect to at runtime. My Connect string is simply:
(ConnectStr)
"ODBC;DSN=Visual FoxPro Database;DATABASE=D:\DBFiles"

The file name is table001.dbf and the subsequent table name within the dbf file is table001.

I then do:

  dim dbTmp as Database
  dim tdfLink as TableDef
  Set dbTmp = CurrentDB
  
  set tdfLink = dbTmp.CreateTableDef("tbl_DB")
  tdfLink.Connect = ConnectStr
  tdfLink.SourceTableName = "table001"
  dbTmp.TableDefs.Append(tdfLink)

upon appending the tdflink I get the Visual FoxPro DSN dialog that asks for a path to the table. I though I had already set that via the DATABASE= parameter of the connect string. I need to do this all real time, with no user input, and it uses a foxpro data source driver. How do I get this to work? I only want to load a table reference into the access mdb so that I can perform SQL commands upon that table, however the table is found in an external FoxPro dbIII dbf file.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[Robert88]

Hi

Don't know if this link will help http://www.access-programmers.co.uk/forums/showthread.php?t=102185

But good microsoft reference on post 5 of the above link to another issue you might of over looked? http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

Has the ODBC in control panel been set-up? might want to check this site, although for Oracle still show you how to set-up the ODBC. http://www.databasejournal.com/features/oracle/article.php/10893_3358411_1

Robert88

 

[supmktg]

Jaeden,

I use the following two subs to establish a DSN-less connection to a foxpro table:

Sub FoxProLink(dbase_path As String, Optional oconn As Connection, Optional rst As Recordset, Optional file_name As String)
'connect to a foxpro table based on the dbase_path information
 Dim file_path As String, file_ext As String
 ExtractDbaseInfo dbase_path, file_path, file_name, file_ext
 Set oconn = New Connection
 oconn.Attributes = 3
 oconn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
            "SourceType=DBF;" & _
            "SourceDB=" & file_path & ";" & _
            "Exclusive=No;"
End Sub

Sub ExtractDbaseInfo(source As String, Optional FilePath As String, Optional fileName As String, Optional FileExt As String)

    Dim intCurrentPos As Integer, intFileNamePos As Integer
    Dim intFileExtPos As Integer
    
    'clear UDT passed in if there is any existing data
    fileName = ""
    FileExt = ""
    FilePath = ""

    If source <> "" Then
        'get file name by finding the last slash in the string
        Do
            intCurrentPos = InStr(intCurrentPos + 1, source, "\", vbTextCompare)
            If intCurrentPos <> 0 Then
                intFileNamePos = intCurrentPos
            Else
                intFileNamePos = intFileNamePos + 1
                Exit Do
            End If
        Loop
        'get file extension position if any
        intFileExtPos = InStr(intFileNamePos, source, ".", vbTextCompare) + 1
        If intFileExtPos = 1 Then
            Exit Sub
        End If
        'put data in UDT passed in
        If intFileNamePos <> 1 Then
            fileName = Mid(source, intFileNamePos, Len(source) - intFileNamePos - 3)
            FileExt = Mid(source, intFileExtPos, 3)
            FilePath = Mid(source, 1, intFileNamePos - 2)
        End If

    End If

End Sub

FoxProLink("D:\DBFiles\table001.dbf") should connect you to the table.

HTH,

Sup