ACE DAO recordsets returning zero fields under vb.net

Question

I'm converting some legacy code that was developed in VBA in access and uses DAO to perform database queries, updates, etc. I need to convert some non-GUI functionality so that it can run unattended without Access running. So I've ported the VBA code to VB.net, using the Access database Engine object library (ACE) to provide the DAO interface.

It works, up to a point: After a certain (unknown) number of times creating and opening (and then closing) recordsets, it starts returning recordsets with the correct number of records, but no members in its Fields collection. So trying to get a field's value using Fields("Name").Value throws a "Item not found in this collection" exception.

It's not a problem with the particular query: In some cases the exact same query with the exact same parameters worked fine earlier in the execution of the program (with no change to the underlying data). If I rearrange the order of execution of parts of the program, then I get the same kind of error (i.e. recordsets returned with empty Fields collection) with different queries in different parts of the program.

So it looks like there's some kind of bug in the DAO library in which it fails after opening and closing a certain number of recordsets. But it only happens under .Net, not under VBA.

Has anyone come across this problem? Are there any workarounds?

Thanks!

Update: Someone asked that I post some code. As I described above, it doesn't happen at a specific point in the code - I can happen at various places depending on the execution flow. Here's an example of one of the places that it happens. It's a simple function to retrieve an option value from a table of options:

Public Function GetSolverOption(ParameterName As String) As Object
    '
    ' o Gets the value of a solver option.

    Dim dbsjet As Microsoft.Office.Interop.Access.Dao.Database
    Dim qdfControl As Microsoft.Office.Interop.Access.Dao.QueryDef
    Dim rstControl As Microsoft.Office.Interop.Access.Dao.Recordset

    dbsjet = CurrentDB
    qdfControl = dbsjet.QueryDefs("Q_Solver Options")
    qdfControl.Parameters("Q_Parameter").Value = ParameterName
    rstControl = qdfControl.OpenRecordset
    GetSolverOption = rstControl.Fields("Value").Value
    rstControl.Close()
End Function

This function gets called many times without any issue, but then the GetSolverOption = rstControl.Fields("Value").Value line starts throwing "Item not found in collection" exceptions because the Fields collection is empty.

Answer 1

oK, is this by any chance asp.net? Or just desktop?

in asp.net? there is bug. And you don't mention if you using the .net ODBC provider, or the oleDB provider. You are free to use either one.

and I don't know if you are say opening a new connection each time, or on startup create a global connection object and use it over and over?

I don't think you should reference or use the DAO library in .net (and I don't know or think you are). If you are - don't - DAO is un-managed and will cause memory leaks.

So, to read some data say into a table? You can use this:

    Dim MyTable As New DataTable

    Using cmdSQL As New OdbcCommand("SELECT ID, FirstName, LastName from tblhotels",
                        New OdbcConnection(My.Settings.TESTAce))
        cmdSQL.Connection.Open()

        MyTable.Load(cmdSQL.ExecuteReader)

    End Using

Now in above, we do create a connection, and then open it, and then due to the "using block" of code it is supposed to be disposed and tossed out. But, if you are using asp.net, then after about 60 opens - it will blow up. And you have to add to above a dispose command - this is due to the connection pooling of asp.net.

so to fix this, then:eg:

        MyTable.Load(cmdSQL.ExecuteReader)

        cmdSQL.Dispose

    End Using

And in above? I used the ODBC .net provider. If you use the oleDB provider (and you can), then above really is the "same", but with using the oleDB provider, and you get this:

    Dim MyTable As New DataTable

    Using cmdSQL As New OleDbCommand("SELECT ID, FirstName, LastName from tblhotels",
                        New OleDbConnection(My.Settings.TESTAce))
        cmdSQL.Connection.Open()

        MyTable.Load(cmdSQL.ExecuteReader)

    End Using

So note how you can swap out in your code the "provider". But, the base objects like datatable, dataset, and datarow are ALL the same in all cases.

In fact, because of above? I would actually consider using the ODBC provider, since then you can swap out the connection strings, and say start using SQL server - and with VERY little code changes. And both me and Microsoft does not recommend using the oleDB provider against sql server anymore. But they DO recommend that both sqprovider (the native .net provider for sql server), or ODBC are still both wide supported and suggested providers to use.

But they also recommend that you avoid oleDB against sql server. In fact the ONLY real compelling reason to use oleDB these days? Why of course if you still working against Access (ACE/JET data engine).

Don't' worry too much about the above. But you don't note nor mention if you using a new connection object each time, or wrapping the whole mess in a using block (which should dispose both the connection object and command object automatic for you.

And note while I used "new connection object" in above, I could have used a pre-created oleDB (or odbc) connection object that was/is pre-created - the sql command object accepts both a string, or in fact a connection object - your choice.

So, first real question: do you dispose of the connection object each use as per above or are you re-using one given connection object? I would consider creating a connection object ONE time for the whole application - this could/would eliminate the re-create and dispose of the connection object.

However, you MOST certainly do NOT want to create a new connection object over and over without a using block - or at the very least having a connection.dispose in your code. This would explain the over time - things start to go south in your code.

Edit: Ok, given that we have say a bunch of code - VBA, and we want to convert + use in say vb.net?

I done this before, and the conversion rate is not all that bad.

So, say take this code snip:

VBA:

  Dim MyDB As Database
  Dim myRST As Recordset
  Set MyDB = CurrentDb()
  dim strSQL as String
  strSQl = "SELECT * from ProjectComponentHeader where ID = MyForm.ProjectCompoentID
  Set myRST = MyDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
  myRST.Edit
  myRST!StPrepress = "WAIT - APPROVAL"
  If Nz(myRST!FirstProofSentDate, 0) = 0 Then
      myRST!FirstProofSentDate = Now()
  End If
  myRST!ProofSentDate = Now()
  myRST.Update

So, to re-factor above, using say the oleDB provider in .net?

The above would become this:

    Dim da As OleDbDataAdapter
    Dim rst As DataRow
    Dim strSQL As String
    strSQL = "SELECT * from ProjectComponentHeader where ID = " & ProjectCompoentID
    rst = MyrstEdit(strSQL).Rows(0),"",da)

    rst("StPrepress") = "WAIT - APPROVAL"
    If Nz(rst("FirstProofSentDate"), 0) = 0 Then
        rst("FirstProofSentDate") = Now()
    End If
    rst("ProofSentDate") = Now()
    da.Update(Myrst)

So, I built say a routine called MyRst, and MyRstEdit. They just return a datatable.

And I built a nz() function.

so, now, I am "more" re-factoring the code. I really don't have to re-write the existing code logic - but just re-factor to using the .net objects. I done the the above with some rather messy VBA, and some rather long and complex routines.

I mean, even pulling in the VBA code, and using the DAO object reference? You still had to "go over" the code and refactor as vb.net code. As you can well note, that process goes quite fast - and is not too much work. Without question, there is extra work to convert - but it not a huge amount of work to convert those routines - and not a lot more then you having used say DAO objects as opposed to say the .net objects (datatable, datarow, and MORE rare dataset).

So, I built a MyRst, and a MyRstEdit function - they return a .net data table. As a result, I actually find that a lot of routines wind up with a bit LESS code then before, or at the very least code that follows the same logic and flow as the VBA code.