Solved Exporting query freezes program (1 Viewer)

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
I would use something like

Code:
    Dim xlApp            As Object        'Excel.Application
    Dim xlWB             As Object        'Workbook
    Dim xlWS             As Object        'Worksheet
      
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Open(strSaveFile )
 
  • Like
Reactions: Tea

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:15
Joined
Feb 28, 2001
Messages
26,999
Sometimes it doesnt open at all, freezes the whole screen and the only way to get out of it is to close the database. Sometimes theres an option to restore the program but sometimes it opens after few 5-10minutes or not at all.

I'm joining this late because I've been distracted on a home project. My question is, when you get the option to restore the program, there is usually more in that kind of message than just "restore the program." The next time you get that message, can you take a screenshot or copy the exact wording of the message? This smells of corruption but the full text of that message would help to confirm or deny that diagnosis.

I didn't see whether you did this, but have you tried to do a Compact & Repair of this database?

I also didn't see the answer as to whether the DB is a split FE/BE database.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:15
Joined
Feb 28, 2001
Messages
26,999
Add one more thing to my question. If you open a query to SQL view, you can copy it to the Windows clipboard. Can you copy/paste the SQL of the query that is giving you the most trouble?
 

Tea

Member
Local time
Today, 17:15
Joined
Oct 30, 2020
Messages
51
I'm joining this late because I've been distracted on a home project. My question is, when you get the option to restore the program, there is usually more in that kind of message than just "restore the program." The next time you get that message, can you take a screenshot or copy the exact wording of the message? This smells of corruption but the full text of that message would help to confirm or deny that diagnosis.

I didn't see whether you did this, but have you tried to do a Compact & Repair of this database?

I also didn't see the answer as to whether the DB is a split FE/BE database.
Well there's no window that just pops up and tells me to restore, but when the database is not responding I try to close the database and THEN theres's a window for access is not responding (sometimes theres also another row Restart)

MS-Access-is-not-responding.png

Yes I've also tried compact and repair, that didnt help.
My DB is a split FE/BE, using SQL server as BE.
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
I think we need to ascertain if it's processing the records from the server, or the Excel export that's causing the issue here. How many records are returned? 1000, 10000, 100000 ?

Copy and paste the query results into a local temp table then perform your export from that table.
If it doesn't hang then I suspect the problem is the export from the query to the server getting hung up.

In which case my initial suggestion to move the query to the server as a view then export that.
 

Tea

Member
Local time
Today, 17:15
Joined
Oct 30, 2020
Messages
51
I think we need to ascertain if it's processing the records from the server, or the Excel export that's causing the issue here. How many records are returned? 1000, 10000, 100000 ?

Copy and paste the query results into a local temp table then perform your export from that table.
If it doesn't hang then I suspect the problem is the export from the query to the server getting hung up.

In which case my initial suggestion to move the query to the server as a view then export that.
around 2000 give or take, the copy from query alone made the database freeze and not respond, could this much record slow the performance of the database ?
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
No, I regularly export 10000's of records to excel in VBA without issue.
From your latest description, the issue is the query itself.

Please post up the SQL of it.
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
Okay - It appears that there is some not shown Excel formatting being done, that might be the real cause of the issue.

Please post up the full code, unless I am misunderstanding your PM?
 

Tea

Member
Local time
Today, 17:15
Joined
Oct 30, 2020
Messages
51
Okay - It appears that there is some not shown Excel formatting being done, that might be the real cause of the issue.

Please post up the full code, unless I am misunderstanding your PM?
No theres no code for the query, just the one i posted with outputto, the formatting was there already. I don't even know where it came from, when i export it to excel, the first header line is in gray and bordered and bold. There is no code to it.
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
Okay back to the original suggestion.

Your query doesn't look wrong or badly constructed at all, but does link to many tables.
As suggested create a view on the server and try using that to create your export.

The fact that copying the data to the clipboard causes issues, indicates it's actually a bigger overhead than you think.
 

Users who are viewing this thread

Top Bottom