ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
Crystal
You should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
CrystalYou should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC - FMTONLY

Hi There

I need to know if the "perform translation for character data" odbc connection option is responsible for SET FMTONLY statements, i think it is but i cannot find 100% confirmation from knowledge base articles or various searches.

Secondly if this option is unchecked will it stop FMTONLY statements completely.

Thirdly if it is enabled, what is responsible for FMTONLY statements not having a where clause ? the odbc driver or the application using the odbc connection, i am sure it is the application but once again , i need confirmation.

Thank YouNo, the two are seperate. 'SET FMTONLY' is used when the driver needs metadata before a statement has been executed. The driver turns FMTONLY OFF as soon as it has the metadata. You can check what's happening with SQL Profiler. The driver uses FMTONLY internally inresponse to some sequences of ODBC calls. An application could also execute SET FMTONLY statements itself. An ODBC trace would show if the application is doing this.|||Hi Chris

That is my issue.Millions of set fmtonly statements are being generated.
So this is directly application related?
My issue is that no FMTONLY statements have where clauses, is this also application responsible?

I have issues where sometimes a FMTONLY statements that normally takes 0 Duration takes 60-90 seconds, if you know about this please check out the TSQL forum for my question.

Do you have a good link or article about ODBC and specifically FTMONLY?

Thank You|||The following looks as though it may be related to your problem

http://support.microsoft.com/kb/836830/

FMTONLY is used by ODBC and OLE DB to get metadata for a query qithout actually executing the query. Sometimes the query would get executed and this would explain the long execution times you are seeing.

What versions of software are you using?

ODBC - FMTONLY

Hi There

I need to know if the "perform translation for character data" odbc connection option is responsible for SET FMTONLY statements, i think it is but i cannot find 100% confirmation from knowledge base articles or various searches.

Secondly if this option is unchecked will it stop FMTONLY statements completely.

Thirdly if it is enabled, what is responsible for FMTONLY statements not having a where clause ? the odbc driver or the application using the odbc connection, i am sure it is the application but once again , i need confirmation.

Thank YouNo, the two are seperate. 'SET FMTONLY' is used when the driver needs metadata before a statement has been executed. The driver turns FMTONLY OFF as soon as it has the metadata. You can check what's happening with SQL Profiler. The driver uses FMTONLY internally inresponse to some sequences of ODBC calls. An application could also execute SET FMTONLY statements itself. An ODBC trace would show if the application is doing this.|||Hi Chris

That is my issue.Millions of set fmtonly statements are being generated.
So this is directly application related?
My issue is that no FMTONLY statements have where clauses, is this also application responsible?

I have issues where sometimes a FMTONLY statements that normally takes 0 Duration takes 60-90 seconds, if you know about this please check out the TSQL forum for my question.

Do you have a good link or article about ODBC and specifically FTMONLY?

Thank You|||The following looks as though it may be related to your problem

http://support.microsoft.com/kb/836830/

FMTONLY is used by ODBC and OLE DB to get metadata for a query qithout actually executing the query. Sometimes the query would get executed and this would explain the long execution times you are seeing.

What versions of software are you using?