Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Linking multiple dbf's from different locations to one access db (2007)

    Hi,

    I created the following code which loops trough a file called "TeImporterenBestanden" (containing the different locations/directories of the different dbf's (including the different dbf file names)
    When I used this code the first dbf get's linked (through ODBC) to my Access db. But the next dbf which exist on a different location doensn't!

    I got a message "the microsoft Office Access database engine could not find the object "XXXX" (=dbf name). Make sure the object exist and that you spelt its name and the path correctly"

    I checked and double checked but the path and names are correct. So I wonder whats wrong with the following code. I think the access db holds the first ODBC DSN link not matter what DSN comes next. In the code below it go's wrong when using the "CurrentDatabase.TableDefs.Append MyTableDef" for the second time.

    Please help!


    Sub MaakOutputVIP(StatusBar As Control)

    Dim CurrentDatabase As Database

    Dim MyTableDef As TableDef

    Dim strODBC As String 'Variant

    Dim constr, OutputName As Variant

    Dim rs As Recordset

    Dim strPath As String

    Dim strFileName, strTableName As String

    Dim sSql, nameT As String

    Dim regel As Integer

    Dim firsttime As Boolean

    Dim filename As String

    Set CurrentDatabase = currentdb

    SchrijfMessage "Bezig met het inlezen van de dbf file(s)", StatusBar

    On Error GoTo error_handler

    'Geef het bestand op waar de in te voeren produktresultaten in staan

    nameT = "TeImporterenBestanden"

    sSql = "Select * From " & nameT & " Where Importeren = True"


    Set rs = CurrentDatabase.OpenRecordset(sSql, dbOpenDynaset)
    regel = 0

    Do While Not rs.EOF

    regel = regel + 1
    strPath = rs!BestandsNaam

    OutputName = rs!Outputnaam

    strFileName = Left([strPath], InStrRev([strPath], "\") - 1)

    filename = GetFileName(rs!BestandsNaam)

    SchrijfMessage "Bezig met het importeren van " &
    rs!BestandsNaam, StatusBar

    On Error GoTo error_handler



    constr = "DSN=Visual FoxPro Tables"

    constr = constr & ";SourceDB=" & strFileName & ""

    constr = constr & ";SourceType=DBF"

    constr = constr & ";Exclusive=No"

    constr = constr & ";BackgroundFetch = Yes" 'No

    constr = constr & ";Collate=Machine" 'Machine

    constr = constr & ";Null=Yes"

    constr = constr & ";Deleted=Yes"

    constr = constr & ";Mode=Read;"

    strODBC = "ODBC;" & constr



    If fExistTable(rs!Outputnaam) = False Then


    Set MyTableDef = CurrentDatabase.CreateTableDef

    MyTableDef.name = rs!Outputnaam

    MyTableDef.Connect = strODBC

    MyTableDef.SourceTableName = filename

    CurrentDatabase.TableDefs.Append MyTableDef

    CurrentDatabase.TableDefs.Refresh

    MyTableDef.RefreshLink


    Else
    Set MyTableDef = CurrentDatabase.TableDefs(rs!Outputnaam)
    MyTableDef.Connect = strODBC
    MyTableDef.RefreshLink
    End If
    CurrentDatabase.TableDefs.Refresh
    Set MyTableDef = Nothing


    rs.MoveNext

    Loop

    rs.Close

    Set rs = Nothing

    Set CurrentDatabase = Nothing

    SchrijfMessage "inlezen gereed", StatusBar
    Exit Sub

    error_handler:

    If err.Number <> 3440 Then 'niet 3340, want dan is het bestand leeg

    MsgBox err.Description & " Fout bij inlezen van recordnummer "
    & regel & ""

    End If

    SchrijfMessage "inlezen mislukt agv fout bij inlezen van
    recordnummer " & regel & "", StatusBar

    Exit Sub
    Resume Next
    RefreshDatabaseWindow
    Set CurrentDatabase = Nothing
    End Sub
    Sub SchrijfMessage(sMessage As String, ctlStatusBar As Control)
    ctlStatusBar.Caption = sMessage
    End Sub


    Public Function fExistTable(strTableName As String) As Boolean
    Dim db As Database
    Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    fExistTable = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
    If strTableName = db.TableDefs(i).name Then
    'Table Exists
    fExistTable = True
    Exit For
    End If
    Next i
    Set db = Nothing
    End Function




    Public Function GetFileName(flname As String) As String
    'Get the filename without the path or extension.
    'Input Values:
    ' flname - path and filename of file.
    'Return Value:
    ' GetFileName - name of file without the extension.
    Dim posn As Integer, i As Integer
    Dim fName As String
    posn = 0
    'find the position of the last "\" character in filename
    For i = 1 To Len(flname)
    If (Mid(flname, i, 1) = "\") Then posn = i
    Next i
    'get filename without path
    fName = Right(flname, Len(flname) - posn)

    'get filename without extension
    posn = InStr(fName, ".")
    If posn <> 0 Then
    fName = Left(fName, posn - 1)
    End If
    GetFileName = fName
    End Function

  2. #2
    Join Date
    Nov 2011
    Posts
    4
    Any luck someone can help me with this problem?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just a wild guess here. I saw a similar, though not identical, problem once. I never could find a satisfying explanation, but the problem was solved by re-assigning the database object inside the loop:
    Code:
        Set rs = CurrentDb.OpenRecordset(sSql, dbOpenDynaset)
        regel = 0
        Do While Not rs.EOF
            Set CurrentDatabase = CurrentDb
            regel = regel + 1
            . . .
    Not related to what you describe, I can see two sources of potential problems:

    1. When you declare variables like:
    Code:
    Dim strFileName, strTableName As String
    strTableName is a string but strFileName is a variant.


    2. You do not exit the error handler correctly.
    - There should be a:
    Code:
    Resume Label
    to quit it, not an Exit Sub or End Sub.

    -Also, you should never use:
    Code:
    On Error Resume Next
    inside an error handler,

    - Finally you should clear the Err object (Err.Clear) before exiting the function.
    Have a nice day!

  4. #4
    Join Date
    Nov 2011
    Posts
    4
    Hi

    Thanks for your replied. But unfortunately your advice didn't work out. Any other ideas? Anyone else maybe?

  5. #5
    Join Date
    Nov 2011
    Posts
    4
    Hi,

    I discovered that 1) when I try to link the manually (to the different dbf files on different location) using the linked table manager only one is accessible. 2) When I put the different dbf's in one directory they are alle accessible. (but this takes a lot of time (copy/paste) and needs a lot of extra space)

    Conclusion: It seems like it is impossible to link within one database to multiple dbf files on multiple/different locations.

    I hope this insn't true......

    Please help!

Posting Permissions

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