|
|
I have an Access 2003 database that is using ODBC tables linked to SQL Server
2005.
I am a novice at this, so please forgive me for my ignorance. I am trying to
update a record, in code, on a forms command button click event. Here's the
code:
strSql = "SELECT * FROM Request" _
& " WHERE RequestId=" & intRequestId _
& " and RequestVersion=" & intRequestVersion
Set rstRequest = mydb.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
With rstRequest
.Edit
!RequestStatus = "In Process"
.Update
End With
I get this error when it's running:
Runtime error '3157':
ODBC--update on a linked table 'Request' failed.
It errors out on the .Update line. If I click debug, I can step through the
code and it works fine. Do I need to put a timer in to slow it down? Any
ideas what's going on? There form open has a continuos form subform on it
that has a pass-thru query, as it's record source, and this record is
selected. I tried re-querying the form to select another record, but it still
didn't work.
I am just starting to convert record sources to Pass-thru queries. Any
advice would be appreciated. Thanks in advance.
|
|
If you intend to update some results that you have got from a passthrough
query under Access 2003, it's possible that you may have to use the NOLOCK
hint in your passthrough Select query. This is the kind of problem that
will go unnoticed when running under the debugger because the number of
opened connections against the server is not the same.
My advice: if you are a beginner, stick with ODBC linked tables for the
moment, until you have a better knowledge of both Access and SQL-Server;
otherwise, instead of going further, you will be going backward (ie., making
things worse instead of better).
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Crossh" <Crossh[ at ]discussions.microsoft.com> wrote in message
news:207F1FFE-8873-4572-8B21-F05CD204ADFF[ at ]microsoft.com...
[Quoted Text] >I have an Access 2003 database that is using ODBC tables linked to SQL
>Server
> 2005.
> I am a novice at this, so please forgive me for my ignorance. I am trying
> to
> update a record, in code, on a forms command button click event. Here's
> the
> code:
>
> strSql = "SELECT * FROM Request" _
> & " WHERE RequestId=" & intRequestId _
> & " and RequestVersion=" & intRequestVersion
> Set rstRequest = mydb.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
> With rstRequest
> .Edit
> !RequestStatus = "In Process"
> .Update
> End With
>
> I get this error when it's running:
>
> Runtime error '3157':
> ODBC--update on a linked table 'Request' failed.
>
> It errors out on the .Update line. If I click debug, I can step through
> the
> code and it works fine. Do I need to put a timer in to slow it down? Any
> ideas what's going on? There form open has a continuos form subform on it
> that has a pass-thru query, as it's record source, and this record is
> selected. I tried re-querying the form to select another record, but it
> still
> didn't work.
>
> I am just starting to convert record sources to Pass-thru queries. Any
> advice would be appreciated. Thanks in advance.
|
|
"Sylvain Lafontaine" wrote:
[Quoted Text] > If you intend to update some results that you have got from a passthrough
> query under Access 2003, it's possible that you may have to use the NOLOCK
> hint in your passthrough Select query. This is the kind of problem that
> will go unnoticed when running under the debugger because the number of
> opened connections against the server is not the same.
I thought I read that Pass-through queries were not updateable, why would
the record selected on the form be locked? Where can I find this NOLOCK hint?
I already started updating the database to use Pass-through queries, too late
to turn back.
|
|
If the select query returns multiples rows and you didn't read them all (for
example, you have just displayed the first 10 rows of a query that is
returning 100 rows); the SQL-Server will hold a sharing lock on all of the
rows of the query. Even if the passthrough query is read-only on the Access
side, this is something that SQL-Server doesn't care because it doesn't know
what really happening on the client side. If this is your case, then simply
add the WITH (NOLOCK) hint after each table in the FROM clause; for example:
Select * from table1 with (nolock) inner join table2 with (nolock) on
table1.id1 = table2.id1
This will tell SQL-Server to no hold any sharing lock on any table.
Finally, don't forget that when you are making any type of request against a
SQL-Server, the choice of the type of the query (passthrough or not) as well
as the choice of the recordset (client or server side, cached or not,
dynaset, keyset, etc.) and the number of rows can have an influence on the
SQL-Server that can affect both the locking process as well as the
performance. Most often - but now always - these things gone unnoticed;
until you are hit (hard) by a problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Crossh" <Crossh[ at ]discussions.microsoft.com> wrote in message
news:4AB41BDB-1057-4CD4-BD41-97BD5C701A9B[ at ]microsoft.com...
[Quoted Text] > "Sylvain Lafontaine" wrote:
>> If you intend to update some results that you have got from a passthrough
>> query under Access 2003, it's possible that you may have to use the
>> NOLOCK
>> hint in your passthrough Select query. This is the kind of problem that
>> will go unnoticed when running under the debugger because the number of
>> opened connections against the server is not the same.
>
> I thought I read that Pass-through queries were not updateable, why would
> the record selected on the form be locked? Where can I find this NOLOCK
> hint?
> I already started updating the database to use Pass-through queries, too
> late
> to turn back.
>
>
|
|
Thanks for your time and insight, the 'with (nolock)' worked.
Does it affect the performance with or without the lock? Do you need to
unlock all the tables (joined) in the recordset or only the one you need to
update?
Also, can you recommend a good book to learn the specifics of optimizing
Access after upsizing to SQL Server? (so I can stop bothering you)
"Sylvain Lafontaine" wrote:
[Quoted Text] > Select * from table1 with (nolock) inner join table2 with (nolock) on
> table1.id1 = table2.id1
|
|
I would say that only the table that need to be updated should have the hint
Nolock; however, it shouldn't be to hard for you to make a test and verify
this. In term of performance, SQL-Server always uses locks; so the overall
performance should remains practically unchanged. However, there is more to
this then simply what's hitting the eye.
For a good book, I would either recommand the Mary Chipman and Baron or the
Klander's books. However, in your case, any good book strictly about
SQL-Server should also be handy.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Crossh" <Crossh[ at ]discussions.microsoft.com> wrote in message
news:38694840-E1F5-4A7B-A176-84EB276B0ECD[ at ]microsoft.com...
[Quoted Text] > Thanks for your time and insight, the 'with (nolock)' worked.
>
> Does it affect the performance with or without the lock? Do you need to
> unlock all the tables (joined) in the recordset or only the one you need
> to
> update?
>
> Also, can you recommend a good book to learn the specifics of optimizing
> Access after upsizing to SQL Server? (so I can stop bothering you)
>
>
> "Sylvain Lafontaine" wrote:
>
>> Select * from table1 with (nolock) inner join table2 with (nolock) on
>> table1.id1 = table2.id1
>
|
|
|