Re: type argument in OpenRecordset method
- From: "bcap" <bcap@xxxxxxxxxxxxxx>
- Date: Sat, 20 Sep 2008 07:21:24 +0100
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?
<mirandacascade@xxxxxxxxx> wrote in message
news:987ca5ee-7563-4d04-b296-4c725fccad32@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
.
- Follow-Ups:
- Re: type argument in OpenRecordset method
- From: mirandacascade
- Re: type argument in OpenRecordset method
- References:
- type argument in OpenRecordset method
- From: mirandacascade
- type argument in OpenRecordset method
- Prev by Date: Re: Row Source
- Next by Date: Re: Combo boxes on a report
- Previous by thread: type argument in OpenRecordset method
- Next by thread: Re: type argument in OpenRecordset method
- Index(es):
Relevant Pages
|
