Problem with Syntax

[SAM453]

Hi guys,

what Iam doing is Updating a backend sql table using front end form.

Set db = CurrentDb
stSQL = "Update Lkp_Store1 set Facility_Number='" & (Text8) & "',[Station_Id]='" & (Text0) & "'," & _
"[Zone]='" & UCase(Text2) & "',[Type]='" & (Text4) & "'," & _
"[UserId]='" & fGetUserName() & "',[UpdatedDateTime]='" & Now() & "' " & _
" where SID = " & Me!Combo7.Value & ""

DoCmd.RunSQL stSQL

When i use above code and table name as Lkp_Store1 it works fine.

When i use Lkp_Store_testing it says
Run time error 3073
Operation requires Updatable query.

Can any one tell me how to handle the table names like this table_abc_szy

I tried [Lkp_Store_testing] but it didn't worked.

Thanks,
Sam.

 

[oharab]

Is Lkp_Store_testing a table or a query?
Is Lkp_Store_testing exactly the same as Lkp_Store1?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[kjv1611]

When you changed the table name... are you SURE you changed the name in all locations in your code AND that what you put in the code matches the table name?

Also, if is a different table, not just a different instance of a linked table, then do you have write permissions to the different table?

 

[oharab]

Just re-read your question & it seems Lkp_Store_testing is a linked SQL table. If so, then are you sure the permissions are set on the external database?

B.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[SAM453]

thanks for your replies folks...
Both are Linked tables.
I came to know why it was creating problem.
Lkp.store1 has a primary key in it
and Lkp.store_testing doesn't have any key on it.

If we want to edit a table from the form then that table should contain a key column. That's what i found after spending 3hrs straight.
Let me know if assumption is wrong.

thanks,
SAM.

 

[oharab]

That's right, when you're using external databases it needs a primary key. It's bitten me on the rear several times!

Well done on getting ot the bottom of it.

Ben.


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.