SpaceCoastLife
asked on
Strange result when linking to a SQL Server Db from Access.
First time for me seeing this one …
After attaching a SQL database in SSMS and linking to an Access Db, all linked tables display #Deleted in every field.
Ideas anyone?
After attaching a SQL database in SSMS and linking to an Access Db, all linked tables display #Deleted in every field.
Ideas anyone?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<Jim, you're saying the #Deleted means Access>>
Not that it can't locate the DB, but locate the records. There was a MSKB article explaining this, but I can't find it at the moment. I did however find an older version of the article which still touches on most of it:
https://jeffpar.github.io/kbarchive/kb/172/Q172339/
In short, JET uses a Keyset cursor with ODBC datasources. It builds the recordset and then when it needs to do something with a record (like repaint it on the screen or update it), it actually goes out and re-fetches the record. In order to do that, it uses the key it has saved. If for some reason it can't re-fetch it, it will display #Deleted.
The other way you can get #Deleted is if the structure of the table has changed since you last linked the table. In that case, all you need to do is use the linked table manager to refresh the table links.
<<but from Access I used the TransferDatabase command in a loop with ODBC to link the tables (50 of them) >>
That also might be part of the problem. You need to make sure when linking you have a unique key specified. If one does not exist in the table (which shouldn't be the case in a relational DB), then you can create a pseudo-index on the Access side for the table that is unique.
So:
1. Take a look at a few tables and see if they have a primary key
2. Use the linked table manager and refresh the link.
If neither of those take care of things, then add a timestamp field to the table in SQL. Re-fresh the link and check again.
If that still doesn't do anything, then it's one of the data types as Daniel pointed out.
Jim.
Not that it can't locate the DB, but locate the records. There was a MSKB article explaining this, but I can't find it at the moment. I did however find an older version of the article which still touches on most of it:
https://jeffpar.github.io/kbarchive/kb/172/Q172339/
In short, JET uses a Keyset cursor with ODBC datasources. It builds the recordset and then when it needs to do something with a record (like repaint it on the screen or update it), it actually goes out and re-fetches the record. In order to do that, it uses the key it has saved. If for some reason it can't re-fetch it, it will display #Deleted.
The other way you can get #Deleted is if the structure of the table has changed since you last linked the table. In that case, all you need to do is use the linked table manager to refresh the table links.
<<but from Access I used the TransferDatabase command in a loop with ODBC to link the tables (50 of them) >>
That also might be part of the problem. You need to make sure when linking you have a unique key specified. If one does not exist in the table (which shouldn't be the case in a relational DB), then you can create a pseudo-index on the Access side for the table that is unique.
So:
1. Take a look at a few tables and see if they have a primary key
2. Use the linked table manager and refresh the link.
If neither of those take care of things, then add a timestamp field to the table in SQL. Re-fresh the link and check again.
If that still doesn't do anything, then it's one of the data types as Daniel pointed out.
Jim.
ASKER
Turned out to be Datatypes after all. Specifically, BIGINT and DATETIME2.
Thanks to both of you for the help.
Thanks to both of you for the help.
Would you do me a favor please and go back and mark Daniel's first comment:
https://www.experts-exchange.com/questions/29158383/Strange-result-when-linking-to-a-SQL-Server-Db-from-Access.html?anchorAnswerId=42943481#a42943481
as the solution as that's what it turned out to be.
Thanks,
Jim.
https://www.experts-exchange.com/questions/29158383/Strange-result-when-linking-to-a-SQL-Server-Db-from-Access.html?anchorAnswerId=42943481#a42943481
as the solution as that's what it turned out to be.
Thanks,
Jim.
ASKER
My most recent attempt to resolve this was to create a Snapshot of the converted (now SQL Server) file but the results were the same.
Jim, you're saying the #Deleted means Access couldn't locate the file but from Access I used the TransferDatabase command in a loop with ODBC to link the tables (50 of them) and Access did that without incident. Do you still think it's a "locate" problem?