Trying to use linked SQL table instead of Access table and multi value field N/A (1 Viewer)

[joe789]

Hey Folks,

I really hope someone can offer some sort of guidance in this issue. I started to write a fairly elaborate Access database, and as I usually do, I make sure it is all how I want it before I then replace the Access based tables with SQL linked tables in order to store all the data on the server and be able to share it.

Well my heart skipped a beat, because a deal breaker with this program is the ability for a multi-value field, which works perfectly fine in Access tables but is not supported in SQL linked tables.

Is there any easy remedy of some sort, otherwise, I am in a world of hurt big time and I just literally wasted months of time and effort just simply because SQL linked tables multi-value support is not available.

Thanks for any assistance,

Joe

 

[Uncle Gizmo]

You might find the following links handy in explaining multi-value fields:-

Access2010 MultiValued ComboBox

How to: Manipulate Multivalued Fields With DAO

You may be able to glean some options from the code provided by Microsoft.

 

[SQL_Hell]

Hi,

Speaking from purely a SQL database perspective, multi selection objects represent a one too many relationship in the database and you cannot store multiple values in one field because it breaks normalisation and referential integrity.

So you must separate those multi values into another table in the database.

In a example of orders and parts, where part numbers are selected from a multi value list, then the database tables would look like this:

Orders:

OrderNo
OrderName
DeliveryDate
AddressID

(one to many relationship)

OrderParts:

OrderNo
PartNo

(many to one relationship)

Parts:

PartNo
PartName
Cost
Manufacturer

What you need to do is either in VBA or using a stored procedure loop through the recordset from the multi value object, inserting each value into the database one by one.

 

[Rx_]

I totally agree with SQL_Hell
In my world of Regulatory madness it sometimes involves that box thinking outside thing.
So at the risk of being clever, let me describe a project coming hot off the press. (The hot press of the old news papers is now replaced by the pressing of Hot keys, and why is my keyboard so fracking hot anyway?)

In land surveying, the US is measured in Sections, Townships and Ranges.
There are three more levels that are ignored for this description.
A property right is located at S3T012R065 or Section 3, Township 12 and Range 65. In addition, Townships have a N/S and Ranges have a E/W descriptor.
Some of you understand, the rest of you, look up President Jefferson's Louisiana Purchase and his contract to the British company Mason and Dixon.
My table for data entry has three fields with limits for Section, Township, and Range (or STR for short.
As SQL Hell would have it, I have three formulas that fill in the other DB fields and create data in the format for T012N-R065W-S01
This format sorts nicely if some one is needing to sort STR close together.
But, Jefferson also designated specific Section numbers to become the property of schools, county and other things that changes the need to include other numeric formats.
For those wondering why Lat/Long are not used, the STR is a Legal area that does include a Lat/Long centroid. I won't digress into that and its use.

Basically, this is used for a Zoning application for many things ranging from road Righ-of-way, surface rights, mineral rights, joined properties, Court Docket, and so on.
Here is a quick tutorial that should make the STR numbering system easy to understand:
http://nationalmap.gov/small_scale/a_plss.html

While this kind of table brakes all kind of RI, the indexing and sorting formula's work really well on highly formatted fields.

It would be interesting to see some samples of what your trying to accomplish!