August 23, 2007 at 8:56 am
Hello,
I have a problem with a linked table in ms Access. Here is the setup.
I have tables in a database in SQL Server 2000. I am replicating these table to another database on another server using Transactional replication (also SQL Server 2000). Then, my business analysts are accessing these tables from MS Access. They have created a linked table in Access.
Now for the problem. Viewing the tables or running queries from Access always results in every field in every record showing as #Deleted. However, if I select distinct, or an aggregate, then the records show up correctly.
Any ideas on what the problem is? We're using ODBC version 3.525.1117.0. Here is the create table script...
CREATE TABLE [tblBAUse] (
[ID] [bigint] NOT NULL ,
[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcctNum] [bigint] NULL ,
[RecNum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VarNum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EnterDate] [datetime] NULL ,
[ExitDate] [datetime] NULL ,
[TotalCharges] [money] NULL ,
[OtherCharges] [money] NULL ,
[Discount] [money] NULL ,
[Prepay] [money] NULL ,
[Waived] [money] NULL ,
[Adjustment] [money] NULL ,
[Duration] [int] NULL ,
[Payment] [money] NULL ,
[Amount] [money] NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistNum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayDate] [datetime] NULL ,
[TaxID] [int] NULL ,
[UnitNum] [int] NULL ,
[CheckAmount] [money] NULL ,
[ReportDate] [datetime] NULL ,
[CheckNum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogID] [int] NULL ,
[LinkID] [bigint] NULL
) ON [PRIMARY]
GO
Thanks,
Michael
August 24, 2007 at 7:40 am
When you create a link to a table from within Access - Access will often ask you what the primary key is (if it can't determine what it is on its own). It tends to get very confused if you didn't pick anything, or if what you picked isn't unique.
Also - the #deleted sometimes shows up when Access figures out something big changed in the underlying data (like all of the data gets rebuilt, or it figures out that the table structure in SQL has changed from what it was when the link was established).
In either case - try dropping and rebuilding the linked table. That seems to do the trick often enough for me that I wrote something to do it "automatically" when my occasional user has the problem.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2007 at 2:09 pm
Thanks for the response. I've tried all of that, but none of it worked. Actually, I found out that the primary key is what's causing the problem.
If I don't choose a primary key when I link the table in Access, my problems are gone. However, Access was trying to be clever and choose a primary key for me on most of my tables. It was doing this based on an index defined in SQL Server. I removed the index and relinked without primary keys, now I can see my data!
My identity columns are Bigint, so Access was treating them like text and for some reason, Access can't seem to handle a text identity column.
Thanks again,
Michael
September 11, 2007 at 8:02 am
You need to change the type of your identity columns from bigint to int. I was having the same problem and that solved it...
September 12, 2007 at 7:54 am
I have seen this when the database schema has changed since the creation of the ODBC.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply