Object invalid or no longer set error involving database object. H

[rhc]

im running access 2003 on xp. i have started seeing this bug in somve vba
routines where if I declare a dao database object then:
Set D = currentdb()
Set R = D.recordset(sSQL,dbOpenDynaset,dbSeeChanges)
loop through R updating rows.
R.close
Set RD.openrecordset(sSQL,dbOpenDynaset,dbSeeChanges)
Here I get the Object invalid or no longer set error. The database object D
seems to have become uninstantiated for no apparent reason. The only thing
that has changed recently in the code is the addition of the dbSeeChanges in
the recordset statements. I add these in to make the UI mdb the code is in
compatible with a sql server 2005 backend that im in the process of
developing. to be clear the above error happens wihen linked to the original
access mdb backend.
Could deSeeChanges cause this?
Is there a size limitation on the modules associated with forms? The ui mdb
as a lot of stuff in it but ithe file size is still only 12MB

Help!!!!

 

[Allen Browne]

What's RD?
You have not set that to anything.

You've not included all of your code, but add the line:
Option Explicit
to the top of this module. Then choose Compile on the Debug menu. This
should catch RD as an undeclared variable.

 

[Chris O'C via AccessMonster.com]

Set RD.openrecordset(sSQL,dbOpenDynaset,dbSeeChanges)

Are you sure there isn't a typo in there? Is the equals sign missing between
the R and the D of RD? R is a recordset and D is a database, but there's no
RD variable declared or used in the code above that line. Do you have Option
Explicit declared at the top of every module?

dbSeeChanges doesn't cause that error. The RD object isn't valid because it
hasn't been set correctly before attempting to open the recordset. Looks
like you're trying to do both in the same line of code.

A module has limitations but you're nowhere near them.

The limits for a Jet 4 db are:

Max 1,024 characters per line.
Max 6,601 lines per procedure.
Max 65,545 lines per module.
Max 1,000 modules per database.

Chris
Microsoft MVP

 

[rhc]

thanks for the response. no thats not the problem all the variables have been
declared. i was just trying to be brief. Option Explicit is set.
Dim D As DAO.Database
Dim R As DAO.Recordset
Dim RD As DAO.Recordset

 

[rhc]

btw D is set with
Set D = currentdb()

What's RD?
You have not set that to anything.

You've not included all of your code, but add the line:
Option Explicit
to the top of this module. Then choose Compile on the Debug menu. This
should catch RD as an undeclared variable.

 

[Chris O'C via AccessMonster.com]

If it's not a typo, then fix the syntax errors.

Set D = CurrentDb()
Set R = D.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
' loop through R updating rows.
R.Close
Set RD = D.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

In your attempt to be brief, I think you're missing some vital code in there.
Why not copy and paste it into your next post, then delete the loop part of
the code and replace it with the comment, like you did earlier?

Chris
Microsoft MVP

 

[rhc]

apologies
im working on to many things at once. when I have time later today I will
post all the exact code. In the meantime can you tell me is there a size
limitation for form vba modules?
thanks
Bob

 

[Chris O'C via AccessMonster.com]

Max 65,545 lines per module. That's for form modules, class modules and
standard modules.

Chris
Microsoft MVP