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 access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.
db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>
|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>
|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> I have a DB and some tables that I'm moving to Sql2005 machine that just
>> get
>> query access. People use ACCESS with ODBC link table.
>> What permissions other than datareader would allow them to only see USER
>> tables?
>>
>> Thanks.
>>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> >
> > db_datareader is enough.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "AHartman" wrote:
> >
> >> I have a DB and some tables that I'm moving to Sql2005 machine that just
> >> get
> >> query access. People use ACCESS with ODBC link table.
> >> What permissions other than datareader would allow them to only see USER
> >> tables?
> >>
> >>
> >> Thanks.
> >>
> >>
> >>
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> That's what I gave but when linking the tables the user saw lot of sys
>> tables also.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>> >
>> > db_datareader is enough.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "AHartman" wrote:
>> >
>> >> I have a DB and some tables that I'm moving to Sql2005 machine that
>> >> just
>> >> get
>> >> query access. People use ACCESS with ODBC link table.
>> >> What permissions other than datareader would allow them to only see
>> >> USER
>> >> tables?
>> >>
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>>

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just g
et
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>