Write confilct access 2003

I'm working with a table that was built before my time at this company - it has many many bit fields. I added an INT to the table to use but i cant update the record in access on the form, or in the table directly..i made sure there are no null Bits in the table, i tried setting dirty = false...Not sure w hat to do form here other then change the Bits to Tiny int? Will that hurt anything?
bmcnultyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I assume the tables are hosted in SQL Server?

The most common cause of this is a NULL value in one of those Bit fields, but you mention that you've insured that's not the case. I'd still suggest you check again, and perhaps run a query to update any NULL bit Fields to 1 or 0, depending on your needs.

Also, make sure there is a primary key on the table. Access won't be able to update it if there is no PK. To be sure the linked table has a PK, open that link in Design view. Access will complain, but it'll let you do it. Check to be sure you have a PK on the linked table.

I've also had issues where I needed to remove and recreate the links. Simply relinking didn't work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmcnultyAuthor Commented:
Very very interesting...I saw a primary key, we already have a timestamp...i crated a local copy with the data/structure for testing and still got the error. I then tried removing the table and re-linking it and it is now saving....i'll test a few more times and let you know.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've found quite a few oddities with SQL linked tables that could only be resolved with a "delete-and-recreate" process - so much so, that in most of my more widely used SQL linked apps, I do this on startup. I'll keep a table in each FE which lists all Tables to be relinked, and have a routine that removes all links and recreates them at launch. It takes a few extra second (usually about 10 - 15 seconds, if that), but it helped me to resolve a LOT of issues.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bmcnultyAuthor Commented:
Seems to be working now, Thanks.
0
Nick67Commented:
Very very interesting...I saw a primary key, we already have a timestamp...i crated a local copy with the data/structure for testing and still got the error. I then tried removing the table and re-linking it and it is now saving....i'll test a few more times and let you know.

I don't run into that kind of grief too often.
I assume (or ass u me) that you used the Linked Table Manager to refresh the table after your change?
That must have had a hiccup.
Timestamp is a big one, but you had that covered.

The fun one can be that Access has its beliefs about the PK of a linked datasource, and the datasource itself may or may not have a PK or in fact a completely different PK.  Access can be happily told that a certain combination of fields is the PK for a linked table, when the linked table is in fact an unindexed View.  Just so long as Access can use what that GUI dialog had as a unique key, your table will be updatable.

When Access loses that PK information, well, that's not fun -- like when you export all the tables from a one app to another.  You can't re-add that information through the user interface.  You can delete /relink tables -- in my case hundreds of them.  Or you can use VBA and TableDefs to do it.

Sounds like Access lost its PK ideas about your table.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
you can use VBA and TableDefs to do it.
That's what I do with my FE recreate routine. I remove any table with a connection, and then loop through my FE table containing (a) the name of the table, (b) any Index SQL that needs to be run and (c) details about the PK for each table. Using VBA/SQL, I recreate the Tables, Indexes, and Primary Key structure.
0
Dale FyeCommented:
Scott,

I've don't recall ever running into a situation where simply dropping and relinking didn't take care of the indices and PK.  Are you actually running code to set indexes and PK of the linked SQL tables?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, I am, but only if needed. As Nick indicated, sometimes Access has trouble dealing with Indexes and PKs on certain SQL tables and views. I found that simply relinking (or removing and relinking) those tables/views would fail to properly recreate PK and Indexes - not every time, but often enough to be a pain. I don't recall the exact situations I ran into that put me down that path, of course, but I recall it being sporadic, and very troublesome.

So I created the routines that would drop/recreate all links, and verify the PK and Indexes. For those ones that turn out to be troublesome, I incorporate those routines into the startup of the application. Takes a few extra seconds to launch, but it stopped the phone calls!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.