Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
dao cannot open any more databases
Hi all,
I've been working on this project to convert this VB6 Access DB project to .NET. Things are going OK except I have run into persistent dao cannot open any more databases error.
I have diligently tracked down every openrecordset/db connection to close them down and set to nothing after use, but it still pop out upon large number of queries. Could anyone please give some pointers as to what to do next?
I begin to suspect whether the recordset.close truly close the connection etc or is it that's an intrinsic trait of Microsoft Access cannot handle repeated open/close etc (I think I read somewhere that is the case and microsoft said it is by design...)
Anyway, looking forward to some helpful hints,
T
I've been working on this project to convert this VB6 Access DB project to .NET. Things are going OK except I have run into persistent dao cannot open any more databases error.
I have diligently tracked down every openrecordset/db connection to close them down and set to nothing after use, but it still pop out upon large number of queries. Could anyone please give some pointers as to what to do next?
I begin to suspect whether the recordset.close truly close the connection etc or is it that's an intrinsic trait of Microsoft Access cannot handle repeated open/close etc (I think I read somewhere that is the case and microsoft said it is by design...)
Anyway, looking forward to some helpful hints,
T
Asked:
-
3
-
2
1 Solution
mdougan,
I agree that dao shouldn't be used. However there are way too many places in the program to do quick conversion to the .NET way (ADO ?)
The program already did the movefirst/movelast combo thing to force populate recordset... I am not sure I understand the implication of the different "cursortype" when openrecordset. Could you give a quick explanation as to which one can be more "reliably" closed afterwards?
T
I agree that dao shouldn't be used. However there are way too many places in the program to do quick conversion to the .NET way (ADO ?)
The program already did the movefirst/movelast combo thing to force populate recordset... I am not sure I understand the implication of the different "cursortype" when openrecordset. Could you give a quick explanation as to which one can be more "reliably" closed afterwards?
T
Well, it is hard to remember which of my old programs uses DAO and which uses ADO... most people made the switch many years ago. Here is one routine that I think uses DAO. Our database was SQL Server, and so, we wrote all of our SQL in Stored Procedures, so, to execute a Stored Procedure, you create a QueryDef object, and you do a DB_SQLPASSTHROUGH call, which tells DAO not to try to interpret the Query, but rather just pass it along to SQL Server to worry about.
In the OpenRecordset statement, the dbOpenSnapshot flag specifies the "CursorType". This type of cursor will be a readonly cursor, as it is just a snapshot of the data... not to be used for updating. Not sure what the "CursorLocation" for this would be, but would guess something like dbUseClient, meaning that it should bring all the results back to the client right-away. The other option is dbUseServer which can be more efficient, in that it only brings back some of the data to the client right-away, and then it will bring down the rest as you movenext through the recordset.
We opened our database at the beginning of the program, and kept it open the whole time, until the end of the program. Whenever we created the QueryDef object, we associated it with the database by using our database variable db.... so, for example
Dim lqd as QueryDef
lqd = db.CreateQueryDef(......
Are you opening and closing your database after each query? Or maybe you are opening a connection to the db before each query, and then not closing the db object when done?
Dim SAVE As String
Dim lrs As Recordset
Dim lqd As QueryDef
Dim Parms As Variant
Dim sErrors As String
Dim lError As Variant
On Error GoTo FillCharactersErr
SAVE = cboCharacter.Text
cboCharacter.Clear
cboCharacter.AddItem "[None]"
cboCharacter.ItemData(cboC
Parms = Array(gUserShow, Mode)
' this is a function we wrote that does a CreateQueryDef statement
PrepareQuery lqd, "SPC_SEL_CHARACTER_BY_SHOW
Set lrs = lqd.OpenRecordset(dbOpenSn
If lrs.RecordCount <> 0 Then
lrs.MoveLast
lrs.MoveFirst
While Not lrs.EOF
cboCharacter.AddItem ValidString(lrs("CHARACTER
cboCharacter.ItemData(cboC
lrs.MoveNext
Wend
End If
cboCharacter.Text = SAVE
lrs.Close
lqd.Close
In the OpenRecordset statement, the dbOpenSnapshot flag specifies the "CursorType". This type of cursor will be a readonly cursor, as it is just a snapshot of the data... not to be used for updating. Not sure what the "CursorLocation" for this would be, but would guess something like dbUseClient, meaning that it should bring all the results back to the client right-away. The other option is dbUseServer which can be more efficient, in that it only brings back some of the data to the client right-away, and then it will bring down the rest as you movenext through the recordset.
We opened our database at the beginning of the program, and kept it open the whole time, until the end of the program. Whenever we created the QueryDef object, we associated it with the database by using our database variable db.... so, for example
Dim lqd as QueryDef
lqd = db.CreateQueryDef(......
Are you opening and closing your database after each query? Or maybe you are opening a connection to the db before each query, and then not closing the db object when done?
Dim SAVE As String
Dim lrs As Recordset
Dim lqd As QueryDef
Dim Parms As Variant
Dim sErrors As String
Dim lError As Variant
On Error GoTo FillCharactersErr
SAVE = cboCharacter.Text
cboCharacter.Clear
cboCharacter.AddItem "[None]"
cboCharacter.ItemData(cboC
Parms = Array(gUserShow, Mode)
' this is a function we wrote that does a CreateQueryDef statement
PrepareQuery lqd, "SPC_SEL_CHARACTER_BY_SHOW
Set lrs = lqd.OpenRecordset(dbOpenSn
If lrs.RecordCount <> 0 Then
lrs.MoveLast
lrs.MoveFirst
While Not lrs.EOF
cboCharacter.AddItem ValidString(lrs("CHARACTER
cboCharacter.ItemData(cboC
lrs.MoveNext
Wend
End If
cboCharacter.Text = SAVE
lrs.Close
lqd.Close
mdougan
Thanks for the explanation.
I've resolved the problem. Here is what happened:
I have this function that would open a recordset like the following (to a MDB) at the beginning of a function (which gets called numerous times repeatedly):
fldRec = stateDB.OpenRecordset(Me.Y
fldRec.MoveLast()
fldRec.MoveFirst()
Then there are a bunch of complicated actions taken through many conditional branches. I closed the recordset at the very end of the function, however, there are Return(s) somewhere midway in the function that I didn't catch, hence, whenever it reaches the end of the function, the recordset is closed, if not, then that connection is still open, hence the problem.
I think recordset.Close() would indeed close the actual connection in the dao world, so this means, you can have a persistent MDB handle (through OpenDatabase call), but it is the OpenRecordset that is opening the connection, which needs to be closed after each use.
I would assign partial points for your prompt response and clear explanation, but not sure how to do that though...
Thanks
T
Thanks for the explanation.
I've resolved the problem. Here is what happened:
I have this function that would open a recordset like the following (to a MDB) at the beginning of a function (which gets called numerous times repeatedly):
fldRec = stateDB.OpenRecordset(Me.Y
fldRec.MoveLast()
fldRec.MoveFirst()
Then there are a bunch of complicated actions taken through many conditional branches. I closed the recordset at the very end of the function, however, there are Return(s) somewhere midway in the function that I didn't catch, hence, whenever it reaches the end of the function, the recordset is closed, if not, then that connection is still open, hence the problem.
I think recordset.Close() would indeed close the actual connection in the dao world, so this means, you can have a persistent MDB handle (through OpenDatabase call), but it is the OpenRecordset that is opening the connection, which needs to be closed after each use.
I would assign partial points for your prompt response and clear explanation, but not sure how to do that though...
Thanks
T
Hi TongZ
No worries, you can feel free to delete the question and ask that your points be refunded. Glad you found your solution!
I always added the code to close the recordsets just before the return, because you could logically see what recordsets would be open at that time. Some people will code a label at the bottom of the routine and GoTo it in all cases, and the close statements would follow that label, but then others don't like using GoTos.
Cheers!
No worries, you can feel free to delete the question and ask that your points be refunded. Glad you found your solution!
I always added the code to close the recordsets just before the return, because you could logically see what recordsets would be open at that time. Some people will code a label at the bottom of the routine and GoTo it in all cases, and the close statements would follow that label, but then others don't like using GoTos.
Cheers!
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
I know that under VB6, there were issues, depending on the "cursortype". If it was a server-side cursor, you had to read all the way to the end of the recordset before the cursor would actually close... so, you could try doing a MoveFirst then MoveLast just prior to doing your Close and then setting to Nothing.
But, under .NET, I wouldn't recommend using DAO. Rather, you should be working with SQLCommand objects, Dataset objects and DataReader objects.