Migration from mdb to MS SQL Server

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I moved my tables to the MS SQL Server 2000 and created linked tables to the
SQL server via the DNS I created. Now when I attempt to edit a record via a
form I get a write conflict. Can anyone point me in a direction that I can
see what must be changed in my code to mae this work.

Before I moved it to the server, I split the data to a BE file to test with.
This all worked fine. I verified the permissions on the tables are fine on
the SQL server.
 
I had problems with any "Yes/No" fields. For some reason - if they were
"NULL" in SQL - they didn't allow me to write in them in Access. So I set any
NULL records to NO.

Tammy
 
That did it. THANKS!!!

Tammy F said:
I had problems with any "Yes/No" fields. For some reason - if they were
"NULL" in SQL - they didn't allow me to write in them in Access. So I set any
NULL records to NO.

Tammy
Click to expand...
 
To avoid the problem of Null BIT fields, set the column Required property to
Yes/True and set the Default value to 0 ....
 
Brent said:
To avoid the problem of Null BIT fields, set the column Required
property to Yes/True and set the Default value to 0 ....
Click to expand...

There can be other problems with bit fields used from Access due to the fact
that True in an Access yes/no is stored as negative one and a bit field
stores True as Postive one. I have seen filters and criteria get it wrong
because of this.

I ran into these issues way back with SQL Server 6.5 before they were even
allowed to contain Null.

I always use integer fields for this now on the SQL Server. You can bind
them to CheckBoxes and such just like bit fields and they have none of the
problems.
 
Thank you - I knew that was the problem - but didn't think to change the
field type as a solution. I did what Brent suggested - default to 0.
Tammy
 
Hey Rick ---

Yes the 1, 0 mixed with True / False can sometimes lead to "issues". The
technique you mention is a good one. What I do is just adopt a policy of
how I will set up my criteria. In other words, I know the most (if not all)
applications and languages define 0 to be False, and NOT 0 to be True. What
that does is open the door to the value True is assigned (Access: -1; SQL
Server 1; other apps ... but NOT 0). That adherance to the definition of
False is 0 and True is Not 0 is what allows us to create VBA like this:

If Len(Me.txtMyText) Then
'Do True Part
Else
'Do False Part
End If

So ... on to how I handle this possibility ... For virtually universal
compatibility I test for a False condition with =0 and a True condition with
<> 0
 
Brent said:
Hey Rick ---

Yes the 1, 0 mixed with True / False can sometimes lead to "issues". The
technique you mention is a good one. What I do is just adopt a
policy of how I will set up my criteria. In other words, I know the
most (if not all) applications and languages define 0 to be False,
and NOT 0 to be True. What that does is open the door to the value
True is assigned (Access: -1; SQL Server 1; other apps ... but NOT
0). That adherance to the definition of False is 0 and True is Not 0
is what allows us to create VBA like this:
If Len(Me.txtMyText) Then
'Do True Part
Else
'Do False Part
End If

So ... on to how I handle this possibility ... For virtually universal
compatibility I test for a False condition with =0 and a True
condition with <> 0
Click to expand...

Yes that is exactly what I do, but... You still have to put up with the fact
that many user-initiated actions cannot always be so controlled. Simple
menu choices like applying a filter on the current value or "excluding" the
current value don't work, filter by form doesn't work, etc.. Usign an
integer just makes all of that go away.
 
Back
Top