Haslett, Andrew
andrew.haslett at ilc.gov.au
Wed Feb 11 04:24:13 CST 2004
** also posted to SQL Server list **
There was an Access 2000 SQL Server 'Readiness Update' in early version of
Office 2k. I believe it was included in a later service pack.
This may or may not fix the issue, however it definitely fixed issues we had
when upgrading an Access MDB to SQL Server.
Check out Office Update for more info.
Cheers,
Andrew
-----Original Message-----
From: James Barash [mailto:James at fcidms.com]
Sent: Wednesday, 11 February 2004 12:18 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] [dba-SQLServer]X-Posted: Problem Converting Bit
Fields From SQL Server 7.0 to SQL Server 2000
Yes, SQL 2000 uses 1 for true and 0 for false. In general, 0 is false and
anything non 0 is true. I believe, in Access, you can test for True and it
will find 1 or -1 or anything but 0 and Null. I usually just test for <> 0
since that seems to work everywhere.
Another thing to watch out for is bound forms that have bit fields.
Because of the difference in how Access and SQL Server handle bit fields it
can cause errors if you have bit fields that are Nullable. You must have a
default value or Access will raise an error whenever you edit a record.
James Barash
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawhon, Alan C
Contractor/Morgan Research
Sent: Monday, February 09, 2004 5:31 PM
To: 'accessd at databaseadvisors.com'
Cc: dba-sqlserver at databaseadvisors.com
Subject: [AccessD] [dba-SQLServer]X-Posted: Problem Converting Bit Fields
>From SQL Server 7.0 to SQL Server 2000
We upgraded our SQL Server 7.0 backend to SQL Server 2000 over the weekend.
The front end runs Access 2000 GUI client applications.
After the conversion, we are experiencing a problem with Access queries that
select data from the [converted] back end SQL Server tables. The problem
involves a hardcoded "-1" (i.e. "True" logic condition criteria) in the
Access queries testing against a "Bit" data type field in the SQL Server
back end.
With the SQL Server 7.0 backend tables, the Access queries were working just
fine. In converting to SQL Server 2000, it appears that previously
populated bit fields populated with the value of "-1" ("True") got
arbitrarily switched to a value of "1" during the conversion. (The "-1"
values in the back end table ["Bit" fields] got changed, NOT the "-1"
criteria value in the front end query
cells!) Further, when the query runs against the [converted] back end
table, the "-1" criteria (from the Access query) is no longer recognized -
the query doesn't return data.
Needless to say, we are no longer getting the expected results (from
execution of the queries) after the conversion from SQL Server 7.0 to SQL
Server 2000. I suspect that Microsoft implemented a change in the behaviour
of "Bit"
fields
from version 7.0 to SQL Server 2000. While I search the Knowledge Base and
try to track this down, can anybody confirm whether this is the case?
We have a lot of queries (and VBA code) that test the "-1" value against
fields of data type "Bit", so we don't want to start changing these
hardcoded criteria (from "-1" to just plain "1") until and unless we are
ABSOLUTELY SURE that this is a SQL Server version 7.0 to version 2000
conversion anomaly.
TIA for any info or enlightenment on this problem.
Alan C. Lawhon
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.