troubleshooting Question

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

Avatar of JoeMiskey
JoeMiskeyFlag for United States of America asked on
Microsoft SQL Server
2 Comments1 Solution162 ViewsLast Modified:
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?

Thanks
ASKER CERTIFIED SOLUTION
JoeMiskey

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros