All geek questions in one place

Access 2010 with related sql tables

I have a view of a split form with data coming from a sql (2008) linked server.

How do I update a record?

Currently, it will not allow me to change anything in the text fields, I assume that this is because the data comes from linked tables ?!

To update, do I need to create command and combined objects and program them in the usual way vb?

And if so, what is the syntax for referencing related tables when creating an update request?

In my broken form, I omitted the button there, and I can see the options to make it run the macro, run the code, etc. etc. which one is suitable?

many thanks,

The cop

+4
source share
2 answers

First, make sure the table is not read-only.

When you contact a remote table, Access will make it read-only if it cannot identify the primary key or other combination of fields to uniquely identify each row. Sometimes, but not always, he may ask you to specify which field to use as the primary key, if it is not defined.

But this question is easy to verify. Open the linked table directly in the Datasheet view and see if you can edit any values. If not, reinstall the table and find the option to tell Access about the primary key.

If the link is not read-only, make sure that the Allow Edits property is set to Yes.

Alternatively, you can try a simple form rather than a split form to determine if something about the split form is causing a problem.

+3
source

Decision:

MS Access barfs, when trying to register tables with a primary key of type BigInt, which is 8 bytes, Access can only process Ints of 4 bytes. Workaround below:

  • Drop constraint (bigint PK) in SQL table
  • Create a new primary key (int) with an identity seed
  • Link to a table in MS Access
  • Cancel the new constraint (int PK) and repeat adding the previous constraint (bigint PK) in MS SQL

Voila!

+3
source

Source: https://habr.com/ru/post/1392981/

More articles:


All Articles