locked
OleDB / DBase3 problem RRS feed

  • Question

  • Im working on a project that require to load DBase files into the application.

    During the development, everything went fine. But when testing this on the customer pc they have an error that the files cannon be read.
    After long searching we have found out that this is working localy cause we have borland and so BDE installed.

    Now im trying to find a way to get the dbase files loaded without having BDE installed on the PC.

    This is the connection string im using:
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=dBase III

    I have read in one of the ms support articles, without BDE u can use dbase files in read only mode.
    How does this work? When i set "Mode=1" i still get the same error.

    Monday, January 18, 2010 4:03 PM

Answers

  • This is the error i get:

    The Microsoft Jet database engine could not find the object 'DATA_TABLE'.  Make sure the object exists and that you spell its name and the path name correctly.
    I tried the connection string u posted, but it makes no difference

    This particular message usually means that the table name is too long. The dBase table name, for this particular version, is limited to eight characters (not including the file extension).

    Also, if you have the latest version of the Jet Database Engine the BDE is not required to perform updates, however; the tables do require a primary key (unique index).

    EDIT: One other thing, the Data Source in the connection string should refer to the folder where the database is located, exclusive of the file name.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Jeff Shan Monday, January 25, 2010 1:51 AM
    Tuesday, January 19, 2010 3:02 PM
  • OK, well DATA_TABLE is ten characters in length. The Jet OLEDB/dBase ISAM driver only looks at the first eight, so it won't find the file.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Nightmare_BE Tuesday, January 26, 2010 7:27 PM
    Wednesday, January 20, 2010 1:38 PM

All replies

  • Dear Nightmare (wow.. that sounds freaky),

    What is the error message exactly?

    Have you tried this:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefile.dbf;Mode=ReadWrite;Extended Properties=dBase III;Persist Security Info=False


    Cheers,

    John
    Monday, January 18, 2010 8:39 PM
  • This is the error i get:

    The Microsoft Jet database engine could not find the object 'DATA_TABLE'.  Make sure the object exists and that you spell its name and the path name correctly.
    I tried the connection string u posted, but it makes no difference
    Tuesday, January 19, 2010 7:56 AM
  • I assume you've changed the path to the dbf file in the connection string :0). 

    I've read some articles, and it indeed seems a problem with dBaseIII. There is an ODBC driver available in windows, which in part your access over Microsoft's OLEDB driver for ODBC. Perhaps that is something you can try as well:

    Read this article, it is a different problem, but the solution might work for you as well.

    Cheers,

    John


    Tuesday, January 19, 2010 10:09 AM
  • I have tried using both ODBC and OleDB connections.

    For some reasson the ODBC just fails localy while the OleDB is working.
    I just can't use the OleDB on the customer pc atm cause they don't have BDE installed.
    Tuesday, January 19, 2010 10:14 AM
  • The author of this page states on another page that these should work for Dbase II and Dbase III

    http://www.connectionstrings.com/visual-foxpro

    If you don't succeed here, you can try a foxpro forum.
    In those is a huge knowledge of Dbase II to Foxpro


    Success
    Cor
    Tuesday, January 19, 2010 10:19 AM
  • In most cases the FoxPro drivers work fine for using Dbf files.

    But the problem with these files is that they contain DBT files (memo files)
    Its not possible to read the memo fields that are stored in the DBT files without using a dbase 3 or above provider
    Tuesday, January 19, 2010 10:21 AM
  • This is the error i get:

    The Microsoft Jet database engine could not find the object 'DATA_TABLE'.  Make sure the object exists and that you spell its name and the path name correctly.
    I tried the connection string u posted, but it makes no difference

    This particular message usually means that the table name is too long. The dBase table name, for this particular version, is limited to eight characters (not including the file extension).

    Also, if you have the latest version of the Jet Database Engine the BDE is not required to perform updates, however; the tables do require a primary key (unique index).

    EDIT: One other thing, the Data Source in the connection string should refer to the folder where the database is located, exclusive of the file name.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Jeff Shan Monday, January 25, 2010 1:51 AM
    Tuesday, January 19, 2010 3:02 PM
  • The application is running on Windows XP - SP3 at the customer.

    We have checked the MDAC and are running the latest version (doesnt even allow running the installer).
    We have also done the test with renaming the files and using 8.3 file formatting (folders + file) (made it like C:\Data\Test.dbf)

    I have been testing all day on a virtual machine with a fresh installed windows 7. There we have the same error like our customer.
    OleDB connection fails, ODBC connections fails ...

    When i was comparing the ODBC drivers with my machine i found one driver thats missing on the virtual machine:
    Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)  version: 12.00.6423.1000

    I dont have office 2007 installed on the virtual machine (no free license), so that probably why its missing.
    The customer is using Office 2003, so i don't know if that has something to do with it.

    I did test it on another PC in the office that has office 2003 running. There is works, but thats probably cause it has BDE installed.

    Tuesday, January 19, 2010 3:23 PM
  • Just a couple of things, Jet isn't part of MDAC; they should have the latest version of Jet installed:

    How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine

    How to use dBASE data with Access and Jet

    If your app works on another machine (with the same database) that would rule out any code or database issues. Also, I'm assuming that the database is not on the network.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, January 19, 2010 4:41 PM
  • I read the articles for both links several times now.

    For the MS Jet 4.0 page, if we download the hotfix for Windows XP it also say the latest version is installed

    The database is not located on a network share, just on the local HD.


    ---------------------------
    KB829558 Setup Error
    ---------------------------
    Setup has detected that the Service Pack version of this system
    is newer than the update you are applying.

    There is no need to install this update.

    Wednesday, January 20, 2010 8:54 AM
  • Could you post your code, including the Data Source information, and indicate what the exact error is?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 20, 2010 12:55 PM
  • This is the code the the DBF reader class i made.

    I have tried adding a backslash at end of datasource, i have tried using filename with extention on the select, i have tried change different properties of the connection string ... none seem to have much effect. They work on pc's with BDE, fail on pc without BDE
    Imports System.IO
    Imports System.Text
    Imports System.Data
    Imports System.Data.OleDb
    
    Public Class InputFileDBF
        Implements IDisposable
    
        Private m_connection As OleDbConnection = Nothing
        Private m_dataTable As DataTable = Nothing
        Private m_disposed As Boolean = False
        Private m_filename As String = ""
    
        Public Sub New(ByVal filename As String)
            m_filename = filename
            m_connection = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=Read;Extended Properties=dBase III;Persist Security Info=False;User ID=admin;Password=", Path.GetDirectoryName(filename)))
            m_connection.Open()
        End Sub
    
        Public Sub Read()
            Dim command As OleDbCommand = Nothing
            
            Try
                ' Initialize the datatable
                If (m_dataTable Is Nothing) Then
                    m_dataTable = New DataTable(Path.GetFileNameWithoutExtension(m_filename))
                Else
                    m_dataTable.Clear()
                    m_dataTable.AcceptChanges()
                End If
    
                ' Create the command
                command = m_connection.CreateCommand()
                command.CommandText = String.Format("SELECT * FROM {0}", Path.GetFileNameWithoutExtension(m_filename))
                command.CommandType = CommandType.Text
                command.CommandTimeout = 0
    
                ' Load the data
                '>>> ERROR: The Microsoft Jet database engine could not find the object 'DATA_TABLE'.  Make sure the object exists and that you spell its name and the path name correctly.
                m_dataTable.Load(command.ExecuteReader(), LoadOption.OverwriteChanges)
            Catch ex As Exception
                Throw ex
            Finally
                If Not (command Is Nothing) Then
                    command.Dispose()
                End If
            End Try
        End Sub
    
        Public ReadOnly Property Data() As DataTable
            Get
                Return m_dataTable
            End Get
        End Property
    
        Public ReadOnly Property Filename() As String
            Get
                Return m_filename
            End Get
        End Property
    
        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not Me.m_disposed Then
                If disposing Then
                    If Not (m_connection Is Nothing) Then
                        m_connection.Close()
                        m_connection.Dispose()
                    End If
                    m_connection = Nothing
                End If
            End If
            Me.m_disposed = True
        End Sub
    
    #Region " IDisposable Support "
    
        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
    
    #End Region
    
    End Class
    Wednesday, January 20, 2010 1:02 PM
  • OK, well DATA_TABLE is ten characters in length. The Jet OLEDB/dBase ISAM driver only looks at the first eight, so it won't find the file.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Nightmare_BE Tuesday, January 26, 2010 7:27 PM
    Wednesday, January 20, 2010 1:38 PM
  • Paul,.... Dude! I know that what you're saying is true... but I would have never thought of it... hope that solves it!
    cheers,
    John

    Wednesday, January 20, 2010 1:57 PM
  • DATA_TABLE is not the real table name. I just renamed the real table name to hide company/customer info that is used in the datafile.
    The actual file names are longer, like: PROD_<customer>_<type>_<company>H_C0_<date>.DBF

    We have asked our customer to rename theyre files to have the 8.3 file formatting and they said this does not fix the issue.


    To have it verified i did a test on one of the virtual machines where i can reproduce the error.
    On the XP virtual machine where i also have the same error, i made a new folder C:\Data and renamed the the header file to H.DBF, detail file to D.DBF

    This is working fine on the virtual machine. Currently im getting some other errors, but those are related to info needed from the file names. (such as the customer/company name, ...)

    Should be easy to fix by temporary rename the files before reading them.

    Thanks for the help :)
    Wednesday, January 20, 2010 2:12 PM
  • Yes, I would definitely put a check in your code for the file length and temporarily rename the file if necessary. If the customer is still having a problem then it's probably related to something else and I doubt that the error is the same.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 20, 2010 2:28 PM
  • 2019 and this problem still exists.

    Well,  i wouldn't take risk nor use destructive way to work around with file renaming  (MS always drives you to "workaround" but's this one seems hard...).

    In 2019 I use to work with the Kernel API function GetShortPathNameW to get the name "like MS wants it to be"...

    GetShortPathNameW  gives you the 8.3 name of a file

    For this to work you must call GetShortPathNameW  on the full path of the DBF file (path and file, and it must exist), and use the short path with the folder removed (so, just the file name in 8.3)  when you pass the dbf file name as the table name.

    GetShortPathNameW  :

    https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-getshortpathnamew
    Tuesday, July 2, 2019 10:09 PM