473,715 Members | 2,207 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Migrating From Access 2000 To SQl Server 2000


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.

Jul 20 '05 #1
1 4977
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

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



= -1

and = FALSE


= 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)...tha t way a (-1) WILL BE STORED by the ODBC driver, and it'll be

Hope this helps!
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Serdar Yegulalp | last post by:
I'm in the process of migrating a lot of data (millions of rows, 4GB+ of data) from an older SQL Server 7.0 database to a new SQL Server 2000 machine. Time is not of the essence; my main concern during the migration is that when I copy in the new data, the new database isn't paralyzed by the amount of bulk copying being one. For this reason, I'm splitting the data into one-month chunks (the data's all timestamped and goes back about 3...
by: lcifers | last post by:
First off, sorry if my cross posting offends anyone. I'm posting this in Access and SQL Server groups - not sure which one is appropriate. I have a relatively simple ASP.NET/VB.NET application that is now hitting an Access 2000 database over an intranet. We have to migrate the database to SQL Server 7. My experience with ASP.NET is pretty limited and my experience with SQL Server is nonexistent. We have an MSDN subscription, so I went...
by: canigou9 (remove your socks to reply) | last post by:
(cross posted - comp.databases.ms-access, microsoft.public.access) Hello folks - this is my first post here, after a short lurk. I have written an application in Access2002 for a friend's business which allows him to take orders, arrange deliveries and trace deliveries back in the event of subsequent complaints. It uses a handful of tables in a relational schema, with a few ancillary lookup tables, some forms, reports and VBA modules.
by: bonnie.tangyn | last post by:
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application is MS Access 2000. The back-end is MS SQL Server 2000. After I migrated the database and
by: Shai Levi | last post by:
Hi, I'm trying to migrate native c++ class to managed c++ class. The native class header definition looks as: class NativeClass { public: typedef void (CbFunc1)(int n,void* p);
by: Neil | last post by:
We are running SQL 7, using Access 2000 as a front end. Our network person is wanting to migrate to Windows 2003 (we're currently on Windows 2000), and wants to know if we should migrate to SQL 2003 at the same time. Are there major changes between SQL 7 and SQL 2003, and how hard of a task would it be to migrate our single database to a new version of SQL? Thanks, Neil
by: ARC | last post by:
Hello, What is the best way to do this. I have many off-site locations with access 97 back-end databases. If I wish to go to access 2000, and don't want to manuall upgrade the back ends to access 2000, how do I automate this? If there isn't a way to do this through code, do I need to install to a different folder with a blank 2000 database, and manually import each table's data from the 97 database? Or is there an easier way? Many...
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access database runs on terminal services and supports approximatly 25-30 users. It is crapping out big time, corrupted data, changes to the front end are difficult for someone unfamiliar with the system (me), the table structure is bad...really bad....there is...
by: cathy25 | last post by:
Hi, we are planning on migrating our sql from version 2000 to 2005 (I know SQL 2005 is in market from a while). As a part of testing, i have installed sql server 2005 on my development box and able to restore all databases from the production. When it came to DTS packages, I have used SSMS to migrate them by going into Management --> Legacy --> Data Transformation Services --> RC and selected Migrate Wizard. I have gone through the wizard and...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.