Alternatives to M$ Jet Driver

• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago
  Hey guys,
  
  One of the modules to a program I wrote awhile back has become popular at the plant I work at and so has jumped from around 10 people to 150 using the code.  The downside is now I'm becoming more aware of bugs on code I thought I was done with as different people run the code on different machines.  The biggest issue I have now is the stability of the Microsoft Jet driver to connect to a flat DBF IV database.  It will work fine 99% of the time, then randomly fails on the .Open command.  I can find no errors with the DBF itself, and when I comment out the section that uses the Jet driver the code proceeds fine.
  
  So, what alternatives do you all suggest?  I'm looking into the FoxPro driver right now as a possible fallback if the Jet driver fails.  The driver needs to be capable of a few basic functions (so Microsoft dBASE Driver (*.dbf) is too primitive) like checking total records, existence of fields, field lengths, modifying field values, etc.  Example of the code that works most of the time is below.  Compiled code will say "Run-time error, Syntax error in FROM clause" and the uncompiled code will fail on the bolded line.  I added code to make an entry to a log file on these errors with the file it failed on and the SQL query it tried to use.  I could find no errors in these log files and was able to manually make connections with the same syntax.  I do believe it to be completely a bug in the Jet driver itself.
  
  
  
  Quick side note, I do have as much error checking code I can think of to verify the file is there, not a 0 byte ghost file, has at least one record, and is not locked by another process before running this chunck of code.
  
      Dim conRecordset As ADODB.Connection
      Dim rsRecordset As ADODB.Recordset
      Dim strSQL As String
         
      Set conRecordset = New ADODB.Connection
      Set rsRecordset = New ADODB.Recordset
     
      strSQL = "SELECT * FROM " & FileName
      conRecordset.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & JobListArray(z).DataPath & ";Extended Properties=DBase IV"
  
      rsRecordset.CursorLocation = adUseClient
      rsRecordset.Open strSQL, conRecordset
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse