Solved Sub Form suddenly not allowing additions - Source Query not allowing additions (1 Viewer)

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Good afternoon,

I have a form with a nested sub form to create delivery notes

The parent form links to a single table and creates a delivery note number, logs the customer and the date.

The sub form is based on a query that contains 2 tables, both tables have the same data in a field in each, table 1 "Job No" and table 2 is "insightJobNo"

The join is correct and has always been include records which match both tables. I have altered this to include all from table 1 and matching from table 2 but this has made no difference.

There are no calculated fields in the tables - this is all done on the forms.

This has happened previously and deleting and replacing the users front end file has sorted it in the past - not this time though!

I have an archive version of table 1, which every so often I archive records from table 1 into the archive in order to keep the size of table 1 down to around 12000 records.

I have archived today and this issue seems to have started after this, but it's never been an issue before.

If i create a new query with table 1 and table 2 the recordset is not updatable deleting either table from the query and the table is obviously then updatable.

Any clues where on earth to start diagnosing this one?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
27,003
OK, you did some correct things for sure. Replacing the FE with a clean copy thereof was right. Testing that a new query shows the same behavior is right. But the significant clue is that removing a table from the offending query (thus making it a single-table query) doesn't help. In this case, my first guess would be corruption of some sort. Access databases have that problem now and then. Too many causes to enumerate, so let's just go about possible fixes.

Make a copy of the BE file before proceeding, since you don't want to lose data. Then perform a BE Compact & Repair. See if that helps. If it does, IMMEDIATELY make another copy of the (now repaired) BE file as a spot backup. This is a relatively cheap fix, you just have to get everyone out of the DB to implement it.

If that C&R doesn't help, the next step would be to create a new (empty) database file and attempt to import all tables (structure & data) from the BE. There should not be anything else in the BE. This will be more tedious and again you need folks to keep out of the DB.
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
OK, you did some correct things for sure. Replacing the FE with a clean copy thereof was right. Testing that a new query shows the same behavior is right. But the significant clue is that removing a table from the offending query (thus making it a single-table query) doesn't help. In this case, my first guess would be corruption of some sort. Access databases have that problem now and then. Too many causes to enumerate, so let's just go about possible fixes.

Make a copy of the BE file before proceeding, since you don't want to lose data. Then perform a BE Compact & Repair. See if that helps. If it does, IMMEDIATELY make another copy of the (now repaired) BE file as a spot backup. This is a relatively cheap fix, you just have to get everyone out of the DB to implement it.

If that C&R doesn't help, the next step would be to create a new (empty) database file and attempt to import all tables (structure & data) from the BE. There should not be anything else in the BE. This will be more tedious and again you need folks to keep out of the DB.

Thanks for the quick reply.

We are having a number of instability issues as the moment which I'm slowly trying to resolve. Our main table for example for some reason no longer had a PK even though we have a PKfield which was an autonumber. This also had a duplicate number for some reason which i have resolved and reseated the PKfield as a PK.

C&R has not worked in this case.

When I delete one of the tables from the query this makes the record set updateable - it is only when they are both there that it is read only. Having just a single table in the query is fine which points me to something about the join or relationships?

Footnote: This database has been running and continuously developed for around 15 years with liited access and VB knowledge. It's messy!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
27,003
OK, here is a handy little article that might help.


The general rule for these kinds of problems is to ask "What changes did you last make before the problem showed itself?"

"Number of instability issues" is also a potential source of clues. Can you be more specific as to what you mean by that?

The loss of a PK is something that doesn't happen by itself very often, but if you have something that has "hosed" the table or the PK's index, perhaps due to corruption of some type, then this problem could be related to your instability.
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Well, we've changed quite a bit over the last few weeks.

-New debian server with SMB share (moved away from an old NAS that was serving this)
-Added new users which use Office 2021

The instabilities do not seem to have any theme to them. We now reasonably regularly have a unknown database format, records are deleted after just being entered and one user added 3 records to the main table today that simply vanished without trace - Most of these must have something to do with the record locking information and I'm hoping re-establishing the PK in the main table will resolve some / most of these.

But, right before this particular problem occurred was just the append / delete query to shift records to the archive table. Rolling back to an backup of the BE resolves the problem, so it must be something to do with the table joins.

Code:
SELECT [Job Register and Report Log].PKFLD, [Job Register and Report Log].[Job No], TblDeliveryDetails.InsightJobNo, [Job Register and Report Log].[Drawing No], [Job Register and Report Log].[Pattern Number], [Job Register and Report Log].[Job Number], [Job Register and Report Log].[Melt Number], [Job Register and Report Log].[Serial No], [Job Register and Report Log].[Order No], [Job Register and Report Log].[Report No], TblDeliveryDetails.DeliveryDetailID, TblDeliveryDetails.DeliveryNoteID2, TblDeliveryDetails.Description, TblDeliveryDetails.SerialNumber, TblDeliveryDetails.OrderNumber, TblDeliveryDetails.ReportNo, TblDeliveryDetails.JobNumber, [Job Register and Report Log].Description, TblDeliveryDetails.Quantity, [Job Register and Report Log].[Failure code], TblDeliveryDetails.Notes, [Job Register and Report Log].Method, [Job Register and Report Log].Location FROM [Job Register and Report Log] INNER JOIN TblDeliveryDetails ON [Job Register and Report Log].[Job No] = TblDeliveryDetails.[InsightJobNo];

We use a combo box to select the required record from "Job Register and report log" and add the record to "TblDeliveryDetails"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
27,003
The "unknown database format" is the mark that a corruption just happened. Lost records after an "unknown format" event are consistent. The general mechanism is this: Access starts to make a change due to an update, append, or delete. If the records being changed involve a formal relationship then relational integrity kicks in, and if it is AUTOMATIC RI (i.e. cascade deletes allowed) then other actions might need to happen. Or it might just be that an index has to be updated.

So Access is doodling along on its merry little path and suddenly loses connection to the BE file. BUT the file update was being performed by the FE using SMB to manipulate the BE. If multiple records are involved or if multiple tables are involved or if a table and indexes are involved in what is being written, they might be in separate disk buffers. If the update has started but hasn't finished yet and you suddenly lose the SMB connection, you now have a corrupted DB because it was HALF-updated. That means its table structures are no longer self-consistent. And even if it was a momentary drop, the problem with SMB is that it is one of those protocols where transactions are internally numbered as a way to know if you have lost a packet. Restarting the connection is generally not possible, so the update that you needed to perform is now trapped in a disk buffer for a failed (disk) update sequence.

Behind the scenes, the data in a table has to be updated, so that means writing new records to replace the old ones. (You don't update in-place because of the chance that you might have to do a roll-back.) So that means that whatever defined the locations of each record in a table could suddenly be garbage such that the tests for self-consistency would fail. And that is how you lose records or get an "unknown format" error. In a system heavily dependent on pointers to maintain consistency, if you trash the pointers, you have totally lost consistency.

The new users should not make a difference but the change in servers might. The record-locking issues in Access are incredibly important. I don't want to point fingers at your new Debian server, but I will tell you that Access does not play well with things that don't manage SMB too well. It might be a costly experiment, but is there any way you can get back to the NAS disk as a test? Or can you have your IT folks check the statistics on the Debian server to see if it has been dropping connections?
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Indeed, I had come to the same conclusion that the server setup is most likely the reason we are having so many issues and do plan on moving the BE on to a NAS to confirm. I guess I'll need to do this sooner rather than later.

I can't see how this is going to resolve my current issue though?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
Try rebuilding the BE. Export all the data to .csv files. Create a new db by importing the structure, no data, from a backup. Make sure all the relationships are recreated. Make sure all the indexes are defined. Then import from the .csv file.

The shortcut is to just import the tables from the original. You could try that first. It may work. Just make sure that you end up with all the relationships and indexes you need.
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Thanks Pat, although there are over 150 tables and and the relationship view looks like a team of spiders with ink on their feet and slid across the screen :ROFLMAO:

I just can't get my head around why this query is non updateable. I've had a look at the article The_doc_Man suggested and nothing jumps out at me to be the issue.

I realise I'm not being very accommodating here - There is a lot of data to lose in this DB.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
Then just try the second method first. It may work.

Also, create a new FE and the same query. Is it updateable in the new FE? If it isn't, then that points to corruption in the BE. You need to force Access to rebuild it. The reason I suggested the long method is because I don't know if the indexes are imported or rebuilt using the second method and I want to force them to be rebuilt. C&R should rebuild them. Do the copy first, then do a C&R.
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Gotcha, So I created a new DB, imported the tables did a C&R and created the query with the same net result, not updatable.

I can manually add the information to the second table and everything seems correct on the produced reports.

So each table is independently updatable, but when I pull the info together in a query the set isn't editable. Is there a way to check for the corrupted data, or is it not as simple as that?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
OK, then delete the data fromtwo tables from the new version. You'll have to deal with the relationships separately and re-add them later. Compact. Then export the original data to .csv and import for just those two tables. You can append the existing autonumbers if there are relationships dependent on them or you can generate new ones if there are no relationships. We are trying to excise any corruption which will usually be in the indexes.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
27,003
is it not as simple as that?

Since Microsoft didn't give us insight into the internal structure of an Access DB file and didn't tell us what their code really does, I am sad to say "it is not as simple as that."

Pat, I think if you just import a table with structure and data, you DO rebuild the indexes, though I'm going to guess (for reasons as stated in the first paragraph of this post) that the import does one row at a time and, with indexes enabled, updates the indexes one index-entry at a time. Your .CSV method into a table without indexes, followed by adding the indexes, might be SLIGHTLY faster because there would be slightly less pointer thrashing. Since in either case it should be a one-off, the speed issue is immaterial, but I seem to recall that adding an index after the load is more efficient than adding the index to the infrastructure first and THEN populating the table.

@Morphies - you have now mentioned a query several times. Can you perhaps show us the query? EDIT: I see you DID provide us with the query.... never mind.

I have another question, though. You said you did some other DB work recently. By any chance was one of the actions to add a relationship that didn't exist before the problem started and that involved a logical relation path between the two tables in the query? This question exclusively relates to why the query won't update.

As seeing more frequent instabilities with "unknown database format" - having a non-updateable query won't do that ... because the query doesn't run, so CAN'T have done anything. It never made it far enough to damage anything. I will state categorically that your query cannot muck your database format. But a mucked database format certainly could interfere with your query. Still, the fact that you CAN build a version of the query that does update when you drop one of the member tables (and you say it works if either table is dropped) makes me think that this is a relationship issue of a subtle kind.

Earlier you said "Rolling back to an backup of the BE resolves the problem, so it must be something to do with the table joins." If it is resolved by using the backup, look at what is different between the backup's relationships and the current iteration's relationships. You have a before-and-after snapshot to help you figure out specific changes.

EDIT:
Code:
SELECT [Job Register and Report Log].PKFLD, 
             [Job Register and Report Log].[Job No], 
             TblDeliveryDetails.InsightJobNo, 
             [Job Register and Report Log].[Drawing No], 
             [Job Register and Report Log].[Pattern Number], 
             [Job Register and Report Log].[Job Number], 
             [Job Register and Report Log].[Melt Number], 
             [Job Register and Report Log].[Serial No], 
             [Job Register and Report Log].[Order No], 
             [Job Register and Report Log].[Report No], 
             TblDeliveryDetails.DeliveryDetailID, 
             TblDeliveryDetails.DeliveryNoteID2, 
             TblDeliveryDetails.Description, 
             TblDeliveryDetails.SerialNumber, 
             TblDeliveryDetails.OrderNumber, 
             TblDeliveryDetails.ReportNo, 
             TblDeliveryDetails.JobNumber, 
            [Job Register and Report Log].Description, 
            TblDeliveryDetails.Quantity, 
            [Job Register and Report Log].[Failure code], 
            TblDeliveryDetails.Notes, 
            [Job Register and Report Log].Method, 
            [Job Register and Report Log].Location 
FROM [Job Register and Report Log] INNER JOIN TblDeliveryDetails 
           ON [Job Register and Report Log].[Job No] = TblDeliveryDetails.[InsightJobNo];

Nothing jumps out by looking at the query itself. In the relationship diagram, looking at your tables [Job Register and Report Log] and [TblDeliveryDetails] and their fields in this query, I see a LOT of fields that involve xxx-number or xxx-no. Makes me think you have a lot of potential for other relationships too. What I want you to look for is whether there is ANY OTHER path between the two tables, perhaps indirectly through some intermediate table. Multiple paths involving two tables will hose up query update abilities. Also, are ANY of the fields based on a table-level LOOKUP field. (Form-based lookups don't have the problem I'm considering.)
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
@The_Doc_Man I wasn't looking for speed. I was looking to ensure that the indexes were rebuilt from scratch.

If an old version of the BE still works, the corruption is definitely in the BE so, importing the non-broken tables and just rebuilding the two tables from scratch should work but you have to deal with relationships and remove them first and add them back at the end.

You already said that you'd had an issue with the autonumber failing. Did that affect either of the two tables in the join?

Also, there was a bug, that may now be fixed where the autonumber would corrupt if you were not using it as the primary key. So, if you had a table where the PK was CustomerName but the table had an autonumber column just for decoration, when you compacted the database, the database rows would be in CustomerName order rather than autonumber order and apparently MS was using the last autonumber rather than the max autonumber to generate a new autonumber and that was causing duplicates to happen.

I have also seen the autonumber corrupt if you have both a main form and a subform that are bound to the same table. This doesn't happen with a split form. Perhaps because the split form was developed after the other autonumber bug had been fixed.
 
Last edited:

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Thanks for the replies - I at home now but still have RA.

So, looking at the backup and the current BE files on the backup one relationship is listed as one to many whereas on the current DB it's listed as indeterminate but I don't see a way to change this. I have tried to recreate the relationship but it still states indeterminate.

Edit:

Changed the index type of [Job Register and Report Log].[Job No] to indexed no duplicates (as this is actually correct for this field) and the relationship is back to one to many and the form works again.

What a mare.

TYVM for all of your help once again.

PS: anyone interested in paid work to change this to SQL backend and redo the frontend? Please PM me :)
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
Are you sure the PK is set correctly? Relationships should be data field (the FK) to the PK in some other table.

Access only says indeterminate if the link is data field to data field.
 
Last edited:

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Are you sure the PK is set correctly. Relationships should be data field (the FK) to the PK in some other table.

absolutely not sure at all. The table I changed to indexed is the same table I mentioned earlier that has an auto number field that used to be the PK, that then wasn't the PK as the table didn't have one assigned. So i reset it as the PK.

To be honest that is the first table that was put in to this DB and the PKfield (autonumber) isn't really used anywhere else.

The job number which is xx/xxxxx is the unique field and identifies the record.

To top that this particular table is literally the heart of the data.

needs a redesign / rebuild.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
Is the autonumber used in the joins to the other tables? If it isn't then you might as well delete it. Perhaps that old bug is back. NEVER have an autonumber in a table if it isn't going to be the PK. When I have a natural key that I need to be unique, I make the autonumber the PK and create a unique index on the field or fields that define the natural key. In Access, it is always easier to have the PK as an autonumber rather than a multi-field PK. Also having the autonumber be the PK gives you flexibility should the unique identifier defined for the business rules change.
 

Morphies

Member
Local time
Today, 15:14
Joined
Dec 8, 2021
Messages
30
Without digging around I'm not entirely sure. I don't believe so but if I delete it i'll have to amend a shed load of other queries, snippets of code and god knows what else that has been done to it over the years.

Currently much easier to leave it be.

I'm fast falling out with the current solution we have. the DB was first set up in 2004 so is around 18 years old, has been developed over this time by a couple of people (myself included) that do not really know the fundamentals of DB design nor how to implement a scalable solution.

It is however central to our business and controls everything from quotation through quality, calibration, maintenance, methods, qualifications, certification, invoicing and finance so it's incredibly difficult to find an off the shelf solution that caters for our particular needs.

We also employed a line of though that when we had a non conformance, for whatever reason, could we eliminate that possibility by changes in the DB. Most often the answer was yes, and we could implement and distribute a solution within the DB and carry on. This means the DB contributes significantly to first time right for the entire business.

I think my preferred options would be to redesign and rebuild the BE in MySQL and have a web based FE but this is well beyond my skillset and would require time I simply don't have.

Anyhow, for now the delivery note issue is resolved, albeit a bush fix but I'm employing the "is it broken? No? Leave it alone" stance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Feb 19, 2002
Messages
42,986
Without digging around I'm not entirely sure. I don't believe so but if I delete it i'll have to amend a shed load of other queries, snippets of code and god knows what else that has been done to it over the years.
just delete the autonumber property and set the field to not required.

I think my preferred options would be to redesign and rebuild the BE in MySQL and have a web based FE but this is well beyond my skillset and would require time I simply don't have.
Estimate the amount of time it has taken to build the current app and multiply by 10.

Unless you actually need a web app, why not stay with something you know works. No other platform will be as responsive to changes as Access is. The key is having a solid foundation.
 
Last edited:

Users who are viewing this thread

Top Bottom