Migration from mdb to MS SQL Server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat - 28 May 2008 20:11 GMT

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.

Reply to this Message

Tammy F - 28 May 2008 20:57 GMT

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

> 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
[quoted text clipped - 4 lines]
> This all worked fine. I verified the permissions on the tables are fine on
> the SQL server.

Reply to this Message

Pat - 28 May 2008 21:33 GMT

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
[quoted text clipped - 10 lines]
> > This all worked fine. I verified the permissions on the tables are fine on
> > the SQL server.

Reply to this Message

Brent Spaulding (datAdrenaline) - 29 May 2008 07:22 GMT

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

Signature

Brent Spaulding | datAdrenaline | Access MVP

>I moved my tables to the MS SQL Server 2000 and created linked tables to
>the
[quoted text clipped - 9 lines]
> on
> the SQL server.

Reply to this Message

Rick Brandt - 29 May 2008 12:10 GMT

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

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Tammy F - 29 May 2008 14:09 GMT

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

> > To avoid the problem of Null BIT fields, set the column Required
> > property to Yes/True and set the Default value to 0 ....
[quoted text clipped - 10 lines]
> them to CheckBoxes and such just like bit fields and they have none of the
> problems.

Reply to this Message

Brent Spaulding (datAdrenaline) - 30 May 2008 06:15 GMT

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

Signature

Brent Spaulding | datAdrenaline | Access MVP

>> To avoid the problem of Null BIT fields, set the column Required
>> property to Yes/True and set the Default value to 0 ....
[quoted text clipped - 10 lines]
> them to CheckBoxes and such just like bit fields and they have none of the
> problems.

Reply to this Message

Rick Brandt - 30 May 2008 12:23 GMT

> Hey Rick ---
>
[quoted text clipped - 15 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message