linked sql table has no unique identifier

[daisymay]

Hi everyone, first post here. I'll get right to it, if you need more background just let me know -
I've linked tables from a SQL database, I'm attempting to do updates to a table using ADO. I can read what's in the linked table, I've used hard-coded data instead of the variable (to avoid a bad variable being the problem) but I get an 'Object Required' error. When I try the same update using DNS it updates 2 records (instead of the 1 I was expecting, but at least it updates!).
Looking at the table at the source, it has 3 key fields but no unique identifier. When I link the table I reference those 3 fields as the 'keys'. I'm at a loss... doeas access not allow you to update SQL tables that do not have unique identifiers? Has anyone here found a way around that?
Thanks in advance for any suggestions.

 

[JoeAtWork]

'Object Required' usually means you did not initialize an object correctly, or perhaps misspelled a property. Post your code, it may be easy to spot.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[JBinQLD]

G'day,

I had similar problem and I found a hint that suggested adding a timestamp field to the table. I done that and it fixed the problem. Like so many things in life I intend on going back to fix it one day.

When that "one day" arrives it's gonna be a fell of a busy one, Over the years I've promised to do so much that day!

Will be interested to see your final tidier solution

Have a great weekend

JB

 

[daisymay]

Unfortunately I can't add a field to the database because it is a purchased product. If they upgrade then my field could go away, if I'm gone... then my tool will no longer work.

Instead of using the update method I created a private function that takes the necessary info and returns an update SQL string, then I use

db.Execute UpdateSTR(strStudent, strRel, "hemail", Me.txthemail.Value)

instead of

Set rs.Fields("hemail").Value = Me.txthemail

A little bit more code, but not much. It works, but leaves the problem of unexpectedly updating more than one record. Apparently not an issue in this particular app, but leaves me feeling sloppy.