July 31, 2007 at 9:18 am
Hi-
I have a VERY odd issue using Microsoft Access 2003 as a quick data entry front end to SQL Server 2005. Note, both products are FULLY updated. Access is great with its "lookups" to provide quick dropdowns.
When "importing" the table into 2k5, all is fine and the data imports correctly. However when "linking" to the same table, the data is incorrect. This is using the SQL Server 2005 native ODBC provider. The only odd thing about the SQL Server table I can think of is that it contains a smallint identity column seeded at -32768 and incrementing by one.
I will try creating a new table with different values, data types etc.. to try to whittle down the issue. Also note, the link worked correctly for about 10 items of data entry, then the smallint identity values in access started appearing incorrectly.
Will post any results but if anyone has thoughts - please let me know.
CREATE
TABLE [dbo].[ProviderBusinessUnit](
[IDProviderBusinessUnit] [smallint]
IDENTITY(-32768,1) NOT NULL,
[idProvider] [smallint]
NOT NULL,
[idSubmissionType] [tinyint]
NOT NULL,
[Descriptor] [varchar]
(127) NOT NULL,
CONSTRAINT [IDX_PU_ProviderBusinessUnit_idProviderBusinessUnit] PRIMARY KEY NONCLUSTERED
(
[IDProviderBusinessUnit]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
)
ON [PRIMARY]
- B
July 31, 2007 at 12:06 pm
Well-
Only after dropping and recreating the SQL 2K5 table was I able to proceed properly.
My best guess is that I had "reset" the table - meaning - I changed the identity column to be NOT an identity even though about 10 records existed. Then I changed it back to identity and reset the identity seed to -32768.
Only after a full recreate was I able to link properly and add more records to the table. This is certainly a bug and can be reproduced with a little linking and tinkering.
Also, if I scroll past one page of data in Access and then add a new row the system will say #Deleted once the row edit is complete - even though the row does exist after an exit and refresh.
Really wierd - I saw no such wackiness between Access and SQL 2K.
- B
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply