using Access with SQL Server??

[Jerome]

Hi there,

I've created a DB on Microsoft SQL server and I'm now defining the
front-end in Microsoft Access 2000.

The tables are linked and the data already in the DB gets displayed
without problems but if I want to make changes or edits I get the
following error: "This record has been changed by another user since you
started editing it" and I can't save then! But I'm the only one editing
it right now!!!

What happened? Or where can I find some more info regarding the Access
<> SQL Server connection? Do I need to create special users or something
like that? Or is there something on the ODBC level??

Anyhelp is greatly appreciated,

Jerome

 

[Kevin3NF]

Check out the target table, and look for any fields defined as a Bit
datatype. Make sure there is a default.

Make sure every target table has a primary key, and that it is included in
the query underneath the form

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Pieter Wijnen]

also, if you're using recordsets add the option dbseechanges
ie
db.openrecordset("...",dbopendynaset,dbseechanges)

Pieter

 

[Jerome]

Hi, it was indeed because the bit field didn't have a default!

Thanks a lot mate

Jerome

 

[Jerome]

hmm, no ... It worked once to edit and save a record, but I tried
another one now and the original error message pops up again :/

 

[Kevin3NF]

Did you update all of the existing records' bit fields to some value as
well? Forgot to mention that...

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[david epsom dot com dot au]

SQL Server bit fields are 3 state by default: Yes No Null
Jet 4 does not support 3 state bit fields.

When you connect Access to SQL Server, all bit fields with
value Null are translated as "false". When Jet compares
the "false" value to the original "Null" value, it reaches
the conclusion that someone else must have changed the data.

You can change all SQL Server bit fields to integer fields,
or change them so that they cannot or do not have null
values.

(david)