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
GetFullyQualifi edTableName).
The code from the GetFullyQualifi edTableName function is below:
*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifi edTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFull yQualifiedTable Name
strExec = "EXEC dbo.uspDummyGet FullyQualifiedT ableName "
' GetStandardParm ams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardPara ms()
' 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.Ret urnsRecords = True
' Session.PassThr uQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenR ecordset(Sessio n.PassThruQryNa me)
GetFullyQualifi edTableName = 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.
7  11049 
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:
dbOpenForwardOn ly: 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, dbOpenForwardOn ly 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******** *************** ***********@y38 g2000hsy.google groups.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
GetFullyQualifi edTableName).
The code from the GetFullyQualifi edTableName function is below:
*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifi edTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFull yQualifiedTable Name
strExec = "EXEC dbo.uspDummyGet FullyQualifiedT ableName "
' GetStandardParm ams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardPara ms()
' 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.Ret urnsRecords = True
' Session.PassThr uQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenR ecordset(Sessio n.PassThruQryNa me)
GetFullyQualifi edTableName = 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.
On Sep 19, 11:21*pm, "bcap" <b...@nospam.no wherewrote:
Since you are using SQL Server linked tables, you have a choice of three:
dbOpenForwardOn ly: 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, dbOpenForwardOn ly 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...n o 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...t he 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.
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.OpenRecordse t(Session.PassT hruQryName, dbOpenDynaset,
dbSeeChanges)
If it does not need to be updatabale use:
Set rst = db.OpenRecordse t(Session.PassT hruQryName,
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
GetFullyQualifi edTableName).
The code from the GetFullyQualifi edTableName function is below:
*** code begins on next line ***
Dim rst As recordSet
Dim strExec As String
GetFullyQualifi edTableName = ""
' there is a stored proc on SQL Server named
uspDummyGetFull yQualifiedTable Name
strExec = "EXEC dbo.uspDummyGet FullyQualifiedT ableName "
' GetStandardParm ams returns a string, the contents of which are
the parameters that get sent
' to the stored proc
strExec = strExec & GetStandardPara ms()
' 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.Ret urnsRecords = True
' Session.PassThr uQryName is a string the contents of which is the
name of qdfReusable
Set rst = CurrentDB.OpenR ecordset(Sessio n.PassThruQryNa me)
GetFullyQualifi edTableName = 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.
Hi again,
You are correct, the advice is not affected, dbOpenForwardOn ly 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 "qdfreusabl e". There is
every possibility that your users are colliding over the use of this query
object.
<mi************ @yahoo.comwrote in message
news:b5******** *************** ***********@2g2 000hsn.googlegr oups.com...
On Sep 19, 11:21 pm, "bcap" <b...@nospam.no wherewrote:
Since you are using SQL Server linked tables, you have a choice of three:
dbOpenForwardOn ly: 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, dbOpenForwardOn ly 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...n o 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...t he 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.
On Sep 22, 11:56*am, "bcap" <b...@nospam.no wherewrote:
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 "qdfreusabl e". *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...specific ally, 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...i f 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.
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******** *************** ***********@26g 2000hsk.googleg roups.com...
On Sep 22, 11:56 am, "bcap" <b...@nospam.no wherewrote:
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 "qdfreusabl e". 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...specific ally, 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...i f 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.
On Sep 19, 2:35*pm, mirandacasc...@ yahoo.com wrote:
GetFullyQualifi edTableName).
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\SONYLAPT OP].[FFDBA_ESO].[sys].[objects]
AS so
INNER JOIN
[FFDBA-LAPTOP\SONYLAPT OP].[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' This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Bobbak |
last post by:
Hello All,
I could really use some help with this bit of code I am working on,
every time I come to execute it I get an error that says "Compile
Error: Argument not optional". Now I am using Access 2002 to run this
code that will allow me to populate a field in several tables at once.
Here is an example of the code.
Private Sub cmdOK_Click()
DoCmd.SetWarnings False
|
by: WindAndWaves |
last post by:
Dear All
Can you tell me why you use a class module???
Thank you
Nicolaas
---
|
by: Richard Hollenbeck |
last post by:
I have two functions (one using the other) where I want to pass into it the
character A, B, C, D, or F and have it return a Double
indicating the minimum score it takes to get that grade. For example,
here's a stipped-down version of the first function:
private function getComment()
Select Case txtTotal ' the value in the text box txtTotal in the report
Case Is >= minimumScore(B)
getComment = "Good work!"
end select.
|
by: Jake |
last post by:
I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a data mismatch
error. I realized UPC codes are 12 digits, sometimes more and
sometimes less. This falls out of the Long integer field type. I then
changed my type on all UPC fields in all of the tables containing this
field to a text data type. ...
|
by: Sunnyrain |
last post by:
I am developing a program in Access 2000. I couldn't make OpenRecordset
method work right.
It's working when I opened a simple SQL query below in OpenRecordset.
.....
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
| | |
by: jnikle |
last post by:
I have a parameter query named "qry_employee_info_reports" that I need
to run in the OnOpen event of a form. I'm after its total number of
records. The query's got several joins in it, and one of them is to
query "qry_last_transition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondDate" on a
pop up form.
What I've been trying to do up to this point is to open up this
parameter form,...
|
by: Harold Howe |
last post by:
I am running into a situation where the compiler complains that it
cannot infer the type parameters of a generic method when one of the
function arguments is an anonymous method.
Here is a complete code example:
//-----------------------------
using System;
using System.Collections.Generic;
|
by: tomlebold |
last post by:
Why do I receive a Type mismatch error when running the following
code, which has a SQL Server 2000 back end database?
The error occurs on the following line: Set rs = db.OpenRecordset(SQL,
dbOpenDynaset)
Dim db As DAO.Database
Dim rs As DA0.Recordset
Dim SQL As String
Dim CountRecs As Long
|
by: sheperson |
last post by:
Hi,
I have a stored procedure in my database and it has an argument of
type output (int).
When I create a dataset using VS2005, it creates a table adapter for
me and the Fill method has and argument of type
ref int?.
When I try to call the Fill method it prompts an error and says cannot
convert ref int to ref int?.
Does anyone know how should I call this Fill method?
Thanks in advance.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
