Form becomes too slow to edit when recordsource has a join.

[Javier Hernández]

Hi again. I'm creating a form to introduce data. I'm not having trouble to add and edit control when the form recordsource is merely a select query on a single table, but as soon as I changed the record source to a query involving an inner join between two tables... it becomes almost impossible to edit. Even trying to move a control can take 5 - 10 seconds since the moment I click it until the moment it becomes ready to move. I have checked the Task Manager and I haven't seen a surge in the memory or CPU Access uses.



I thought the reason was the joined table being linked from a server so I created a local copy in my computer but performance didn't improve a single bit. Is it something common with Access? I guess I can work around it by attaching code to independent controls so they make the changes to the other table without including it on the recordsource but seems unecessary.

[orange]

Please show the SQL of the record source. What else can you tell readers about your application?

[ssanfu]

There are too many variables to give an answer: table structure, number of records, number of tables in the query,.....

I changed the record source to a query involving an inner join between two tables... it becomes almost impossible to edit.

Typically, this would be a case for a main form/sub form setup. The main form would be the "one" query and the sub form would be the many query.
(I always use queries for form record sources.)

Or you can open a form (the sub form in the previous case) with a filtered records source to limit the number of records.

[June7]

INNER join requires related records to already exist in both tables. If you want to add a new record in either through the query, can't.

Are you wanting to add/edit records in the 'many' side of relationship?

A query/form should edit only one table.

I agree with Steve, you should consider a form/subform arrangement or at least change the join type.

[Javier Hernández]

This is the recordsource which references a single table (CCE dummy):

SELECT [CCE dummy].folio, [CCE dummy].[FECHA DE MUESTREO], [CCE dummy].[HORA DE MUESTREO], [CCE dummy].Idlugar, [CCE dummy].Idmuestra, [CCE dummy].Idmuestra1, [CCE dummy].Idmuestra2, [CCE dummy].Idapariencia, [CCE dummy].Idolor, [CCE dummy].Idquienentregó, [CCE dummy].[FECHA DE ENTREGA], [CCE dummy].[HORA DE ENTREGA]
FROM [CCE dummy];

This is the recordsource if I establish a join with another table:

SELECT [Muestras dummy].CLAVE, [Muestras dummy].IdCliente, [CCE dummy].folio, [CCE dummy].[FECHA DE MUESTREO], [CCE dummy].[HORA DE MUESTREO], [CCE dummy].Idlugar, [CCE dummy].Idmuestra, [CCE dummy].Idmuestra1, [CCE dummy].Idmuestra2, [CCE dummy].Idapariencia, [CCE dummy].Idolor, [CCE dummy].Idquienentregó, [CCE dummy].[FECHA DE ENTREGA], [CCE dummy].[HORA DE ENTREGA]
FROM [CCE dummy] INNER JOIN [Muestras dummy] ON [CCE dummy].folio = [Muestras dummy].FOLIO;



The joint field, Folio, is an autonumber field in table [Muestras dummy] and it is indexed without duplicates in both tables. There is not a formal relation, but a row in table [Muestras Dummy] corresponds to exactly one row in table [CCE dummy]. Both tables store information regarding a sample: [muestras Dummy] stores information regarding the contract with the client while [CCE dummy] stores the technical data of the sample.

Now that I delve into the tables, I noted there is no primary key set on either table. Folio is the clear candidate for this. Can this be the cause?

[June7]

Both tables will always have a record for each folio? Why two tables? Are there more than 255 fields?

[Javier Hernández]

Sorry for the late response, I was filled with work outside my desk.
Both tables amount 213 fields; I'm not sure why two tables were created (I didn't create the database): my guess is that it was expected to work as a split database but in the end the split part wasn't necessary but the two table system sticked.
I get the idea that the Access may be handling it as a one-to-many query by design and that may be the reason why it is too slow. I will be avoiding the join for now and will advocate for the two tables to be merged into one.
Thanks for the help.

[June7]

The high number of fields and multiple similar name fields (Idmuestra, Idmuestra1, Idmuestra2) are indicators of a non-normalized data structure.