Migration from mdb to MS SQL Server

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

 

[Tammy F]

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

 

[Pat]

That did it. THANKS!!!

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

 

[Brent Spaulding \(datAdrenaline\)]

To avoid the problem of Null BIT fields, set the column Required property to
Yes/True and set the Default value to 0 ....

 

[Rick Brandt]

To avoid the problem of Null BIT fields, set the column Required
property to Yes/True and set the Default value to 0 ....

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.

 

[Tammy F]

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

 

[Brent Spaulding \(datAdrenaline\)]

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

 

[Rick Brandt]

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

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.