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/>