HKFuey
asked on
Access #Deleted data
Jim Dettman provided a potential solution here: -
https://www.experts-exchan ge.com/que stions/285 83375/Why- is-it-show ing-Delete d-in-all-f ields-and- records-in -my-Access -2010-link ed-table.h tml#a40506 986
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: -
https://www.experts-exchan
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."
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
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.
<<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.
Which version of Access?
Jim.
Jim.
ASKER
sql Select @@version = 2012
Access = 2013 (I also have 2007)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, casting to int will work as well and may be preferable.
Jim.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
<<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.
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!)
(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.
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.
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
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.
ASKER
Thanks,
Andy
Andy
So I would first refresh the table in the Access DB with the linked table manager. See if that fixes it.
Jim.