We help IT Professionals succeed at work.

Trouble Converting Access DB to SQL 2008, Dealing with Unique Values and Nulls

JoeMiskey asked
Last Modified: 2012-02-09
I found a thread that discusses the situation I am having (https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20677681.html), I am just trying to now figure out the best workaround for our situation.

Basically, we have Access back-end db that we are trying to convert to SQL 2008.  In one of our tables, we have a non-primary key field that in Access has the following properties:
Required: No
Allow Zero Length: Yes
Indexed: Yes (No Duplicates)

So this field contains Null values for many records, but for the records with entries, all entries are unique (this is a system ID for a particular software package some of our clients are on -- if they are not on this software package, the field is Null).

The table containing this field is joined to another table via a query in a one-to-many type relationship (it is a table of payroll dates for each client on that software package).  This other table contains many date fields that the user needs to update.  So there is a Form based on this query that the user uses to update these values, and it works just fine.

The issue is in that trying to move the table to SQL 2008, you are not allowed to have any field be both Unique and Not Null (as explained in that other thread I referenced).  If we allow Nulls, we cannot make it Unique, and therefore it thinks we have a many-to-many relationship, making the query un-updateable.  So our update Form no longer works.

We were trying to figure out ways around this.  One idea we came up is to make the fields we want updated on the Form unbound, then have an AfterUpdate script to run some SQL code to update the table directly behind the scenes, then refresh the Form.  Seems like it will be a bit of work, so I want to make sure we exhaust any other simpler options before we decide to go that route.

Does anyone know of easier/better ways of doing this?

Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)


Solved own problem
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.