locked
Migrate Access "Yes/No" column to SQL Server 2008 "bit not null" RRS feed

  • Question

  • I'm using SSMA for Access 4.2 to migrate to SQL 2008. The Access db has "Yes/No" column with default value 0. But this column is migrated to SQL server as "allow null"

      [columnName] bit DEFAULT 0 NULL

    Shouldn't it be "NOT NULL" since I think Access DB doesn't support null value in Yes/No column? Is there a global setting to make it generate "NOT NULL" in SQL Server?

    Thanks,
    Heng-yi

    Monday, October 11, 2010 12:18 AM

Answers

  • Hi Heng-yi Liu,

     

    Based on my test, it seems a default behavior to make this column nullable.

    In this situation, I would like to recommend you to adujst it manualy instead of using the Migration Wizard. Please see the steps below:

    1.       Start Microsoft SQL Server Migration Assistant 2008 for Access

    2.       If Migration Wizard is shown, please click “Close” to shut this window

    3.       Click “File | New Project…” to start a new project
    meanwhile if there is an existing project, please select “Open Project…” to open it

    4.       Click “File | Add Databases” to add a Access Database
    if you use the existing project, please ignore this step

    5.       Click “File | Connect to SQL Server” to add a destination
    if you use the existing project, please ignore this step

    6.       In Access Metadata Explorer window, please expand “Access-metadata | Databases | <access database name> | Tables” and click the table which you want to modify

    7.       Then you will see two new tables which show the structures of the table in Access and SQL Server

    8.       In the below table, there is a column named Nullable, please uncheck the checkbox under Nullable column for specified record, then please click other column or record and click “Apply” button

    9.       In the below table, please right-click anywhere and choose “Synchronize with Database”

    10.   After this step, please click “Convert, Load and Migrate” button in the tool bar

    11.   Complete the migration

     

    If you don't want to import the data again, we could alter this table to remove the Nullable property with the following statement:

    ALTER TABLE <table name> ALTER COLUMN <column name> BIT NOT NULL

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Heng-yi Liu Thursday, October 14, 2010 12:14 AM
    Tuesday, October 12, 2010 8:16 AM

All replies

  • Hi Heng-yi Liu,

     

    Based on my test, it seems a default behavior to make this column nullable.

    In this situation, I would like to recommend you to adujst it manualy instead of using the Migration Wizard. Please see the steps below:

    1.       Start Microsoft SQL Server Migration Assistant 2008 for Access

    2.       If Migration Wizard is shown, please click “Close” to shut this window

    3.       Click “File | New Project…” to start a new project
    meanwhile if there is an existing project, please select “Open Project…” to open it

    4.       Click “File | Add Databases” to add a Access Database
    if you use the existing project, please ignore this step

    5.       Click “File | Connect to SQL Server” to add a destination
    if you use the existing project, please ignore this step

    6.       In Access Metadata Explorer window, please expand “Access-metadata | Databases | <access database name> | Tables” and click the table which you want to modify

    7.       Then you will see two new tables which show the structures of the table in Access and SQL Server

    8.       In the below table, there is a column named Nullable, please uncheck the checkbox under Nullable column for specified record, then please click other column or record and click “Apply” button

    9.       In the below table, please right-click anywhere and choose “Synchronize with Database”

    10.   After this step, please click “Convert, Load and Migrate” button in the tool bar

    11.   Complete the migration

     

    If you don't want to import the data again, we could alter this table to remove the Nullable property with the following statement:

    ALTER TABLE <table name> ALTER COLUMN <column name> BIT NOT NULL

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Heng-yi Liu Thursday, October 14, 2010 12:14 AM
    Tuesday, October 12, 2010 8:16 AM
  • Hello,

     

    I have an access database/application that is very critical for business. Since the database is growing in size, we are planning on migrating the database to MS SQL Server 2008. Our application is so big we can't think of making changes to the code since it will take a very long time. To begin with, our plan is to migrate the database as is so that we can hookup the access application to SQL server. Here is what our plan is...

     

    1. Migrate data to SQL Server 2008 as is (no changes to the table structures).

     

    2. Create linked tables in access pointing to the new SQL Server 2008 database.

     

    3. Change the connection string from access application to connect to the new access database with linked tables (pointing to SQL Server 2008 database)

     

    We expect the application to work for most part. However, we may be running into tricky situation handling the Yes/No datatype in Access. It stores -1 for True and 0 for false. If we directly migrate to SQL Server, it will be migrated to a BIT datatype with 1 for true and 0 for false. But in the VBA code, at places it looks for TRUE/FALSE (expecting -1/0 resepctively) and other places it is hard coded to look for -1 and 0, again for TRUE and FALSE respectively.

     

    If I create a new user-defined data type in SQL Server as VARCHAR (2) or CHAR (2) and migrate the data as is (all trues as -1s and falses a 0s from access), my VBA code might not like it and may complain that there is mismatch in the data type.

     

    I am looking for your expert advise to show me a path that involves no changes to VBA code and make it work against the new SQL Server 2008 database.

     

    I appreciate your advise.

     

    - Helios

     

    Wednesday, October 19, 2011 7:04 PM