Solved

Upsizing Access 2010 to SQL--- Compatibility with Check boxes and Option Groups

Posted on 2013-06-09
10
435 Views
Last Modified: 2013-06-12
Hi Experts,

When upsizing Access to SQL Express what considerations are needed (if any) to successfully migrate checkboxes (yes|no) and option groups?  Does the version of either make a difference?

Thank you!
0
Comment
Question by:fargus47
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Access and SQL Express are quite different.  The fact that they handle databases is about the only thing they have in common.  Access is a desktop Office program and SQL Express is a server.  Unlike Access, SQL Express does not have a desktop GUI interface.  It is a multi-user server application.  

Nothing that you see on your desktop with Access will be part of SQL Express.  You can get SSMS (SQL Server Management Studio) for a GUI interface but it is nothing like Access either.  This might help: http://blogs.office.com/b/microsoft-access/archive/2012/02/17/five-common-pitfalls--access-migration-to-sql-server.aspx
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
SQL Server doesn't have Boolean fields, so the upsizing wizard will move those to Bits. In most cases you're okay with this, but depending on your code style, you may find your checkboxes don't work, if you're using code to select/deselect based on database values.

Option groups in Access are typically bound to a numeric field, and the upsizing wizard will handle that as well. You shouldn't notice any difference.

there are, however, some bumps in the road. Two good resources are here:

http://www.fmsinc.com/tpapers/index.html#SQLPapers
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp - The Best of Both worlds

Those have some very good information about changes you may need to make. For example, the wildcard in SQL is %, whereas in Access it's *. In many cases this won't matter, but - again - depending on your code style you may need to change this.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
SQL Server doesn't have Boolean fields, so the upsizing wizard will move those to Bits.
I would argue that a bit field is a boolean data type.  However, I suspect you meant that an MS Access boolean (0, -1) is not the same as a SQL Server bit (0, 1) and there I agree with you.

One thing that can cause problems is the use of the float (or real) data type.  These data types (like single and double in MS Access) are approximate numeric data types and should never be used in any business application.

Dates are another source of problems.  If you are keeping MS Access as a front-end, stay with the older datetime (rather than then the newer date or datetime2) data type as it is compatible with MS Access.
0
 
LVL 84
Comment Utility
I would argue that a bit field is a boolean data type.
I'd agree with that, but as you stated it doesn't line up with the Access Boolean data type and often changes are needed.

Interesting about Float ... would you use Decimal instead to store values like 12345.6789?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Interesting about Float ... would you use Decimal instead to store values like 12345.6789?
Yes or money.

For what it is worth, years ago (mid 80's) my boss at the time decided for disk space reasons to use single (or double don't recall which) to represent a telephone number.  Needless to say he soon discovered his mistake when the phone number rounded to a different number!  In order, to avoid many problems I had with single and double in the late 80's writing accounting packages, I switched to the then new Currency data type even when money was not involved.  This had the advantage of being exact (internally it is just an integer offset by 10,000) and faster (important at the time).

So yes, to answer your question for any business app I recommend staying away from float or real (or  double or single in MS Access or VB).
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

In my experience it's a good idea to make sure the default is set to 0 on all SQL bit fields AND you update data accordingly.  Access allows nulls which is inconsistent with bit.

On more thing - make sure you create a timestamp field in the SQL table for any table that has a bit field.  This will make sure your records are updateable.

Regards,

Bill
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Access allows nulls which is inconsistent with bit.
Are you saying that SQL Server does not support the use of NULL with a bit data type?  If so you may want to double check that.
0
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

SQL does support nulls but it is inconsistent with the definition of Bit which is 0 or 1.

The fact that nulls may exist is usually problematic as queries fail unexpectedly.  In my opinion nulls should and can be avoided by changing the data so that it is a 1 or 0 on upsize,  force a default to 0 and disallow nulls in the SQL table.

Regards,

Bill
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
SQL does support nulls but it is inconsistent with the definition of Bit which is 0 or 1.
Not to quibble, but that is not what SQL Server's BOL has to say on the subject:
bit (Transact-SQL)
An integer data type that can take a value of 1, 0, or NULL.
It does not get much clearer than that.  As with all data types there is a valid reason for using NULLs.
0
 

Author Closing Comment

by:fargus47
Comment Utility
Thank you everyone for your thoughts and time!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now