There is no message for this error

[Scott]

Hello,
I am having a problem with my access DB. I have created a simple button
that runs the following code:
----------------------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT [User_Settings].* FROM [User_Settings] WHERE ("
wrk = "[User_Settings].[UserAccessID] = '" & CurrentUser() & "'"
strSQL = strSQL & wrk & ");"
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
Text1.Value = ufr("passwordExpireDate")
----------------------------------------------------

When I click the button I get the following error:
----------------------------------------------------
Run-time error '3000'
Reserved error (-7776); there is no message for this error.
----------------------------------------------------

When I click debug it sends me to the line "Set ufr =
sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)". But when I F8 through the
code everything works and continues to work until the access DB is open
again. Any help or comments on this behavior would be greatly appreciated.

Thanks,
-Scott

 

[James A. Fortune]

Hello,
I am having a problem with my access DB. I have created a simple button
that runs the following code:
----------------------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT [User_Settings].* FROM [User_Settings] WHERE ("
wrk = "[User_Settings].[UserAccessID] = '" & CurrentUser() & "'"
strSQL = strSQL & wrk & ");"
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
Text1.Value = ufr("passwordExpireDate")
----------------------------------------------------

When I click the button I get the following error:
----------------------------------------------------
Run-time error '3000'
Reserved error (-7776); there is no message for this error.
----------------------------------------------------

When I click debug it sends me to the line "Set ufr =
sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)". But when I F8 through the
code everything works and continues to work until the access DB is open
again. Any help or comments on this behavior would be greatly appreciated.

Thanks,
-Scott

Maybe:

Dim strSQL As String
Dim ufr As Recordset
Dim wrk As String
Dim sqldb As Database

Set sqldb = CurrentDb
strSQL = "SELECT * FROM User_Settings WHERE ("
wrk = "UserAccessID = " & Chr(34) & CurrentUser() & Chr(34)
strSQL = strSQL & wrk & ");"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, dbOpenSnapshot)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If

Your main problem is likely to be caused by the constant
DB_OPEN_SNAPSHOT. Access is possibly taking too much time looking
through all the constants for such a thing. I haven't seen
DB_OPEN_SNAPSHOT since Access 2.0. Also, Access is usually happier when
you explicitly move to the record you want rather than depending on the
recordset to go to the first record when you open it. I use Chr(34),
the double quote character, mostly out of habit so that in cases where a
name contains a single quote (probably not needed here) it doesn't cause
an error. Only being able to run the code using F8 is a possible sign
of a timing issue. If it is a timing issue, maybe a DoEvents would give
Access time to evaluate CurrentUser() or come up with something to use
for DB_OPEN_SNAPSHOT before the code optimization tries to be too
helpful. Anyway, try the code above and post back if it does not solve
your problem.

James A. Fortune
(e-mail address removed)

 

[Scott]

Thank you for that modification. unfortunately it did not work.
I have made the following modifications to the code but still receive the
error:
------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT * FROM [User_Settings] WHERE [UserAccessID] =
'Administrator'"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If
------------------------------------
I would also like to not that this simple function works fine on one
computer but not another. In addition, it's not that the function works
only when I step through but rather after the error occurs. When I open the
DB and press the button I get the error, afterwards, weather I step through
the code or not the function works just fine. If I close teh DB and open it
again I get the error again. Originally I had thought this behavior had
something to do with corrupt ODBC files but now I am thinking it may be
something else.

-scott

Hello,
I am having a problem with my access DB. I have created a simple
button that runs the following code:
----------------------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT [User_Settings].* FROM [User_Settings] WHERE ("
wrk = "[User_Settings].[UserAccessID] = '" & CurrentUser() & "'"
strSQL = strSQL & wrk & ");"
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
Text1.Value = ufr("passwordExpireDate")
----------------------------------------------------

When I click the button I get the following error:
----------------------------------------------------
Run-time error '3000'
Reserved error (-7776); there is no message for this error.
----------------------------------------------------

When I click debug it sends me to the line "Set ufr =
sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)". But when I F8 through
the code everything works and continues to work until the access DB is
open again. Any help or comments on this behavior would be greatly
appreciated.

Thanks,
-Scott

Maybe:

Dim strSQL As String
Dim ufr As Recordset
Dim wrk As String
Dim sqldb As Database

Set sqldb = CurrentDb
strSQL = "SELECT * FROM User_Settings WHERE ("
wrk = "UserAccessID = " & Chr(34) & CurrentUser() & Chr(34)
strSQL = strSQL & wrk & ");"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, dbOpenSnapshot)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If

Your main problem is likely to be caused by the constant DB_OPEN_SNAPSHOT.
Access is possibly taking too much time looking through all the constants
for such a thing. I haven't seen DB_OPEN_SNAPSHOT since Access 2.0.
Also, Access is usually happier when you explicitly move to the record you
want rather than depending on the recordset to go to the first record when
you open it. I use Chr(34), the double quote character, mostly out of
habit so that in cases where a name contains a single quote (probably not
needed here) it doesn't cause an error. Only being able to run the code
using F8 is a possible sign of a timing issue. If it is a timing issue,
maybe a DoEvents would give Access time to evaluate CurrentUser() or come
up with something to use for DB_OPEN_SNAPSHOT before the code optimization
tries to be too helpful. Anyway, try the code above and post back if it
does not solve your problem.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Thank you for that modification. unfortunately it did not work.
I have made the following modifications to the code but still receive the
error:
------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT * FROM [User_Settings] WHERE [UserAccessID] =
'Administrator'"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If
------------------------------------
I would also like to not that this simple function works fine on one
computer but not another. In addition, it's not that the function works
only when I step through but rather after the error occurs. When I open the
DB and press the button I get the error, afterwards, weather I step through
the code or not the function works just fine. If I close teh DB and open it
again I get the error again. Originally I had thought this behavior had
something to do with corrupt ODBC files but now I am thinking it may be
something else.

-scott

Please try it exactly the way I posted first and let me know what
happens, along with what you see when you hover over some variables in
debug mode. That way I can rule out some possibilities. If you only
tried the code you just posted, it doesn't help me as much. Maybe it
only works on computers that had Access 2.0 installed at one time,
enabling them to understand the DB_OPEN_SNAPSHOT constant.

James A. Fortune
(e-mail address removed)

 

[Scott]

James,
I tried the code you posted prior to making those changes. I have
uncommented you code and ran again with the same result. It wold seem you
are correct about the error occuring because of the "dbOpenSnameshot"
constant but I'm not sure why. The computer it does run on right now never
had Access 2.0 installed on it. The computer that is throwing the error ran
it fine until a few days ago.

-scott

Thank you for that modification. unfortunately it did not work.
I have made the following modifications to the code but still receive the
error:
------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT * FROM [User_Settings] WHERE [UserAccessID] =
'Administrator'"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If
------------------------------------
I would also like to not that this simple function works fine on one
computer but not another. In addition, it's not that the function works
only when I step through but rather after the error occurs. When I open
the DB and press the button I get the error, afterwards, weather I step
through the code or not the function works just fine. If I close teh DB
and open it again I get the error again. Originally I had thought this
behavior had something to do with corrupt ODBC files but now I am
thinking it may be something else.

-scott

Please try it exactly the way I posted first and let me know what happens,
along with what you see when you hover over some variables in debug mode.
That way I can rule out some possibilities. If you only tried the code
you just posted, it doesn't help me as much. Maybe it only works on
computers that had Access 2.0 installed at one time, enabling them to
understand the DB_OPEN_SNAPSHOT constant.

James A. Fortune
(e-mail address removed)

 

[Scott]

James,
I'm not sure why this works but I have found a temporary work around. By
placing the following code after "Set sqldb = CurrentDb" it runs fine.
-----------------------------------------
Dim x as Recordset
Set x = sqldb.OpenRecordset("SELECT TOP 1 * FROM [User_Settings]")
x.Close
-----------------------------------------
I still don't know why this works nor do I know why using "dbOpenSnapshot"
causes the error in the first place. Any further advice you can offer about
this would be greatly appreciated.

-scott

Thank you for that modification. unfortunately it did not work.
I have made the following modifications to the code but still receive the
error:
------------------------------------
Dim strSQL As String, ufr As Recordset, wrk As String
Dim sqldb As Database

Set sqldb = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT * FROM [User_Settings] WHERE [UserAccessID] =
'Administrator'"
DoEvents
Set ufr = sqldb.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
If ufr.RecordCount > 0 Then
ufr.MoveFirst
Text1.Value = ufr("passwordExpireDate")
Else
Text1.Value = Null
End If
------------------------------------
I would also like to not that this simple function works fine on one
computer but not another. In addition, it's not that the function works
only when I step through but rather after the error occurs. When I open
the DB and press the button I get the error, afterwards, weather I step
through the code or not the function works just fine. If I close teh DB
and open it again I get the error again. Originally I had thought this
behavior had something to do with corrupt ODBC files but now I am
thinking it may be something else.

-scott

Please try it exactly the way I posted first and let me know what happens,
along with what you see when you hover over some variables in debug mode.
That way I can rule out some possibilities. If you only tried the code
you just posted, it doesn't help me as much. Maybe it only works on
computers that had Access 2.0 installed at one time, enabling them to
understand the DB_OPEN_SNAPSHOT constant.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James,
I'm not sure why this works but I have found a temporary work around. By
placing the following code after "Set sqldb = CurrentDb" it runs fine.
-----------------------------------------
Dim x as Recordset
Set x = sqldb.OpenRecordset("SELECT TOP 1 * FROM [User_Settings]")
x.Close
-----------------------------------------
I still don't know why this works nor do I know why using "dbOpenSnapshot"
causes the error in the first place. Any further advice you can offer about
this would be greatly appreciated.

-scott

Note that dbOpenSnapshot is different than DB_OPEN_SNAPSHOT because of
the underscore characters. In Access 97, both constants evaluate as 4.
Since you're getting into situations that are not covered by the
documentation, I can only speculate about what might have happened. It
is likely that the DB_OPEN_SNAPSHOT is maintained in later versions for
backward compatibility when old code is brought into later versions.
The first two letters give a hint about where Access looks up the
constant. E.g., vbCrLf or dbOpenDynaset. That's where I would start
looking to understand what went wrong.

James A. Fortune
(e-mail address removed)

 

[Scott]

James,
I tried using both "DB_OPEN_SNAPSHOT" and "dbOpenSnapshot" but got the same
result. What confuses me most now is that after those three lines of code
everything works just fine.

-scott

James,
I'm not sure why this works but I have found a temporary work around. By
placing the following code after "Set sqldb = CurrentDb" it runs fine.
-----------------------------------------
Dim x as Recordset
Set x = sqldb.OpenRecordset("SELECT TOP 1 * FROM [User_Settings]")
x.Close
-----------------------------------------
I still don't know why this works nor do I know why using
"dbOpenSnapshot" causes the error in the first place. Any further advice
you can offer about this would be greatly appreciated.

-scott

Note that dbOpenSnapshot is different than DB_OPEN_SNAPSHOT because of the
underscore characters. In Access 97, both constants evaluate as 4. Since
you're getting into situations that are not covered by the documentation,
I can only speculate about what might have happened. It is likely that
the DB_OPEN_SNAPSHOT is maintained in later versions for backward
compatibility when old code is brought into later versions. The first two
letters give a hint about where Access looks up the constant. E.g.,
vbCrLf or dbOpenDynaset. That's where I would start looking to understand
what went wrong.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James,
I tried using both "DB_OPEN_SNAPSHOT" and "dbOpenSnapshot" but got the same
result. What confuses me most now is that after those three lines of code
everything works just fine.

-scott

I can only guess. Maybe when the recordset returns just one result it
sets the current record to that record. Anyway, I always move to the
record I want explicitly.

James A. Fortune
(e-mail address removed)

The Clinton Administration was smart about dealing with a possible
recession. No matter how bad things got, it seemed that no one was
allowed to use the "R" word. It didn't prevent a recession, but it
definitely postponed its onset. In lieu of the re-establishment of
sound economic fundamentals, which is the correct way to heal the U.S.
economy, positive thinking and talking can lessen the length of the
pain. I'll never forget the look on Bill Clinton's face after George W.
Bush, in trying to gain the presidency said, "I think we're headed for a
recession."