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
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).