Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access #Deleted data

Jim Dettman provided a potential solution here: -
https://www.experts-exchange.com/questions/28583375/Why-is-it-showing-Deleted-in-all-fields-and-records-in-my-Access-2010-linked-table.html#a40506986

I have an Access DB linked to a Sage SQL database. The Sage developer has created a table for me to populate with ID being the Primary Key, data type bigint. I added a Timestamp field (Suggestion in the link above) but the data (apart from the first row) just shows #Deleted. Strangely if I sort the data Descending I can see the data in the last row.

Can anyone shed light on this? BTW the Sage developer said: -
 
"Sage has specific requirements for custom tables in the database (especially if Sage is to also read from those tables, which is the case). It requires an ID column set as the primary key that does not auto increment as the record ID’s are controlled by the client application."
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

#Deleted occurs when JET can't reselect the record in a data set.  It also occurs if the table design is changed, but the meta data that JET has is old.

So I would first refresh the table in the Access DB with the linked table manager.  See if that fixes it.

Jim.
Avatar of HKFuey

ASKER

Hi Jim,

Did try this, will have another go. I also deleted the table and re-linked it.

The SQL server is on the other end of a 30Mb VPN, could that be the issue?

Andy
Andy,

  <<The SQL server is on the other end of a 30Mb VPN, could that be the issue>>

  No, it should be fine.

  So when you open the table as a table, you see #Deleted for every row/column except for the last?

   Also which version are you using....you said the new field is a Big Init, which could be a problem.

  and do you see the timestamp column in the table?

Jim.
Avatar of HKFuey

ASKER

This is just Test data for now: -
User generated image
Avatar of HKFuey

ASKER

Timestamp looks like this: -
User generated image
Which version of Access?

Jim.
Avatar of HKFuey

ASKER

sql Select @@version = 2012
Access = 2013 (I also have 2007)
Try to open design view of the linked table from access, and screenshot that for us. That might provide clues.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
BTW, casting to int will work as well and may be preferable.

Jim.
@Jim: I believe (but don't know) that bigint previous to the update of Access 2016 was "simply" seen as text, instead of a number. I suspect the issue is that no primary key is defined on the table. You can create this through code however, but a screenshot of the design view should reveal if that is the case.
Avatar of HKFuey

ASKER

OK, noticed a couple more Bigint fields (They don't need to be)
User generated image
If they don't need to be BigInt, then simply change them to int. THEN set a primary key constraint on the ID column, and refresh the link from access. This should allow Access to correctly ID the rows.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Anders,

<<I suspect the issue is that no primary key is defined on the table. >>

 Shouldn't matter with a timestamp field having been added.  JET is supposed to latch onto that above all else, but never say never<g>

 I think though the primary problem is the data types....and good spot on the decimal!

Jim.
Avatar of HKFuey

ASKER

Thanks for the help, I agree bigint is the problem. The Sage guy does not want to change this. Maybe I should look at Access 2016?
(My other thought is to give them access to my SQL and provide them a view for them to work from, less work for me!)
<<Maybe I should look at Access 2016?>>

  Bigint support is only through Office 365, the MSI install doesn't have it (retail box, OEM, or VLS).


<<(My other thought is to give them access to my SQL and provide them a view for them to work from, less work for me!)>>
 
 That would be the route I would go.

Jim.
Avatar of HKFuey

ASKER

Jim, what about this: -
https://support.office.com/en-gb/article/What-s-new-in-Access-2016-76454345-f85d-47af-ace1-98a456cb3496?ui=en-US&rs=en-GB&ad=GB&fromAR=1

I have a quote on our Open License Program, are you saying no bigint support?

Regards

Andy
If you purchased Access 2016 without an Office 365 subscription, you get all of the features listed under "September 2015" at the bottom of the page. If you are an Office 365 subscriber, you get the features listed under "September 2015," plus all other updates as well.

 To my knowledge, anything past the Sept 2015 upgrade is Office 365 only.  Microsoft is *strongly* pushing everyone to Office 365 and one way they are doing that is by providing new features in O365 right away and not releasing those same updates for the traditional MSI installs.

 Whether or not we see Bigint support in the MSI install (and if so when) remains to be seen.

Jim.
Avatar of HKFuey

ASKER

Thanks,
Andy