July 15, 2011 at 9:40 am
We utilized Microsoft's upsizing wizard to convert an Access DB (2007) to a SQL Server DB (2005)
Havin a problem rendering a DROP DOWN COMBO list which was working in Access.
MS Access column was defined as Text datatype and contained multiple values seperated by semi-colon. EG:
New York;New Jersey;Los Angeles;Clearwater etc....
SQL Server 2005 column is defined as varchar(8000) null
any thoughts on how to resolve this?
July 15, 2011 at 11:35 am
Sorry but do not understand the problem, do you mean to say that the CONTENTS of the SQL column are NULL for all rows in that table, or that the SQL column is defined as ALLOW NULLS and does not contain data in that column in any row of the table? Can you please elaborate.
July 15, 2011 at 11:51 am
What are you doing the drop down combo in?
July 15, 2011 at 11:55 am
thx for your reply Ron.. I'm a SQL Server DBA w/ little knowledge of MS-Access (forms, macros, commands, reports, etc) I was tapped to assist w/ this issue.. One of the developers ran the Upsizing wizard to port the MS-Access 2007 db into SQL Server 2005.. here is the DDL for the table (note the column named AREA):
CREATE TABLE [dbo].[USERS](
[USERS] [nvarchar](255) NOT NULL,
[FNAME] [nvarchar](255) NULL,
[LNAME] [nvarchar](255) NULL,
[b]AREA[/b] [varchar](8000) NULL,
[MANAGER] [nvarchar](255) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT yaddy ya
the SQL Server 2005 table contains 5 rows of data --- each row of data includes the column name "AREA" varchar(8000).. Here is the contents of AREA for each of the 5 rows:
row1 :CHERRY HILL;MONMOUTH;PHILLY SUBURBS SOUTH;REGION;UNION;VINELAND
row2: CHERRY HILL
row3: CHERRY HILL;MONMOUTH;NEW CASTLE;
row4: MON
row5: CHERRY HILL;MONMOUTH;NEW CASTLE;PATRIOT;PHILLY CITY;VINELAND
According to the developer, the Access application would obtain a row of data (eg. row #1 above) and populate a drop down combo box w/ the semi-colon deliimited values found in that rows AREA column like this:
CHERRY HILL
MONMOUTH
PHILLY SUBURBS SOUTH
REGION
UNION
VINELAND
After upsizing the Access DB to SQL Sever, and connecting to SQL Server, the Access app no longer populates the drop down combo box correctly... thoughts?
July 15, 2011 at 5:21 pm
It has been too many years since I last touched ACCESS, but if memory servers me correctly isn't the text data type (ACCESS), limited to 255 characters.
My only suggestion would be to attempt to fill the combo box is to trim the length of data returned to the ACCESS application, or to modify the SQL db definition for that AREA column to 255..
July 15, 2011 at 5:56 pm
If the Access developer used a multi-value field for a row-source for a combo box, that's extremely unusual, and would suggest the developer was using a database in the .accdb format, as .mdb format databases don't allow that. More likely however, the data for the combo box was actually stored as a text string in the design of the combo box, where semicolon delimiters are used in that fashion. That of course is generally a bad idea because you have to change the form design if you want to add a selection. My suggestion would be to create a table with a single column that has the list of choices, as presumably they store the actual text selected in the field in whatever table the combo box uses as a data-source. The single column SQL table would become the row source for the combo, and would store only one selection per row. If the Access developer is using a .mdb or .accdb front-end then link to the table using ODBC. If he is trying to use a .adp or .accdp format front-end, then the connection to the SQL Server for the database should make that table available (Microsoft are no longer encouraging the use of that format however). Hope this helps - if you want more details on the steps involved, please post back.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 20, 2011 at 2:16 pm
I think the developer used MULTIVALUED field in Access 2007. That data type is not supported by SQL Server.
Quote from Microsoft:
"Multivalued fields for complex data
You can create a field that holds multiple values, also called complex data. Suppose that you need to assign a task to one of your employees or contractors, but you want to assign it to more than one person. In most database management systems and in earlier versions of Access, you needed to create a many-to-many relationship in order to do this correctly. In Office Access 2007, the hard part is done for you, when you choose a field that you set to accept multiple values. Multivalued fields are especially appropriate when you use Office Access 2007 to work with a SharePoint list that contains one of the multivalued field types used in Windows SharePoint Services. Office Access 2007 is compatible with these data types."
Link explaining more: http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx
Charlie Mather
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply