How to create updatable query

[usr123]

How can i make a query updatable?

"Update dbo_fin_ldg set Year = 2010, Month_Name = 'Feb',Month = 02,Product = '',Category = 'Gear',Customer = '',Actual_Volume = 12,Budget_Volume = 1245 Where ID = '2488';"

 

[DCrake]

Couple of things, firstly field name slike Year and Month are no no's as they are known as Access Reserved words. Access can get confused between field names and reserved words. You need to rename them as soon as possible.

Secondly is the ID a number field, if so, then you do not need the quotes round the value.

What error are you encountering?

David

 

[usr123]

Thanks for your reply.
Year and Month are the column fileds in the database(Sql). Do you want me to chnage these names in the sql table?
Also removed the quotes from ID, same error.
Error 3073, Operation must use an updatable query.

 

[usr123]

My new query:

"Update dbo_fin_ldg set ldg_Year = 2010, Month_Name = 'Feb',ldg_Month = 02,Product = '',Category = 'Gear',Customer = '',Actual_Volume = 12,Budget_Volume = 1245 Where ID = 2488;"

Any ideas plz....

 

[DCrake]

It seams like you do not have write permissions to the table. To test this open the table in datasheet view and try to edit it.

David

 

[usr123]

You are right, i cant edit a table whne i open it in access. How can i do that?

 

[usr123]

In datasheet view,it says recordset is not updatable.

 

[DCrake]

As per post #5 it appears you do not have the necessary permissions to write back to the SQL data tables. Check with the owner of the SQL database and ask them for permission to edit etc.

There may be a valid reason why these permissions have not been granted.

David

 

[usr123]

Hello,

I have changed the permission of the account to 'Read,write,dbowner.' But error is still there. What else should i check?

 

[DCrake]

You may find you need to reconnect the tables to your front end to force the privilages.

David

 

[usr123]

I did,deleted the old link and created a new one.Error is still there.

 

[DCrake]

How are you connecting to the SQL Server? Are you using a System DNS? What type of authentification have you set?

 

[usr123]

I am using ODBC connection.I have set ODBC through system DSN.

 

[usr123]

Here is something else, My identity key in the table wasn't a primary key.I changed it to Primary key,now its giving me the error '3622': You must use the dbSeeChangesoption with Openrecordset when accessing a sql server table that has an identity column.

 

[SOS]

When you link a SQL Server table from Access it will many times come up with a dialog as you link it where you have to tell it what the key field or fields are. If you don't do that, it will not be updateable. Try deleting the table from Access and re-linking and seeing if it gives you that. If not, it still might work after relinking because you didn't have a primary key field set originally and Access needs SQL Server tables to have a PK field (or fields) set to be able to do updates.

 

[usr123]

Thanks for ur reply.
Error i was gettin not because of PK(i did set PK in the sql table and reconnected it to the Access), it was because i wasn't using 'dbseechanges' while i was pulling the data from Sql and was tring to do 'db.execute' rather than doing docmd.runsql.
Anyways its working fine now.
Thanks for the help.

I might come back with some other issue

 

[LPurvis]

Are you saying that you're using
DoCmd.RunSQL strSQLStatement
instead of
db.Execute strSQLStatement, dbSeeChanges
?
If that's the case, then is there a particular reason why?
(Given your SQL statement and lack of parameters I can't even see a "cheat" reason why ;-)

Cheers.