May 6, 2010 at 1:27 pm
I have linked 20 tables from SQL 2005 to Access 2003. I migrated the data from Access 2003 to SQL 2005 with no issues. Then I linked those same tables to the front-end Access 2003 db with no issues
However, there is one table that breaks when linked. The linked table takes one random row and duplicates it when it is rendered in Access. For example. The Table in SQL has the following rendered (correctly)
A B C
1 2 3
D E F
4 5 6
G H I
7 8 0
But when you open this same linked table in Access, the table incorrectly shows the following
A B C
A B C
D E F
D E F
7 8 0
7 8 0
Both tables have the same number of rows; its just that it takes one row, and duplicates it, excluding other rows.
I have deleted and table and relinked it. I have remigrated the table from a different Access 2003 db to a different SQL 2005 db and relinked it. I renamed the table before linking it. Nothing works. I get the same issue!
The table in SQL has correct data. I'm able to run queries against and get correct information. But the Linked Table in Access is utterly useless.
I do not even know where to look to try to solve this problem. I did migrate this one table from a different Access database.
Anyone have any ideas? Thanks
-uman
May 6, 2010 at 2:09 pm
Things I came across in Googling mentioned the primary key type on the table in SQL is not rendered correctly in Access 2003.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 7, 2010 at 9:36 am
Shawn,
Thanks for your suggestion. When I goto Google, I just get so many hits, I don't know where to begin.
As it turns out, in Access 2003 during the linking process, there is a pop up box which asks the user to select Primary Keys before the Table link occurs. I was selecting too few columns in which existed duplicates on those keys. When I expanded the selection to include more keys, I was able to obtain a total "unique key" which then linked the table correctly.
So, for example, the pop up box asked
"Select the following primary keys for your table"
A
B
C
D
E
Before, I was just select A, B, but columns A and B together contained duplicate rows. When I selected A, B, C, D, the combination returned unique rows for those 4 columns which returned the table correctly in its entirely.
Just posting this for future reference in the peculiar table linking issue
-Uman
January 20, 2011 at 7:09 pm
umanpowered (5/7/2010)
...in Access 2003 during the linking process, there is a pop up box which asks the user to select Primary Keys before the Table link occurs. I was selecting too few columns in which existed duplicates on those keys. When I expanded the selection to include more keys, I was able to obtain a total "unique key" which then linked the table correctly.So, for example, the pop up box asked
"Select the following primary keys for your table"
A
B
C
D
E
Before, I was just select A, B, but columns A and B together contained duplicate rows. When I selected A, B, C, D, the combination returned unique rows for those 4 columns which returned the table correctly in its entirely.
-Uman
Another option is to re-create the table and add another field which is guaranteed to be unique.
Remember a Primary key's main purpose is so that you can uniquely identify a row of data. It doesn't necessarily need to be the same as the data you are working with. You can also create indexes, and join on other fields.
SQL server has the equivalent of an autonumber called "Identity". You can use the identity column as a primary key, It is guaranteed to be (and remain) unique.
Access will recognise it and use this for the purpose of handling record sets. (Although it may require a few small subtle changes to work such as adding dbSeeChanges to the record set syntax.)
If required, you can create an additional unique index on your other columns if you wanted to enforce uniqueness on those fields.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply