Update the CURRENT record with a button push ?

[webcat]

Hi

this is mad, for three days i've been scouring the web and my books on VBA, Access etc...

I have a linked table from a MS SQL server
for some reason I can't edit any data in it directly - ie, when typing into a form, i just get a locked message.
however, when i run SQL queries on it, it works fine.???!

i guess this must be some crazy SQL permissions issue?

anyway. i need to UPDATE a field called archiveFlag - this is to tell the following query which records to move to an archive table, out of the main table.

and i want to do this by having a button on each line of a Continuous Form

I'm completely stuck here - how do i write a SQL or DAO or ADO script to simply update one field on the CURRENT record - current being the one on which the button is pressed.

i'm going nuts here. my chair is uncomfortable and the radio is too loud and I can't get my DB to work!!! please help!

thanks!
Edit/Delete Message

[missinglinq]

You have inadvertently posted your question in the Articles section rather than in the Forum section of our site, so I have moved it across to the Forum for you.

Welcome to TheScripts!

Linq ;0)>

[NeoPa]

...
I have a linked table from a MS SQL server
for some reason I can't edit any data in it directly - ie, when typing into a form, i just get a locked message.
however, when i run SQL queries on it, it works fine.???!
...

1. Is this SQL that works running on the Access database or on the SQL Server?
2. What happens if you try editing the data directly within the linked table?

[webcat]

1. Is this SQL that works running on the Access database or on the SQL Server?
2. What happens if you try editing the data directly within the linked table?

hi

the sql that works is running from VBA modules within access

when i try editing data directly in the table i also get the same problem where the data is locked and i can't make any changes - i get the copy to clipboard or drop changes message box.

- other linked tables from a different DSN are working fine - so i'm kinda thinking there is a problem with that particular linked table - but i've checked using MS SQL enterprise manager and as far as i can see i have the same privs on both DBs/tables...!

i'm stuck!

[NeoPa]

...
when i try editing data directly in the table i also get the same problem where the data is locked and i can't make any changes - i get the copy to clipboard or drop changes message box.
...

I'm not sure what "copy to clipboard or drop changes message box" means but I doubt it's important.
I've no idea why updates made via a SQL action query would work if the permissions on the table restrict updates. You are sure the SQL is not a Pass-Thru query?
The only other thing I can think of to do is to delete the link to the table and recreate it if that's not too involved a job (normally it's quite straightforward).

[webcat]

i'm convinced this is SQL permissions now, i don't know why my vb driven queries are working, but the table from one DSN is being strange, but the tables from another are working fine.

SQL permissions seem to be a complex art !


however, going back to my original post, is there a way to use DAO to allow a button push on a row to .Edit a field of the current record - ie, the one where the button is on.?

I can find lots of info on how to navigate through records with DAO like MoveFirst, MoveNext - but I can't find any way to use the Current one !!


can anyone offer a pointer?

thanks a million!

[webcat]

don't believe this


http://support.microsoft.com/kb/278696

after a week of searching this is the issue

my SQL table had BIT fields and its a known issue that Access doesn't update them!

*sigh* wasted a week, but its working now!! thanks anyway!

[NeoPa]

Thanks for the update webcat.
I guess the error message in the link :

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

is what you were referring to when you said

i get the copy to clipboard or drop changes message box.

Well - no bones were broken. I guess the important point is that it got resolved eventually.