type argument in OpenRecordset method

[mirandacascade]

The questions are toward the bottom of this post.

Situation is this:
1) Access 97
2) Multi-user appplication
3) SQL Server 2000
4) Sporadically (i.e. less than 1% of the time) encounter the
following error:

3218 Couldn't update; currently locked

in a particular function (function name is
GetFullyQualifiedTableName).

The code from the GetFullyQualifiedTableName function is below:

*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifiedTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFullyQualifiedTableName
strExec = "EXEC dbo.uspDummyGetFullyQualifiedTableName "
' GetStandardParmams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardParams()

' the value of qdfReusable is established outside this function;
qdfReusable is:
' - a querydef object
' - where the .Connect property is an ODBC connection

' set the .SQL property of the querydef to the string that gets
created in the code above
qdfReusable.SQL = strExec
qdfReusable.ReturnsRecords = True
' Session.PassThruQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenRecordset(Session.PassThruQryName)
GetFullyQualifiedTableName = rst.Fields(0)
rst.Close
*** code ends on previous line ***

The fact that

Questions:
1) Is it likely that the 3218 exception is happening on the call to
the OpenRecordset method?
2) Rather than relying on a default value, if I were to supply an
explicit value in the type argument of the OpenRecordset method, might
that eliminate the 3218 error? If so, would you recommend a value in
the type argument of dbOpenSnapshot?
3) the Help fiile within MS Access tells me what the valid values that
may be supplied in the type argument, but it seems a bit sparse as far
as suggesting: "in this type of envirnoment, you should specify the
following value in the type argument..." can you point me to some
reference material that offers a 'best practice' advice for setting
the value of the type argument in the OpenRecordset method?

Thank you.

[bcap]

For efficiency reasons you should always open the least-functional recordset
that you need.

Since you are using SQL Server linked tables, you have a choice of three:

dbOpenForwardOnly: recordset can't be updated, and you can't navigate in it
except to iterate forwards from beginning to end.
dbOpenSnapshot: recordset can't be updated, but you can navigate in it.
dbOpenDynaset: recordset is updatable and fully navigable (this is the
default).

In your case, since the recordset apparently only returns one record and you
don't need to update it, dbOpenForwardOnly should do the job.

However, I am far from convinced that this is the cause of your problem.
The error you are getting is an obscure Access error and my guess is that it
has nothing to do with locking in the SQL Server database (particularly
since you appear to be doing nothing which updates the database) and is
something to do with locking in your Access front-end file, perhaps when you
try to update the SQL property of the query. Presumably you have some kind
of error handling which tells you that this is the procedure where the error
occurs: if so, it would be a good idea to enhance it to report the line
number where the error occurs.

Does each user have their own copy of your Access front-end, or are they
sharing the same file? If the latter, this is a bad idea and you should
distribute a "local" copy to each user. Does the front-end get regularly
compacted/repaired?

<mi************@yahoo.comwrote in message
news:98**********************************@y38g2000 hsy.googlegroups.com...

The questions are toward the bottom of this post.

Situation is this:
1) Access 97
2) Multi-user appplication
3) SQL Server 2000
4) Sporadically (i.e. less than 1% of the time) encounter the
following error:

3218 Couldn't update; currently locked

in a particular function (function name is
GetFullyQualifiedTableName).

The code from the GetFullyQualifiedTableName function is below:

*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifiedTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFullyQualifiedTableName
strExec = "EXEC dbo.uspDummyGetFullyQualifiedTableName "
' GetStandardParmams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardParams()

' the value of qdfReusable is established outside this function;
qdfReusable is:
' - a querydef object
' - where the .Connect property is an ODBC connection

' set the .SQL property of the querydef to the string that gets
created in the code above
qdfReusable.SQL = strExec
qdfReusable.ReturnsRecords = True
' Session.PassThruQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenRecordset(Session.PassThruQryName)
GetFullyQualifiedTableName = rst.Fields(0)
rst.Close
*** code ends on previous line ***

The fact that

Questions:
1) Is it likely that the 3218 exception is happening on the call to
the OpenRecordset method?
2) Rather than relying on a default value, if I were to supply an
explicit value in the type argument of the OpenRecordset method, might
that eliminate the 3218 error? If so, would you recommend a value in
the type argument of dbOpenSnapshot?
3) the Help fiile within MS Access tells me what the valid values that
may be supplied in the type argument, but it seems a bit sparse as far
as suggesting: "in this type of envirnoment, you should specify the
following value in the type argument..." can you point me to some
reference material that offers a 'best practice' advice for setting
the value of the type argument in the OpenRecordset method?

Thank you.

[mirandacascade]

On Sep 19, 11:21*pm, "bcap" <[e-mail]:

Since you are using SQL Server linked tables, you have a choice of three:

dbOpenForwardOnly: recordset can't be updated, and you can't navigate in it
except to iterate forwards from beginning to end.
dbOpenSnapshot: recordset can't be updated, but you can navigate in it.
dbOpenDynaset: recordset is updatable and fully navigable (this is the
default).

In your case, since the recordset apparently only returns one record and you
don't need to update it, dbOpenForwardOnly should do the job.

I didn't mention this in the original post (and I don't think it
changes the information/advice above regarding which value to choose
in the type argument of the OpenRecordset) but in case it is, in fact,
relevant, I'll mention it here because I noticed a reference to
'...linked tables':

The Access app does NOT have any linked tables. The Access app calls
a stored proc, and that sproc performs a SQL Server 2000 SELECT
statement from a SQL SERVER 2000 table...that SELECT statement returns
the result set back to the Access app. But if one were to look at the
Tables tab of the Database window of the MS Access app, one would not
see any linked tables.

However, I am far from convinced that this is the cause of your problem.
The error you are getting is an obscure Access error and my guess is thatit
has nothing to do with locking in the SQL Server database (particularly
since you appear to be doing nothing which updates the database)

....correct...no updates going on

and is something to do with locking in your Access front-end file, perhaps when you
try to update the SQL property of the query. *Presumably you have some kind
of error handling which tells you that this is the procedure where the error
occurs:

....correct...the function has ON ERROR logic that informs me that the
error happened somewhere within the function

if so, it would be a good idea to enhance it to report the line number where the error occurs.

....yes, that is a good idea. I know how to do that in a "brute force"
manner, e.g. by:
a) updating a variable after each line successfully executes
b) having the exception handling code display the contents of
the variable
but I was wondering whether MS Access provides a more elegant way for
me to do that...is there some built-in value that identifies the
specific line on which the error occurred?

Does each user have their own copy of your Access front-end, or are they
sharing the same file? *

It's more like the former than the latter. The application is
distributed to multiple servers across the company network, so it
resembles the "each user has their own copy", but there may be more
than one user of the application from the same server...there are not,
however, a large number of users sharing the same app on any given
server. The most active server has about a dozen users sharing that
application, but it's never the case that there are more than 3 or 4
users on at any given time, and it's usually the case that there is
usually zero or one user on at any given time.

If the latter, this is a bad idea and you should
distribute a "local" copy to each user. *Does the front-end get regularly
compacted/repaired?

The front-end gets compacted/repaired about once every other month.

Thank you.

[JvC]

I am not sure if this will solve your problem, but I would do the
following:
Don't use CurrentDB. I have found that it occasionally "flakes out".
Use a Database variable. Be explicit.

Dim db as DAO.Database

If this needs to be an updatable recordset, use:
Set rst = db.OpenRecordset(Session.PassThruQryName, dbOpenDynaset,
dbSeeChanges)

If it does not need to be updatabale use:
Set rst = db.OpenRecordset(Session.PassThruQryName,
dbOpenSnapshot)

John

mi************@yahoo.com wrote on 9/19/2008 :

The questions are toward the bottom of this post.

Situation is this:
1) Access 97
2) Multi-user appplication
3) SQL Server 2000
4) Sporadically (i.e. less than 1% of the time) encounter the
following error:

3218 Couldn't update; currently locked

in a particular function (function name is
GetFullyQualifiedTableName).

The code from the GetFullyQualifiedTableName function is below:

*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifiedTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFullyQualifiedTableName
strExec = "EXEC dbo.uspDummyGetFullyQualifiedTableName "
' GetStandardParmams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardParams()

' the value of qdfReusable is established outside this function;
qdfReusable is:
' - a querydef object
' - where the .Connect property is an ODBC connection

' set the .SQL property of the querydef to the string that gets
created in the code above
qdfReusable.SQL = strExec
qdfReusable.ReturnsRecords = True
' Session.PassThruQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenRecordset(Session.PassThruQryName)
GetFullyQualifiedTableName = rst.Fields(0)
rst.Close
*** code ends on previous line ***

The fact that

Questions:
1) Is it likely that the 3218 exception is happening on the call to
the OpenRecordset method?
2) Rather than relying on a default value, if I were to supply an
explicit value in the type argument of the OpenRecordset method, might
that eliminate the 3218 error? If so, would you recommend a value in
the type argument of dbOpenSnapshot?
3) the Help fiile within MS Access tells me what the valid values that
may be supplied in the type argument, but it seems a bit sparse as far
as suggesting: "in this type of envirnoment, you should specify the
following value in the type argument..." can you point me to some
reference material that offers a 'best practice' advice for setting
the value of the type argument in the OpenRecordset method?

Thank you.

[bcap]

Hi again,

You are correct, the advice is not affected, dbOpenForwardOnly is still the
one you need in this situation.

The VBA function "Erl" returns the line number where the most recent error
occurred. You will need to number all your lines, but that shouldn't be too
great a hardship if the procedure is not large.

Having any users sharing a front-end file is a bad idea, but in your case
it's doubly bad because of the way you are using "qdfreusable". There is
every possibility that your users are colliding over the use of this query
object.

<mi************@yahoo.comwrote in message
news:b5**********************************@2g2000hs n.googlegroups.com...
On Sep 19, 11:21 pm, "bcap" <[e-mail]:

Since you are using SQL Server linked tables, you have a choice of three:

dbOpenForwardOnly: recordset can't be updated, and you can't navigate in
it
except to iterate forwards from beginning to end.
dbOpenSnapshot: recordset can't be updated, but you can navigate in it.
dbOpenDynaset: recordset is updatable and fully navigable (this is the
default).

In your case, since the recordset apparently only returns one record and
you
don't need to update it, dbOpenForwardOnly should do the job.

I didn't mention this in the original post (and I don't think it
changes the information/advice above regarding which value to choose
in the type argument of the OpenRecordset) but in case it is, in fact,
relevant, I'll mention it here because I noticed a reference to
'...linked tables':

The Access app does NOT have any linked tables. The Access app calls
a stored proc, and that sproc performs a SQL Server 2000 SELECT
statement from a SQL SERVER 2000 table...that SELECT statement returns
the result set back to the Access app. But if one were to look at the
Tables tab of the Database window of the MS Access app, one would not
see any linked tables.

However, I am far from convinced that this is the cause of your problem.
The error you are getting is an obscure Access error and my guess is that
it
has nothing to do with locking in the SQL Server database (particularly
since you appear to be doing nothing which updates the database)

....correct...no updates going on

and is something to do with locking in your Access front-end file, perhaps
when you
try to update the SQL property of the query. Presumably you have some kind
of error handling which tells you that this is the procedure where the
error
occurs:

....correct...the function has ON ERROR logic that informs me that the
error happened somewhere within the function

if so, it would be a good idea to enhance it to report the line number
where the error occurs.

....yes, that is a good idea. I know how to do that in a "brute force"
manner, e.g. by:
a) updating a variable after each line successfully executes
b) having the exception handling code display the contents of
the variable
but I was wondering whether MS Access provides a more elegant way for
me to do that...is there some built-in value that identifies the
specific line on which the error occurred?

Does each user have their own copy of your Access front-end, or are they
sharing the same file?

It's more like the former than the latter. The application is
distributed to multiple servers across the company network, so it
resembles the "each user has their own copy", but there may be more
than one user of the application from the same server...there are not,
however, a large number of users sharing the same app on any given
server. The most active server has about a dozen users sharing that
application, but it's never the case that there are more than 3 or 4
users on at any given time, and it's usually the case that there is
usually zero or one user on at any given time.

If the latter, this is a bad idea and you should
distribute a "local" copy to each user. Does the front-end get regularly
compacted/repaired?

The front-end gets compacted/repaired about once every other month.

Thank you.

[mirandacascade]

On Sep 22, 11:56*am, "bcap" <[e-mail]:

The VBA function "Erl" returns the line number where the most recent error
occurred. *You will need to number all your lines, but that shouldn't be too
great a hardship if the procedure is not large.

Thank you...that looks like it will be a very helpful tool/utility.

Having any users sharing a front-end file is a bad idea, but in your case
it's doubly bad because of the way you are using "qdfreusable". *There is
every possibility that your users are colliding over the use of this query
object.

Apologies...I didn't put all the implementation details in the
original post or the follow up post...specifically, I failed to
include an important detail about how collisions are avoided. There
is no possibility of the type of collision about which you warned.
The reasons for this are:
1) at the beginning of the session (when app user opens the app), the
app calls NT_GetUserName in advapi32.dll
2) NT_GetUserName returns a value that differs for each user
3) when the app instantiates the qdfreusable querydef object, it
includes the results of NT_GetUserName in the name of the querydef
object
So, for example, if there were a dozen users simultaneously using the
application, there would be a dozen differently-named querydef objects
instantiated.

Thanks for the information...if I'm interpreting the replies
correctly, it sounds as though it's still a bit of a mystery as to why
the 3218 error is occurring.

[bcap]

It's *still* a bad idea to have users sharing a front-end, and I'm still
willing to bet that this is the cause of your problem, whether or not
qdfreusable is at the root of it.

Finding out what line the problem is occurring on will be important.
<mi************@yahoo.comwrote in message
news:88**********************************@26g2000h sk.googlegroups.com...
On Sep 22, 11:56 am, "bcap" <[e-mail]:

The VBA function "Erl" returns the line number where the most recent error
occurred. You will need to number all your lines, but that shouldn't be
too
great a hardship if the procedure is not large.

Thank you...that looks like it will be a very helpful tool/utility.

Having any users sharing a front-end file is a bad idea, but in your case
it's doubly bad because of the way you are using "qdfreusable". There is
every possibility that your users are colliding over the use of this query
object.

Apologies...I didn't put all the implementation details in the
original post or the follow up post...specifically, I failed to
include an important detail about how collisions are avoided. There
is no possibility of the type of collision about which you warned.
The reasons for this are:
1) at the beginning of the session (when app user opens the app), the
app calls NT_GetUserName in advapi32.dll
2) NT_GetUserName returns a value that differs for each user
3) when the app instantiates the qdfreusable querydef object, it
includes the results of NT_GetUserName in the name of the querydef
object
So, for example, if there were a dozen users simultaneously using the
application, there would be a dozen differently-named querydef objects
instantiated.

Thanks for the information...if I'm interpreting the replies
correctly, it sounds as though it's still a bit of a mystery as to why
the 3218 error is occurring.

[lyle fairfield]

On Sep 19, 2:35*pm, [e-mail] wrote:

GetFullyQualifiedTableName).

The fully qualified table name precisely identifies the table:
Server.Database.Schema.Name

If one doesn't know the fully qualified table name then how could one
be sure he/she is getting the fully qualified table name of the
correct table?

I suppose one might not know the Schema or Owner, but as there could
be identically named tables in various databases on the same server
surely one would have to know the database name?

This cannot (unless our connection defaults to a specific database)
find the table and returns no records:
SELECT
[so].[name] AS TableName
,[ss].[name] AS SchemaName
FROM
[sys].[objects]
AS so
INNER JOIN
[sys].[schemas]
AS ss
ON
[so].[schema_id] = [ss].[schema_id]
WHERE [so].[type] = 'U'
AND [so].[name] = 'Schools'

This can:

SELECT
[so].[name] AS TableName
,[ss].[name] AS SchemaName
FROM
[FFDBA-LAPTOP\SONYLAPTOP].[FFDBA_ESO].[sys].[objects]
AS so
INNER JOIN
[FFDBA-LAPTOP\SONYLAPTOP].[FFDBA_ESO].[sys].[schemas]
AS ss
ON
[so].[schema_id] = [ss].[schema_id]
WHERE [so].[type] = 'U'

So can this:

USE [FFDBA_ESO]

SELECT
[so].[name] AS TableName
,[ss].[name] AS SchemaName
FROM
[sys].[objects]
AS so
INNER JOIN
[sys].[schemas]
AS ss
on
[so].[schema_id] = [ss].[schema_id]
WHERE [so].[type] = 'U'
AND [so].[name] = 'Schools'
AND [so].[name] = 'Schools'