query

Copper Contributor

I have an access application that runs fine on my laptop but on my desktop it hangs up (unresponsive) when I query field that then should bring in all associated data.  It works on a number of computers, but not my main desktop.

 

Windows 10

Microsoft Office 365 Home

 

11 Replies

@Stephan7754 

 

Is the accdb located on the same computer (e.g. on a shared network folder, or on the laptop) and only opened from different computers?

Do you have anti-virus software on the desktop computer than might be interfering?

 

Anything else different? Windows 10 on both computers, MS Office versions the same?

The file is stored in a one drive folder, so both computers access it from the same place...  In fact it was pushed to the one drive folder from the desktop and then later accessed it from the same folder on the Surface notebook.

 

Both computers are using only Microsoft's anti-virus

 

All versions are the same, all kept up to date.... 

@Stephan7754 

 

"...The file is stored in a one drive folder,..."

 

Just so you know, running accdbs which reside in a OneDrive folder is generally considered a high risk approach. You ought to look into a more standard shared location on your network.

 

Also, it sounds like this is not a split accdb relational database application because you refer to "it" Another unsafe approach. Sharing of a single accdb (with both tables and interface objects in it) is another high risk practice that is regularly associated with corruption of data.

 

I suggest you invest some time in learning more about standard ways to design and deploy relational database applications, which is done with the following general guidelines.

 

Split the relational database application into two files, one containing ONLY the tables with your data and one containing the remaining interface and logic--forms, reports, queries and VBA. The data file, usually referred to as the "Back End", is stored in a shared folder on your LAN (and again, OneDrive is considered an unsafe location for Access relational database applications in production). The interface and logic file is usually referred to as the "Front End". COPIES of the Front End, or FE, are deployed onto each user's computer and linked to the Back End, or BE. That way the data is shared, but each user has their own copy of the interface. If something goes wrong in any one of those FEs, it doesn't interfere with others.

 

It's hard to know if the sharing arrangement here is the root of your performance problem (two people using one single accdb on OneDrive), but I will venture to suggest it's quite possible and unsafe.

@George Hepworth 

 

I have had a Access database built for my personal and family's use.   It's a way to manage a remodeling project.  My issue is that I would like to run it on my desktop but it fails when I select a vendor on the invoicing screen.  Access just crashes.  

It runs fine on my old Surface notebook, but crashes on my new Alienware desktop.

Both run windows 10 and Microsoft 365.  Both are at the same build level.

 

I've reinstalled both windows and 365 on the desktop assuming there was something wrong as received from Dell, but it didn't resolve the problem.

 

The file is the same, I've copied it between the computers, downloaded from One Drive, tried many methods, it's not the file.

 

https://1drv.ms/u/s!AhEMxc4LabRjitQpDkpar8oUnr63lw?e=GCGwCD

 

Here is a link to the file if you want to try...

 

Thanks if anyone can help.

 

 

@stephantmi54 

 

The VBA in the mdb is password protected. That makes it impossible to trouble-shoot. 

Perhaps you can provide the password? Or a version of the mdb without a password?

@stephantmi54 Hello

Can you give some info on how exactly your application hangs ?

I opened it and it seems "fine" although i didn't examined its functionality

From a quick glance nothing out of ordinary except a little too much code which i am unsure how you use...its not clear at first look

Your references looks good so no problem on this...after all everything is handled by macros (a lot)

@tsgiannis 

 

It hangs when in invoicing you click the drop down to select a vendor... the app hangs and crashes...  I can create a project, add vendors, add PO's but when attempting to add an invoice, a crash... again only on my desktop.  On my old notebook it works fine... 

 

I did check and found that the desktop is Windows home, version 2004 and the notebook is windows pro version 1909...  The desktop is a new Alienware Ryzen 10, the notebook a older Surface Pro 4.

@stephantmi54 

Do you have any events on that combo box?  Please show us the code?  Perhaps try a different approach.

 

Have you tried a Decompile/Compact and Repair/Compile/Compact and Repair?
Have you tried creating a new blank database and importing everything, then compiling to ensure there are no issues?

@stephantmi54 i think i saw it

With Rec3

For i = 0 To Rec.RecordCount - 1
.AddNew
Rec3.Fields("ID") = Rec.Fields("ID")
Rec3.Fields("Amount") = Rec!Amount
Rec3!Remaining = Rec!Amount - Rec!Invoiced
Rec3.Fields("Vendor") = Rec!Vendor
Rec3!Description = Rec!Description
Rec3!ID = Rec!ID
Rec3!Original = 1
Rec.MoveNext

Next i
.Update

End With

 

The problem for start is that Rec recordset is empty to begin....you have criteria which you don't supply values so it bombs out

Also .Update should be inside the loop not outside...at least to my knowledge (never used it otherwise)

Adding a simple if .Recordcount>0 then would resolve it

 

 

With Rec3

If .RecordCount>0 then

For i = 0 To Rec.RecordCount - 1
.AddNew
Rec3.Fields("ID") = Rec.Fields("ID")
Rec3.Fields("Amount") = Rec!Amount
Rec3!Remaining = Rec!Amount - Rec!Invoiced
Rec3.Fields("Vendor") = Rec!Vendor
Rec3!Description = Rec!Description
Rec3!ID = Rec!ID
Rec3!Original = 1
Rec.MoveNext

Next i
.Update

End if

End With

 

P.S. if someone can tip the admins of this Community that "Insert/Edit Code Sample" is not working would be much obliged

@tsgiannis

 

I tried this plus other ideas and I still fail selecting the vendor in the invoicing screen.  It must be a bug in my desktop windows or Access… it runs on all other PCs that I’m aware of… did you get the same crash or did it pull up a vendor in the invoicing screen?

 

 

 

I didn't had any issues after the "fix"...no i didn't had crashes in the first place...you should open the application bypassing the executing code (SHIFT) and run the forms...if an error is popped just do what is needed to fix it