472,127 Members | 2,047 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

Migrating From Access 2000 To SQl Server 2000

Hi,

I have developed an application using VB 6 (SP 5), MS Access 2000 and
Crystal Reports 9. I migrated from from access to SQl Server 2000.
This wasn't a problem as i could import all the tables to SQL Server.
Also, i could make VB6 talk to SQl Server.

The problem arsies when i run my application. The sql syntax for
access seems to be different than that for SQL Server. A simple
example being: In access boolean datatype is true/false ,whereas in
SQL Server the boolean equivalent is bit (numerical 1 or 0). These
kind of issues are causing problems and most queries don't run.

Would i need to go and change all the queries in accordance with SQl
Server syntax ,which would be very time consuming or is there any
function which will convert the access datatype into its equivalent
SQl Server datatype??

Any input/thoughts/suggestions would be appreciated.

Thanks
Jatin
Jul 20 '05 #1
1 4902
There is a big problem with ODBC JET Linked tables when it comes to boolean
fields in Access that get turned into BIT fields in SQL Server.

Basically, JET thinks of boolean TRUE as (-1) where SQL thinks of it as (1).
The ODBC driver switches the values for you in Access, so that when you look at
the table it shows (-1)...but if you look at the table in SQL Server it says
(1).

The bottom line is that the ODBC driver does NOT understand what TRUE or FALSE
means, so the short answer is YES, you have to change all of those refrences to
(-1) or (0) respectively.

The standard, quick answer to this problem is to use a find/replace tool (in
NOTEPAD) to change all

= TRUE

to

= -1

and = FALSE

to

= 0

WARNING: This is only true for JET-based ODBC queries. If you create a view on
SQL Server, you'll have to use = 1 for TRUE!!!!

For that reason, I normally use =0 for FALSE, and <>0 for TRUE!!! That way, it
always works.

Another option is to change the BIT datatype on SQL Server to INT
(integer)...that way a (-1) WILL BE STORED by the ODBC driver, and it'll be
consistent.

Hope this helps!
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by canigou9 (remove your socks to reply) | last post: by
6 posts views Thread by Shai Levi | last post: by
11 posts views Thread by Neil | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.