Very Computer

I'm fairly new to Access 2002 and to SQL Server 2000.  I've created
tables, forms, reports, etc using an Access .ADP file connected to a
development SQL Server 2000.  Everything works fine.  Recently I've
been trying to copy my development SQL Server database to a production
SQL Server (also SQL Server 2000).  Everything to do with the copy
process works but...

...in Access, when I change the connection information in the ADP file
to connect to the production server and I double-click on a table in
the project window to open it, I get a message "The stored procedure
executed successfully but did not return records" and the table
doesn't open.  What is strange is that I CAN open some tables but not
others.  Also, all my forms still work, even when they are referencing
tables I can't open from the project window.

All the tables are owned by 'dbo' and the user I'm logging in as has
all the correct permissions to the tables.  Even if I log in as 'dbo'
I get the same problem.  If I connect back to my development server
everything is fine.

I've tried copying my database to the production server using the DTS
Export wizard and also using the "Transfer Database" facility in
Access 2002.  I've checked all the users, logins, permissions, etc,
and can't see anything that is wrong.

Has anyone come across this before?  Does anyone know how to get round
this?  Am I doing something really stupid?  Please put me out of my
misery!

I'm posting this just in case it will be of use to others.

Pawel

Hi Pawel,

Hmmmm...      I've never seen this error while just opening a table.    I am curious as to what extended
property caused it.     If you have time, I would like to see a script to recreate the whole table (with extended
properties) so I can try to reproduce it.

Thanks,

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------

| Newsgroups: microsoft.public.access.adp.sqlserver
| Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| Date: 12 Jul 2002 04:33:00 -0700

| I've found the answer!  When I use the DTS Export wizard and choose to
| copy "extended properties" I get the problem.  When I don't copy
| "extended properties", it all works fine.  My guess is that the tables
| I **could** open were those without any extended properties.
|
| I'm posting this just in case it will be of use to others.
|
| Pawel
|

| > Hi,
| >
| > I'm fairly new to Access 2002 and to SQL Server 2000.  I've created
| > tables, forms, reports, etc using an Access .ADP file connected to a
| > development SQL Server 2000.  Everything works fine.  Recently I've
| > been trying to copy my development SQL Server database to a production
| > SQL Server (also SQL Server 2000).  Everything to do with the copy
| > process works but...
| >
| > ...in Access, when I change the connection information in the ADP file
| > to connect to the production server and I double-click on a table in
| > the project window to open it, I get a message "The stored procedure
| > executed successfully but did not return records" and the table
| > doesn't open.  What is strange is that I CAN open some tables but not
| > others.  Also, all my forms still work, even when they are referencing
| > tables I can't open from the project window.
| >
| > All the tables are owned by 'dbo' and the user I'm logging in as has
| > all the correct permissions to the tables.  Even if I log in as 'dbo'
| > I get the same problem.  If I connect back to my development server
| > everything is fine.
| >
| > I've tried copying my database to the production server using the DTS
| > Export wizard and also using the "Transfer Database" facility in
| > Access 2002.  I've checked all the users, logins, permissions, etc,
| > and can't see anything that is wrong.
| >
| > Has anyone come across this before?  Does anyone know how to get round
| > this?  Am I doing something really stupid?  Please put me out of my
| > misery!
|

- column (field) description (available from Access' table design)
- column widths (adjusting the column width in Access' datasheet view)

...and I set both of these from within Access.

Hope this helps,
Pawel

- column (field) description (available from Access' table design)
- column widths (adjusting the column width in Access' datasheet view)

...and I set both of these from within Access.

Hope this helps,
Pawel

Quote:>> Hmmmm...      I've never seen this error while just opening a
table.  I am
>> curious as to what extended property caused it.     If you have
time, I
>> would like to see a script to recreate the whole table (with
extended
>> properties) so I can try to reproduce it.

Pawel

Hi Pawel,

You can create a script for an existing database object using Query Analyzer.    This is what I need to
reproduce the table.    The property causing the problem may be one you don't realize is there, or may only
cause the problem if the field is a certain datatype, etc.

Thanks,

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------

| Newsgroups: microsoft.public.access.adp.sqlserver
| Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| Date: 15 Jul 2002 06:38:27 -0700

| Hi Brian,
| Thanks for taking an interest in this. I didn't write any scripts to
| create the tables - I created them from within Access 2002.  As far as
| I'm aware, the only extended properties which I've used are...
|
| - column (field) description (available from Access' table design)
| - column widths (adjusting the column width in Access' datasheet view)
|
| ...and I set both of these from within Access.
|
| Hope this helps,
| Pawel
|
|

| > Hi Pawel,
| >
| > Hmmmm...      I've never seen this error while just opening a table.    I am curious as to what extended
| > property caused it.     If you have time, I would like to see a script to recreate the whole table (with
extended
| > properties) so I can try to reproduce it.
| >
| > Thanks,
| >
| > Brian M. Sockey
| > Microsoft Access Developer Support
| >
| > ---------------------------------------
| > This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your
use.
| > ? 2001 Microsoft Corporation. All rights reserved.
| >
| >
| > --------------------

| > | Newsgroups: microsoft.public.access.adp.sqlserver
| > | Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| > | Date: 12 Jul 2002 04:33:00 -0700
| >  
| > | I've found the answer!  When I use the DTS Export wizard and choose to
| > | copy "extended properties" I get the problem.  When I don't copy
| > | "extended properties", it all works fine.  My guess is that the tables
| > | I **could** open were those without any extended properties.
| > |
| > | I'm posting this just in case it will be of use to others.
| > |
| > | Pawel
| > |


| > | > Hi,
| > | >
| > | > I'm fairly new to Access 2002 and to SQL Server 2000.  I've created
| > | > tables, forms, reports, etc using an Access .ADP file connected to a
| > | > development SQL Server 2000.  Everything works fine.  Recently I've
| > | > been trying to copy my development SQL Server database to a production
| > | > SQL Server (also SQL Server 2000).  Everything to do with the copy
| > | > process works but...
| > | >
| > | > ...in Access, when I change the connection information in the ADP file
| > | > to connect to the production server and I double-click on a table in
| > | > the project window to open it, I get a message "The stored procedure
| > | > executed successfully but did not return records" and the table
| > | > doesn't open.  What is strange is that I CAN open some tables but not
| > | > others.  Also, all my forms still work, even when they are referencing
| > | > tables I can't open from the project window.
| > | >
| > | > All the tables are owned by 'dbo' and the user I'm logging in as has
| > | > all the correct permissions to the tables.  Even if I log in as 'dbo'
| > | > I get the same problem.  If I connect back to my development server
| > | > everything is fine.
| > | >
| > | > I've tried copying my database to the production server using the DTS
| > | > Export wizard and also using the "Transfer Database" facility in
| > | > Access 2002.  I've checked all the users, logins, permissions, etc,
| > | > and can't see anything that is wrong.
| > | >
| > | > Has anyone come across this before?  Does anyone know how to get round
| > | > this?  Am I doing something really stupid?  Please put me out of my
| > | > misery!
| > |
|

If you find a solution please post it here as I have the same problem.
Several tables were copied using DTS and now I can't open them through the
user interface in Access. There are literally hundreds of user-defined
properties on these tables.

I considered directly deleting from the sysproperties table but I would
prefer not to take this drastic step.

Thanks,

Peter

Quote:

> Hi Pawel,

> You can create a script for an existing database object using Query

Quote:> reproduce the table.    The property causing the problem may be one you

Quote:> cause the problem if the field is a certain datatype, etc.

> Thanks,

> Brian M. Sockey
> Microsoft Access Developer Support

> ---------------------------------------
> This posting is provided "AS IS" with no warranties, and confers no

Quote:> | > property caused it.     If you have time, I would like to see a script

Quote:> extended
> | > properties) so I can try to reproduce it.
> | >
> | > Thanks,
> | >
> | > Brian M. Sockey
> | > Microsoft Access Developer Support
> | >
> | > ---------------------------------------
> | > This posting is provided "AS IS" with no warranties, and confers no

Hi Peter,

I don't have an answer at this point because I don't know how to duplicate the problem.   If you could post a
SQL script to recreate a table that demonstrates the problem , I will take a look at it.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------




| Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| Date: Tue, 23 Jul 2002 14:30:08 +1200

| Hi,
|
| If you find a solution please post it here as I have the same problem.
| Several tables were copied using DTS and now I can't open them through the
| user interface in Access. There are literally hundreds of user-defined
| properties on these tables.
|
| I considered directly deleting from the sysproperties table but I would
| prefer not to take this drastic step.
|
| Thanks,
|
| Peter
|


| >
| > Hi Pawel,
| >
| > You can create a script for an existing database object using Query
| Analyzer.    This is what I need to
| > reproduce the table.    The property causing the problem may be one you
| don't realize is there, or may only
| > cause the problem if the field is a certain datatype, etc.
| >
| > Thanks,
| >
| > Brian M. Sockey
| > Microsoft Access Developer Support
| >
| > ---------------------------------------
| > This posting is provided "AS IS" with no warranties, and confers no
| rights. You assume all risk for your use.
| > ? 2001 Microsoft Corporation. All rights reserved.
| >
| >
| > --------------------

| > | Newsgroups: microsoft.public.access.adp.sqlserver
| > | Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| > | Date: 15 Jul 2002 06:38:27 -0700
| >
| > | Hi Brian,
| > | Thanks for taking an interest in this. I didn't write any scripts to
| > | create the tables - I created them from within Access 2002.  As far as
| > | I'm aware, the only extended properties which I've used are...
| > |
| > | - column (field) description (available from Access' table design)
| > | - column widths (adjusting the column width in Access' datasheet view)
| > |
| > | ...and I set both of these from within Access.
| > |
| > | Hope this helps,
| > | Pawel
| > |
| > |



| > | > Hi Pawel,
| > | >
| > | > Hmmmm...      I've never seen this error while just opening a table.
| I am curious as to what extended
| > | > property caused it.     If you have time, I would like to see a script
| to recreate the whole table (with
| > extended
| > | > properties) so I can try to reproduce it.
| > | >
| > | > Thanks,
| > | >
| > | > Brian M. Sockey
| > | > Microsoft Access Developer Support
| > | >
| > | > ---------------------------------------
| > | > This posting is provided "AS IS" with no warranties, and confers no
| rights. You assume all risk for your
| > use.
| > | > ? 2001 Microsoft Corporation. All rights reserved.
| > | >
| > | >
| > | > --------------------

| > | > | Newsgroups: microsoft.public.access.adp.sqlserver
| > | > | Subject: Re: Cannot open tables using Access 2002 adp to SQL Server
| 2000
| > | > | Date: 12 Jul 2002 04:33:00 -0700
| > | >
| > | > | I've found the answer!  When I use the DTS Export wizard and choose
| to
| > | > | copy "extended properties" I get the problem.  When I don't copy
| > | > | "extended properties", it all works fine.  My guess is that the
| tables
| > | > | I **could** open were those without any extended properties.
| > | > |
| > | > | I'm posting this just in case it will be of use to others.
| > | > |
| > | > | Pawel
| > | > |



| > | > | > Hi,
| > | > | >
| > | > | > I'm fairly new to Access 2002 and to SQL Server 2000.  I've
| created
| > | > | > tables, forms, reports, etc using an Access .ADP file connected to
| a
| > | > | > development SQL Server 2000.  Everything works fine.  Recently
| I've
| > | > | > been trying to copy my development SQL Server database to a
| production
| > | > | > SQL Server (also SQL Server 2000).  Everything to do with the copy
| > | > | > process works but...
| > | > | >
| > | > | > ...in Access, when I change the connection information in the ADP
| file
| > | > | > to connect to the production server and I double-click on a table
| in
| > | > | > the project window to open it, I get a message "The stored
| procedure
| > | > | > executed successfully but did not return records" and the table
| > | > | > doesn't open.  What is strange is that I CAN open some tables but
| not
| > | > | > others.  Also, all my forms still work, even when they are
| referencing
| > | > | > tables I can't open from the project window.
| > | > | >
| > | > | > All the tables are owned by 'dbo' and the user I'm logging in as
| has
| > | > | > all the correct permissions to the tables.  Even if I log in as
| 'dbo'
| > | > | > I get the same problem.  If I connect back to my development
| server
| > | > | > everything is fine.
| > | > | >
| > | > | > I've tried copying my database to the production server using the
| DTS
| > | > | > Export wizard and also using the "Transfer Database" facility in
| > | > | > Access 2002.  I've checked all the users, logins, permissions,
| etc,
| > | > | > and can't see anything that is wrong.
| > | > | >
| > | > | > Has anyone come across this before?  Does anyone know how to get
| round
| > | > | > this?  Am I doing something really stupid?  Please put me out of
| my
| > | > | > misery!
| > | > |
| > |
| >
| >
|
|
|

Here is a script for one of the tables.

Note that there are 2 foreign keys for tables "APPLICANTS" (Identity column
APPLICANT_ID) and "CAMPUSES" (nvarchar(2) column CAMPUS_ID). You can either
create 2 dummy tables with these fields, to ensure the script works, or
remove the foreign key constraints.

Best regards,

Peter

<---- Script Follows

/****** Object:  Table [dbo].[INVOICES]    Script Date: 25/07/2002 2:21:23
p.m. ******/
CREATE TABLE [INVOICES] (
 [INVOICE_ID] [int] NOT NULL ,
 [GUID_INVOICE]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT
[DF_INVOICES_GUID_INVOICE] DEFAULT (newid()),
 [APPLICANT_ID] [int] NULL ,
 [CAMPUS_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
 [DATE] [datetime] NULL CONSTRAINT [DF__INVOICES__DATE__4AB81AF0] DEFAULT
(convert(datetime,convert(varchar,getdate(),1),1)),
 [SHOW_IN_QUERIES] [bit] NOT NULL CONSTRAINT
[DF__INVOICES__SHOW_I__4BAC3F29] DEFAULT (1),
 [COMMISSION_PREPAID] [bit] NOT NULL CONSTRAINT
[DF__INVOICES__COMMIS__4CA06362] DEFAULT (0),
 [NOTES] [nvarchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
 [upsize_ts] [timestamp] NULL ,
 [CANCELLED] [bit] NULL CONSTRAINT [DF_INVOICES_CANCELLED] DEFAULT (0),
 CONSTRAINT [INVOICES_PK] PRIMARY KEY  NONCLUSTERED
 (
  [INVOICE_ID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_INVOICES_APPLICANTS] FOREIGN KEY
 (
  [APPLICANT_ID]
 ) REFERENCES [APPLICANTS] (
  [APPLICANT_ID]
 ) ON DELETE CASCADE  ON UPDATE CASCADE ,
 CONSTRAINT [FK_INVOICES_LOOKUP_CAMPUSES] FOREIGN KEY
 (
  [CAMPUS_ID]
 ) REFERENCES [LOOKUP_CAMPUSES] (
  [CAMPUS_ID]
 ) ON UPDATE CASCADE
) ON [PRIMARY]
GO

exec sp_addextendedproperty N'Attributes', N'0', N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'ColIsGuid', N's_GUID', N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'DateCreated', N'29/01/2002 2:49:35 p.m.',
N'user', N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'LastUpdated', N'26/02/2002 7:12:59 p.m.',
N'user', N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'MS_DefaultView', 2, N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'MS_Filter', null, N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'MS_LinkChildFields', N'INVOICE_ID', N'user',
N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'MS_LinkMasterFields', N'INVOICE_ID', N'user',
N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'MS_OrderBy', null, N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'MS_OrderByOn', 0x00, N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'MS_Orientation', 0, N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'MS_SubdatasheetName', N'dbo.INVOICE_ITEMS',
N'user', N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'MS_TableMaxRecords', 0, N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'Name', N'INVOICES', N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'OrderByOn', N'False', N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'Orientation', N'0', N'user', N'dbo', N'table',
N'INVOICES'
GO
exec sp_addextendedproperty N'RecordCount', N'319', N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'Updatable', N'True', N'user', N'dbo',
N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'ValidationRule',
N'IsNull("INVOICE_ID")=False', N'user', N'dbo', N'table', N'INVOICES'
GO
exec sp_addextendedproperty N'ValidationText', N'Please supply an invoice
number, this is a required field.', N'user', N'dbo', N'table', N'INVOICES'

GO

exec sp_addextendedproperty N'AllowZeroLength', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'Attributes', N'1', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'ColumnHidden', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'ColumnOrder', N'0', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'ColumnWidth', N'-1', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'DataUpdatable', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'GUID', N'????????', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_BoundColumn', N'1', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_Caption', N'Applicant', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnCount', N'2', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnHeads', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x00, N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ColumnWidths', N'0;1440', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_DecimalPlaces', N'255', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'The applicant this invoice
is being issued for', N'user', N'dbo', N'table', N'INVOICES', N'column',
N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_DisplayControl', N'111', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_LimitToList', N'-1', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ListRows', N'8', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_ListWidth', N'1440twip', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_RowSource', N'SELECT
APPLICANTS.APPLICANT_ID, APPLICANTS.NAME
FROM APPLICANTS', N'user', N'dbo', N'table', N'INVOICES', N'column',
N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'MS_RowSourceType', N'Table/View/StoredProc',
N'user', N'dbo', N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'Name', N'APPLICANT_ID', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'OrdinalPosition', N'5', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'Required', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'Size', N'4', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'SourceField', N'APPLICANT_ID', N'user',
N'dbo', N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'SourceTable', N'INVOICES', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'Type', N'4', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'ValidationRule', N'Is Not Null', N'user',
N'dbo', N'table', N'INVOICES', N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'ValidationText', N'Please select an applicant,
this is a required field.', N'user', N'dbo', N'table', N'INVOICES',
N'column', N'APPLICANT_ID'
GO
exec sp_addextendedproperty N'AllowZeroLength', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'Attributes', N'2', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'ColumnHidden', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'ColumnOrder', N'0', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'ColumnWidth', N'-1', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'DataUpdatable', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'GUID', N'????????', N'user', N'dbo', N'table',
N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_BoundColumn', N'1', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_Caption', N'Campus', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnCount', N'2', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnHeads', N'False', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x00, N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table',
N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 1635, N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_ColumnWidths', N'0;1905', N'user', N'dbo',
N'table', N'INVOICES', N'column', N'CAMPUS_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Campus where this applicant
intends to study', N'user', N'dbo', N'table', N'INVOICES', N'column',
N'CAMPUS_ID'
GO
exec ...

read more »

Hi Peter,

Thanks for the script.  I reproduced the error you describe.   I can get rid of the error by removing all the
extended properties but I have not yet pinned down which ones are causing the problem.

I'll let you know what I find out.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------





| Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| Date: Thu, 25 Jul 2002 14:28:03 +1200

| Hi Brian,
|
| Here is a script for one of the tables.
|
| Note that there are 2 foreign keys for tables "APPLICANTS" (Identity column
| APPLICANT_ID) and "CAMPUSES" (nvarchar(2) column CAMPUS_ID). You can either
| create 2 dummy tables with these fields, to ensure the script works, or
| remove the foreign key constraints.
|
| Best regards,
|
| Peter
|
| <---- Script Follows
|

Hi Peter,

The only extended properties causing the problem are the ones that try to pass the value "0x00" as the
second parameter of the sp_addextendedproperty procedure.  In your case, this is occurring with the field
property, MS_ColumnHidden, and the table property, MS_OrderByOn.   Removing these properties or
changing their values to something like "0" fixes the problem.

How did the tables get created in the first place?  Did you use the upsizing wizard to create the tables and
then use DTS to move the data?    Also, what version of Access did the files come from (97,2000, 2002)?  If it
came from Access 2002, was the file format in 2000 or 2002 format?   I would like to know how to reproduce
the problem from scratch.

Thanks,

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------


| Subject: Re: Cannot open tables using Access 2002 adp to SQL Server 2000
| Date: Thu, 25 Jul 2002 14:28:03 +1200

| Hi Brian,
|
| Here is a script for one of the tables.
|
| Note that there are 2 foreign keys for tables "APPLICANTS" (Identity column
| APPLICANT_ID) and "CAMPUSES" (nvarchar(2) column CAMPUS_ID). You can either
| create 2 dummy tables with these fields, to ensure the script works, or
| remove the foreign key constraints.
|
| Best regards,
|
| Peter
|
| <---- Script Follows
|