2

I'm having some trouble in Access 2002...

I have two tables, one containing around 60k occurrences and one with a column and the foreign keys to make the join. In my form, I set the source to a query with these two tables joined (left join on the empty one). Basically, I end up with my 60k occurrences and my new column at the end.

Now, I need to allow my users to edit this field in my form. I found out that when the corresponding data exists in my empty table, I can edit the field just fine, however since we need this empty table to contain only the occurrences where we need to add the new column, I can't simply make a new entry for all of my occurrences.

Here are a schema of the two tables:

Table 1:

ID    Sequence    Col1    Col2    Col3    Col5
60k

Table 2:

ID    Sequence    Col6
0

And my query:

SELECT tblOne.*, tblTwo.Col6
FROM tblOne 
LEFT JOIN tblTwo ON (tblOne.Sequence=tblTwo.Sequence) AND (tblOne.ID=tblTwo.ID);
HansUp
  • 95,961
  • 11
  • 77
  • 135
Estarius
  • 1,219
  • 3
  • 13
  • 24

2 Answers2

2

If you're willing to consider a different approach, this could be easier with a form/subform approach.

Base the main form on tblOne and base the subform on tblTwo. Use Sequence and ID as the master/child link fields (you can find that setting in the property sheet of the subform control).

With that design, the subform will display existing tblTwo rows which match the main form's current tblOne row. And you can add a new matching tblTwo row at the subform's new record --- it will "inherit" the Sequence and ID values of the current main form row.

By the way, Sequence is a reserved word. Rename that field if possible. If you must keep that name, you can avoid the risk of confusing the db engine by enclosing the name in square brackets or by qualifying the field name with the table name (or alias) in your SQL statements.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • The sub-form seems to work, however do you know anyway to limit the number of entry to 1 ? The application detects that the primary keys can't be duplicated so it block the new entry, however, since it doesn't remove the new entry, it keeps saying there is a problem with the index keys whatever the users do... Basically, I want to automate the comportment of the 'Escape' key when this happens. – Estarius Sep 20 '13 at 15:08
  • I don't understand what you're asking now. My best guess is you want a unique index on the combination of `Sequence` and `ID` in `tblTwo`. – HansUp Sep 20 '13 at 15:13
  • It's okay, found a way lol... I simply hide the "new" entries field, that way they can't misclick the field. – Estarius Sep 20 '13 at 15:31
1

If you have a query that is not updateable, check out Allen Browne's Tips on what might cause this: http://allenbrowne.com/ser-61.html

MS Access has a shortcoming. If you wish to edit data in list-like views, it generally works best for it to be displayed in basically the same structure that it's stored in your tables. Edit: In reference to the comment left by Yawar about this not being an Access shortcoming, I'd like to point out that when developing in .NET it isn't uncommon to have a database structure that is quite unlike the data model classes used inside your application. In this case the GUI is built on the data model so the database may look somewhat (or event quite) different from your data models/GUI.

Back to MS Access, when you use a table join to create the recordsource/recordset for a datasheet form or continuous form, it's my understanding that only one of the tables is going to be updateable. In other words, only one side of the join is updateable. And in many cases, the recordset is not updateable at all, due to the DAO engine being confused. Update: I have deduced from the link below that what I wrote above seems to be more true of SQL Server than using JET/ACE backend.

The most common solution is as HansUp has suggested, use a form/subform approach. You can actually have a datasheet subform as a child of another datasheet subform, which would work quite well in your case here. There will just be an expandable plus sign at the far left of each record so you can add/edit/delete the record(s) in tblTwo.

Another option is to use an ActiveX grid control such as the iGrid from 10tec which means you'll write quite a bit of code for all kinds of things, like loading the recordset, writing changes/additions/deletions back to the database, handling formatting of cells, etc.

Yet another option is to use a fabricated ADO recordset. This is a terribly clumsy approach and I can't say that I've really seen it in use, mostly just experimented with it and read about it in theory. The problem is that you have to create a fabricated recordset that is nearly identical to the one that you generated, and then you have to loop through and copy all of the records from the generated recordset into your fabricated recordset. It's a tremendous amount of overhead, especially for that many records. And then, you must write code once again to write all additions/changes/deletions back to the database. Handling the creation of new primary keys can be tricky. This particular approach is not easy or simple and is not something I recommend a VBA beginner tackling.

If you're using SQL Server you should check out the following article at Microsoft's website. It covers a variety of material including updating multiple tables from a single recordsource/view. http://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx

HK1
  • 11,941
  • 14
  • 64
  • 99
  • I wouldn't necessarily call this an Access shortcoming. The SQL standard itself is very convoluted when it comes to defining when exactly a view (i.e. query) is updateable (editable). – Yawar Sep 19 '13 at 23:49