[AccessD] Multi value field contents

Martin Reid mwp.reid at qub.ac.uk
Sat May 13 06:02:33 CDT 2006


Arthur
 
In Access 2007 you can have a field that displays multi values. Its a new property for Lookups Allow MultipleValues.
 
For example A Task has many developers associated with it. (Yeah I no but its an example) So in the Access window you can double click the field and Access opens a little floating window showing all the tasks. You select multiple tasks by clicking a check box. Works fine in Access. Under the covers the engine builds a fully relational model but you cant get at it yet via code.
 
When you upsize this to SQL Server or SQL Server 2005, the field is upsized as text and the multi values are in the field as
 
task1;task2;task3
 
Thus breaking relational design
 
What I wanted to do was to recreate the true relational design via code to correct this in SQL Server. 
 
Now the REAL MS reason for this is to enable you to hook this up with SharePoint which does deal with this properly. As it also works with attachment data types.
 
OH and there is good news re ADPs in SQL 2005 but I cant tell you HA 
 
I can send youa couple of scree shots of list if you would liek to see this effect.
 
Martin
 
 
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 

________________________________

From: accessd-bounces at databaseadvisors.com on behalf of Arthur Fuller
Sent: Sat 13/05/2006 10:55
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Multi value field contents



Assuming that every value has been hit at least once, what's wrong with "SELECT DISTINCT value INTO tableName"? Once you've got them, you can add an identity column, add an int column to the table, do a join and an update query to populate it, then drop the original column. Nothing to it. Or am I missing something?

Arthur

Martin Reid <mwp.reid at qub.ac.uk> wrote: I am sorting this myself but was wondering what you think about this and any non obvious solutionsyou may have?


I have a field containing value1; value2; value3

Breaks all notions of relational design.

The table is a table project associated with many developers. In the Project Task Name Field the values are the developer names associated with the projects.

Is it possible in code to

Break out the developer names and populate a table properly.

This is Access 2007 and SQL Server 2005. The data is in SQL Server 2005

You response may be reused but full credit will be given.


Martin

Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast

tel: 02890 974477


________________________________

From: accessd-bounces at databaseadvisors.com on behalf of MartyConnelly
Sent: Sat 06/05/2006 01:46
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Enumerate SQL Servers



You might have problems with SQLDMO on SQL 2005 as it has been
supplanted with SQLSMO
and you might have to use WMI, I haven't checked the differences.

Martin Reid wrote:

>Thanks James.
>
>Just did something similar for the actual database names which is step 2. Idea is combo 1 lists the servers, then combo 2 lists the databases on each server.
>
>
>
>Best Wishes
>
>
>Martin
>
>Martin WP Reid
>Training and Assessment Unit
>Riddle Hall
>Belfast
>
>tel: 02890 974477
>
>
>________________________________
>
>From: accessd-bounces at databaseadvisors.com on behalf of James Barash
>Sent: Fri 05/05/2006 20:36
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Enumerate SQL Servers
>
>
>
>Martin:
>
>Here is a example which should work in Access. You will need to reference
>the SQLDMO library to make it work.
>
>          Dim oApp As SQLDMO.Application
>        Dim oNames As SQLDMO.NameList
>        Dim oName As Variant
>
>        Set oApp = New SQLDMO.Application
>        Set oNames = oApp.ListAvailableSQLServers()
>        For Each oName In oNames
>            cboServers.AddItem oName
>        Next
>
>Hope it helps.
>
>James Barash
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Martin Reid
>Sent: Friday, May 05, 2006 3:05 PM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Enumerate SQL Servers
>
>
>Anyone got an example of populating a drop down list in Access with all
>available SQL Servers?
>I am building a  new login procedure.
>
>Martin
>
>
>
>Martin WP Reid
>Training and Assessment Unit
>Riddle Hall
>Belfast
>
>tel: 02890 974477
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>   
>
>
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006
>
>

--
Marty Connelly
Victoria, B.C.
Canada



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>  


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 




More information about the AccessD mailing list