4

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

How do I go about updating the record?

Currently, it will not let me change anything in the text boxes i assume this is because the data is coming from linked tables?!

In order to update do i have to create command and coonn objects and program it in the usual vb manner?

And if so, what is the syntax for referencing the linked tables when creating the update query?

On my split form I dropped a button on there and I can see options to make it run macro, run code, etc etc, which one is suitable?

many thanks,

KS

Perplexed
  • 877
  • 2
  • 19
  • 32

2 Answers2

3

First make sure the table was not linked read-only.

When you link to a remote table, Access will make it read-only if it is unable to identify a primary key or another combination of fields to uniquely identify each row. Sometimes, but not always, it may ask you to tell it which field(s) to use as the primary key if it is uncertain.

But this issue is simple to check. Open the linked table directly in Datasheet View and see whether you can edit any values. If not, re-link the table and look for an option to inform Access about the primary key.

If the link is not read-only, make sure your form's Allow Edits property is set as Yes.

Also you could try a simple form rather than a split form to determine whether something about the split form is causing the problem.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thanks, I will give that a try - I do recall Access prompting me for primary keys but I cancelled as the tables do not have primary keys. I will have to see if I can alter the tables and put in the primary keys as identity seeds. Will this enable Access to automatically put in the updating , adding functionality? Does this also work for joined queries etc? thanks again. – Perplexed Jan 25 '12 at 16:34
  • OK, that sounds like the first culprit, but there may be other issues we haven't yet identified. Re-link the table (delete existing link and add it anew) after you modify its structure, then verify you can edit the table's data when you open it directly from the Access link. – HansUp Jan 25 '12 at 16:46
  • Whether or not queries are editable depends on the nature of the query. Often, but not always, the fields on the "many" side of a query which uses a one-to-many join can be edited although those on the "one" side can not. The best I can say now is try your query. If it is not editable, but you need it to be, post the SQL in a new question and we may be able to help you find a suitable alternative. – HansUp Jan 25 '12 at 16:50
  • Thanks, I'll get back to you on that. – Perplexed Jan 25 '12 at 17:22
  • ok, so I added the ID to the tables, however when I open the tables in Access, all the column fields have the value of "#deleted". Why is this? – Perplexed Jan 25 '12 at 18:09
  • Did you delete the previous link to that table after changing it's structure, then create a new link for that table? – HansUp Jan 25 '12 at 18:12
  • Yes, I did.Ok, so the ID I added is not an identity seed - should it matter whether or not a primary key is an identity seed or not? – Perplexed Jan 25 '12 at 18:17
  • No, I don't think it has to be identity type. But I can't understand why all the values are "#deleted". I'm perplexed now, too. – HansUp Jan 25 '12 at 18:55
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7055/discussion-between-perplexed-and-hansup) – Perplexed Jan 25 '12 at 20:52
3

Solution:

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

  1. Drop the constraint (bigint PK) in SQL table
  2. Create a new primary key (int) with identity seed
  3. Link the table in MS Access
  4. Drop new constraint (int PK) & Re-Add your previous constraint (bigint PK) in MS SQL

Voila!

Perplexed
  • 877
  • 2
  • 19
  • 32