MS Access and DBF files with a long filename (Run-time error 3011: The Microsoft Jet database engine could not find the object)

Couple days ago I was asked by my friend to help with automating one of his office tasks. The task was very simple – every weekend their old database system produces number of DBF files in DBASE IV format and also there is a main registry file in MS Excel format. Registry file holds all information about customers, including, let me say, some kind of region id and region name fields. Each DBF table includes only region id and other details. The task was to generate appropriate Excel file for each DBF table, adding extra region name by reconciling region id.

I feel that MS Access will be great for coding such simply converting utility. Last projects I worked with were a complex Java EE and Oracle related things. It was a long time ago when I last touched MS Access, so it was a good chance to play with old plain database tool.

Indeed, in a short time I was able to create table structures, defined index fields to speed up joins, designed simple dialog form and bang out VBA code to import XLS registry file and DBF tables, created reconciling SQL query and finally could export query result to Excel file. Everything worked well, but, sometimes Jet Engine cried out with an exception saying that there is some object could not be found.

Run-time error 3011 : The Microsoft Jet database engine could not find the object. Make sure the object exists and that you spell its name and path name correctly.

Strange, how this can be happen? How it can not find table what definitely exists here?

After some researches I understood that problem was with DBF filenames. It seems that Jet Engine thinks DBF filename should be strictly in old DOS format – no more than 8 characters in filename. Otherwise it throws exception. Hah, sometimes our source tables had long filenames and this was the issue. The first idea was to say my friend to rename DBF files manually, the second idea was to create some script for automate renaming process. But I drop both because each generated Excel filename should remain as source DBF, but with extension XLS.

On the web I found several ways for converting long filenames to short ones. May be, it is good idea to convert long filenames using Windows API or parsing dir /x command output. But, for this simple utility I used more simple way (simple, but not the best of course).

A simple workaround for achieving this was to look in source folder for DBF files, store found filename in a variable, then check filename length, and for long filenames to copy DBF file with some other temporary short name, and then import file into database and after all delete it if it was a temporary file.

At the end this worked well and everybody was happy :))

As there is no secret here with this utility, final source VBA code looks like as below:

Private Function openFolderPicker()
    Dim dlgFolder As Office.FileDialog
    Dim folderPath As String
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With dlgFolder
        .AllowMultiSelect = False
        .Title = "Select folder"
    End With
    
    If (dlgFolder.Show = -1 And dlgFolder.SelectedItems.Count > 0) Then
        folderPath = dlgFolder.SelectedItems(1)
    End If
        
    Set dlgFolder = Nothing
    openFolderPicker = folderPath

End Function

Private Sub btnFolderIn_Click()
        Dim folderPath As String
        folderPath = openFolderPicker()
        txtIn = folderPath
End Sub

Private Sub btnFolderOut_Click()
        Dim folderPath As String
        folderPath = openFolderPicker()
        txtOut = folderPath
End Sub

Private Sub btnUpdateRegistry_Click()
       
    '--- warnings off
    DoCmd.SetWarnings False
    
    Dim dlgOpen As Office.FileDialog
    Dim fileRegistryPath As String
    
    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = False
        .Title = "Choose registry file"
        .Filters.Clear
        .Filters.Add "Excel", "*.XLS"
    End With
    
    If (dlgOpen.Show = -1 And dlgOpen.SelectedItems.Count > 0) Then
        fileRegistryPath = dlgOpen.SelectedItems(1)
        DoCmd.Hourglass True
    
        Dim SQL As String
        SQL = "DELETE FROM tblRegistry"
        DoCmd.RunSQL SQL
    
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
        "tblRegistry", fileRegistryPath, True
        DoCmd.Hourglass False
        MsgBox "Registry updated succesfully"
    End If
    DoCmd.SetWarnings True
        
    Set dlgOpen = Nothing
    
End Sub

Private Sub btnProcess_Click()
  
    '--- warnings off
    DoCmd.SetWarnings False

    '--- set cursor to hourglass
    DoCmd.Hourglass True

    Dim fileCount As Long
    fileCount = 0
    Dim dbfPath As String
    Dim dbfFile As String
    Dim xlsPath As String
    Dim xlsFile As String
    Dim SQL As String
    Dim xlsFilePath As String
    
    dbfPath = txtIn
    xlsPath = txtOut
    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '--- remove all XLS files in the out folder
    fso.DeleteFile xlsPath + "\*.xls", True
    
    Dim fs
    Set fs = Application.FileSearch
    
    Dim fileNameId As Long
    Dim baseFileName As String
    Dim tmpFileName As String
    
    fileNameId = 0
    
    '--- look for DBF files
    With fs
        .NewSearch
        .LookIn = dbfPath
        .FileName = "*.dbf"
    End With
    If fs.Execute > 0 Then
    For i = 1 To fs.FoundFiles.Count
        dbfFile = fso.GetFileName(fs.FoundFiles(i))
        baseFileName = fso.GetBaseName(dbfFile)
        xlsFile = baseFileName + ".xls"
        tmpFileName = ""
        
	'--- work around for 8 character length limit 
        If Len(baseFileName) > 8 Then 
            dbfFile = "tmpdb" + Format(fileNameId, "000") + ".dbf"
            tmpFileName = dbfPath + "\" + dbfFile
            fso.CopyFile fs.FoundFiles(i), tmpFileName, True
            fileNameId = fileNameId + 1
        End If
        
        SQL = "DROP table tblDbfImportTmp"
        DoCmd.RunSQL SQL
        
        DoCmd.TransferDatabase acImport, "dBase IV", dbfPath, acTable, dbfFile, "tblDbfImportTmp"
        
        SQL = "DELETE FROM tblDbfImport"
        DoCmd.RunSQL SQL
        
        SQL = "INSERT INTO tblDbfImport SELECT * FROM tblDbfImportTmp"
        DoCmd.RunSQL SQL
        
        xlsFilePath = xlsPath + "\" + xlsFile
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
            "queryOuterJoin", xlsFilePath, True
        
        If tmpFileName <> "" Then
            fso.DeleteFile tmpFileName, True
        End If
        
        fileCount = fileCount + 1
    
    Next i
    End If
    
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    
    MsgBox "Files processed - " + Str(fileCount)
    
End Sub