Search results

The compile errors were on the same PC. I decompile/compact&repair/compile this db occasionally with no problem. In troubleshooting I made a fresh accdb, imported all objects from db, and that is when compile errors start. One example is this code with compile error "Method or data member not...

Understood. The procedure looks different than what I do, and I will test it out today - but I think I'm already doing this. frmCloseDatabase launches in the background when db is opened, and is bound to backend tables, but I use a 60000 timer to check if my tblCloseDatabase is set to true...

Some bloat - sometimes small or large, and also because it seems to improve performance. I do think some attention needs to be paid to my queries.. Some days a query will run instantly and others the db will freeze for up to 20 minutes sometimes, and the underlying data is not changing that...

I will check that persistent connection procedure out.. I do something similar already with a kickout procedure that has a tblclosedatabase flag to shut down the db's.

Hello AWF, Trying to optimize some db's that have been in service for years, and have been struggling with speed, program not responding for 2-20 minutes, etc. Typically if speed is bad I will close sessions of the backend, compact & repair, and release it back to production. This usually speeds...

You may want to test this on a copy of the folder, or make a backup of the current folder, but below should do the trick if I am understanding correctly. Navigate to the folder with the files Click in the the address bar of File Explorer Type cmd, and press Enter. A command prompt window...

I'm thinking the same. Okay, I need to look into separating this update process. The batch process runs by 1 specific admin user that comes in earlier, and the purpose of the form opening while update process happens is essentially a filter. We are updating BOM/MRP data, but not bringing all of...

I will look to move to dCount instead! This is in a standard code module now. And I am calling the function from the batch process & the form. I am not sure if the logic is okay though. Below are current steps. 1. Batch process runs and calls UpdateData 2. Form needs to open to assign model...

That is great. I will look into the side ends also! Thank you. I will add though - my bloat happens in the backend, and all temp tables are in the front end, which does not experience the bloat.

So it sounds like a better way to handle it would be to remove the UpdataData call below on the frmModels_no_grp, and instead just open the frmModels_no_grp with ACDIALOG, update the models, and then just close that form, and it will continue with the original function call, instead of calling...

I had to split this up into 2 messages. if the frmModels_no_grp is opened due to needing to assign models, then the code on that form is - Option Compare Database Private Sub Closepage_Click() DoCmd.Close End Sub Private Sub cmd_Continue_Updates_Click() UpdateData DoCmd.Close End Sub So in...

I do use temp tables in this update function. This is the function below. Option Compare Database Dim db As DAO.Database Dim rs As DAO.Recordset, rs2 As DAO.Recordset Dim strSQL As String, strSQL2 As String Global seqNumber As Long Global lastcall As Date Function UpdateData() If...

Hello AWF, I'm experiencing a problem with a function - UpdateData() in my db, and hoping someone can tell me if I am missing something. I have an update procedure that runs each morning, and it usually takes 20-30 minutes. Recently it has been getting hung up and taking over an hour, and...

I use something like this Private Sub Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb() strSQL = "INSERT INTO ProjectsChangeLog ( ID, EditDate, ProjectID, RecordID, [User], Field, BeforeValue, AfterValue ) " _ & "SELECT ProjectsForm.ID, ProjectsForm.EditDate...

pcd_id is not unique in PCDzone. And the more I have looked at this, the more trouble I'm starting to think I am in. tblPCDzone is a junction table of all tblPCD.pcd_id and tblZone.zone_id to track PCD/Zone revisions. Could I use tblTask.stat_id to tblStation.stat_id(pk) in some way to get...

@Pat Hartman that makes perfect sense. I actually don't even need the tblPCDzone.pcdz_id -- tblTask.pcdz_id as I was joining by pcd_id to have a where clause tblPCDZone.pcd_id = " & pcd & ". I can just use the join tblZone.zone_id -- tbltask.zone_id to filter tbltask.pcd_id. My function is...

Pat, is it the double join in the query picture that makes it appear no RI? I thought it was being followed (not in that query example, in the db). Here is relationship diagram. I thought the structure was correct. I haven't explained this db much either - but basically it is a process...

The function is called from the form. The initial query/picture I provided is the first strSQL in the function. Private Sub Form_Load() Dim pcd As LongPtr pcd = [Forms]![frmPCDcont]![pcd_id] Call sort_time_color(pcd) End Sub and the forms recordsource is a different query. SELECT...

@Isaac that is not my goal. Is doing updates on related multi-table queries, WITH depending on bound forms and subforms okay? I thought that was what was happening. And I'm not trying to update records in more than one table at once - I would never even attempt. I was kind of surprised though...

Good feedback. I inherited this db is the short answer. The bound form does have two subforms. I'm understanding what you are saying.. I will have to work to apply that to the sql/vba mix of opening a recordset and moving through it.. etc. So I should always try to have only one join (if...