Solved Exporting query freezes program (1 Viewer)

[Tea]

Hello,
I've searched this problem everywhere, but couldnt find any solution, that would help my case...
I have a form, where in a frame with the option boxes i can choose either out or in data, by choosing the selected option i then click on a button, that has a code to export the data to excel.

Dim strQry As String
Dim strAgent As String
    If (srchFrame = 1) Then
    strQuery = "qry_in" 
    Else
    strQuery = "qry_out"
    End If

DoCmd.OutputTo acOutputQuery, strQuery, acFormatXLSX, , True

These queries have at least 5 tables each and out qry has at least thousand records.
When i click on the button, the window for saving the file to my directory path appears, but after clicking on save, the screen freezes, access is not responding and the only option is to close the whole program, sometimes when i try to restore the program it loads for 15minutes and sometimes the excel appears but mostly it doesnt and have to restart the whole thing. Using SQL server as backend.

How can i solve this problem?

I would appreciate any suggestions.

 

[Minty]

How quickly do the queries run in the database window.
Open the largest one then click to get to the last record. Is there an obvious delay?

You may find it better to create a view on the server that replicates the query output, and simply link to that an open that as your output.

 

[Gasman]

If I use your outputTo code it works fine for me and opens the file in Excel?
I have always sent output directly to a file.? Have you tried that as a Debug step at least?

 

[Tea]

If I use your outputTo code it works fine for me and opens the file in Excel?
I have always sent output directly to a file.? Have you tried that as a Debug step at least?

Well I put True to open the excel right after i exports to an excel file, if u meant this. But this is a shared database so usually it exports to the default file where the user saves it himself, thats why theres no direct file to save the excel.

 

[Gasman]

Well I put True to open the excel right after i exports to an excel file, if u meant this. But this is a shared database so usually it exports to the default file where the user saves it himself, thats why theres no direct file to save the excel.

Ok, but as a test, is it any different if it just writes to a file and still set to True?
What happens if True is not set with and without a file.?

These are all steps I would take to try and isolate the problem?

Eg, on my system it works fine, but then my tables are small.?

Try an office repair even?

 

[Minty]

In addition to what @Gasman is suggesting, and my questions, make sure any existing file of the same name is closed, or deleted before trying to recreate it.

 

[Tea]

Ok, but as a test, is it any different if it just writes to a file and still set to True?
What happens if True is not set with and without a file.?

These are all steps I would take to try and isolate the problem?

Eg, on my system it works fine, but then my tables are small.?

Try an office repair even?

Thats not it ... tried to use a direct file and path, but still doing the same problem also when set as false for auto opening the file.
Office repair?

In addition to what @Gasman is suggesting, and my questions, make sure any existing file of the same name is closed, or deleted before trying to recreate it.

Everything is closed and when I try to save it, i save it under a different name or to a different path, so that's not the problem also..

 

[Minty]

Okay - back to my initial questions then.
How long does the query take to open and then how long to get to the last record?

 

[Tea]

Okay - back to my initial questions then.
How long does the query take to open and then how long to get to the last record?

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. If it opens then usually takes 15minutes minimum if i had to stopped the time for it. But longest maybe 45minutes... If i open the query alone and not on the form where i have my code then it takes less then a minute...

 

[Minty]

Okay - a minute is a long time to open the query.

What about getting to the last record after it has opened.

 

[Tea]

Okay - a minute is a long time to open the query.

What about getting to the last record after it has opened.

You mean the query in the database or when its exported to an file? When i scroll to the last record, how long does it take or how many records are there?

 

[Minty]

If you open the query in the database window, then move to the last record.
(Don't scroll simply use the last record button in the navigation controls at the bottom of the query.)

How long does that take?

Or how long does it take to populate the total number of records indicator in the query window, that is a good indicator of slow processes?

 

[Tea]

If you open the query in the database window, then move to the last record.
(Don't scroll simply use the last record button in the navigation controls at the bottom of the query.)

How long does that take?

Or how long does it take to populate the total number of records indicator in the query window, that is a good indicator of slow processes?

Not much, 2-4 seconds? Give or take, not longer. Opening the query itself is fast but when trying to run the query in form takes long.

 

[Minty]

Check that you haven't got any hidden Excel processes in the task manager. (Not in the top section, in the bottom process section)
Sometimes when another app opens the excel process it gets left behind if there is a crash.

Other than that I would maybe look into a slightly better method of exporting - I nearly always use Transferspreadsheet as it seems very fast.

 

[Tea]

Check that you haven't got any hidden Excel processes in the task manager. (Not in the top section, in the bottom process section)
Sometimes when another app opens the excel process it gets left behind if there is a crash.

Other than that I would maybe look into a slightly better method of exporting - I nearly always use Transferspreadsheet as it seems very fast.

Task manager in Access? (where to find?)
The problem at transferspreadsheet is that i have to use a file i want the data to transfer to, which is not an option for me as mentioned above, that is a shared database and everybody saves it to their own directory.

 

[Minty]

No the windows task manager.

As for the transfer spreadsheet File - use a filepicker or simply save it locally use something like

DIm strSaveFile as String

strSaveFile =   Application.CurrentProject.Path & "\" & qryName & "_" & Format(Date, "yyyymmdd")) & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, sRange, True

 

[Minty]

Something you have said just makes me ask the question, is the database split, each user having their own front end?

 

[Tea]

No the windows task manager.

As for the transfer spreadsheet File - use a filepicker or simply save it locally use something like

DIm strSaveFile as String

strSaveFile =   Application.CurrentProject.Path & "\" & qryName & "_" & Format(Date, "yyyymmdd")) & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, sRange, True

Dim strQry As String
Dim strSaveFile As String
    If (srchFrame = 1) Then
    strQuery = "qry_in"
    Else
    strQuery = "qry_out"
    End If
    
    strSaveFile = Application.CurrentProject.Path & "\" & strQuery & "_" & Format(Date, "yyyymmdd") & ".xlsx"

    DoCmd.TransferSpreadsheet acExport, 10, strQuery, strSaveFile, True, , True

The out data don"t even know if it exported, dont see it in any file or directory i was working with and with in option theres is an error 3066: QUery must have at least one destination field.

But anywaysit is really weird that is not working right now, it used to work perfectly ...

 

[Minty]

Bear in mind that Application.CurrentProject.Path will return where ever the database is run from.
In the immediate window type

? Application.CurrentProject.Path

and see what you get.

 

[Tea]

Bear in mind that Application.CurrentProject.Path will return where ever the database is run from.
In the immediate window type

? Application.CurrentProject.Path

and see what you get.

Ah yes thank you i see it now, is there a possibility for the excel to open automatically when its exported?
I will bear this in mind if can't find any other solutions will use the transferspreasheet.