using Access with SQL Server??

J

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
 
K

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.
 
P

Pieter Wijnen

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

Pieter
 
J

Jerome

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

Thanks a lot mate :)

Jerome
 
J

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 :/
 
P

Pieter Wijnen

On the principle I wouldn't disagree, especially as Oracle would give you
the *real* reason for any failure (if you present Errors(0) to the user &
remember to strip the ODBC stuff)
but I (beeing who I am), don't like comments like that (especially in
newsgroups where tact is essential) - there are to many idiots around
posting nonsesnse (ie "empty" posts - nothing to do with you! or this
particular thread)
in short
allthough I prefer Oracle as the backend; I won't ever rebuff people for
choosing accees,sqlserver,mysql or other as their backend, the main issue
being to they get "their dollars worth" qua realibility etc.
I am offcourse aware of the fact that most people either think that Oracle
is more expensive (not true!) or reqiures more knowledge (not true, but I
leave that 'till somebody challenges me on the subject) than MS SQL Server

Pieter
still Oracle Certified DBA (but then again..)
 
D

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)
 
Top