Question MS ACCESS Hangs !

pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 09:28
Joined
Nov 30, 2011
Messages
8,494
Everytime i run a query, (which has 7 calculated fields, of which one looks-up to another table to get the values), the reuslt is fine, takes about three to 5 seconds. But I want to export it to Excel. Every time I try to export, it just hangs up. Literally takes 20-30 minutes to run and after that, it just goes to not responding. The number of records in the result is at a minimum of 2500 records. Any idea to improve? The main reason for export is it needs to be sent to other people in the company and it needs a final sum calculation before transmission. So considering export to other form(text) is not really an option. Any help will be appreciated.

My system is pretty good config aswell with 2.0 GB RAM, AMD Athlon AMD processor 2.9GHz speed. Windows 7 (64Bit) . MS Access 2010 version.
 
You could try first making the query a Make Table query, to dump it into a flat table, then export the flat table
David
 
Hello.. boblarson..

The following is the query i have, it is a bit complicated, as it has several conditions to check for.

Code: 
PARAMETERS [EnterStartDate:] DateTime, [EnterEndDate:] DateTime;
SELECT Customers.[CUSTOMER ID], Customers.TITLE, Customers.[FIRST NAME], Customers.[LAST NAME], Customers.[ADDRESS LINE 1], Customers.[ADDRESS LINE 2], Customers.[ADDRESS LINE 3], Customers.COUNTY, Customers.[POST CODE], Customers.TEL, Customers.[START DATE], Customers.AMOUNT, Customers.PAYMENT, Customers.[PRODUCT CODE], [Customers].[AMOUNT]*[NO OF INST] AS [CUSTOMERS COST], (IIf(Customers.PAYMENT='Monthly',"12",IIf(Customers.PAYMENT='Quarterly',"4","1"))) AS [NO OF INST], DLookUp("PRICE","PolicyCodePrice","(CODE='" & DLookUp("[PRODUCT CODE]","Customers","[CUSTOMER ID]=" & [Customers].[CUSTOMER ID] & "") & "') OR (CODE&'r'='" & DLookUp("[PRODUCT CODE]","Customers","[CUSTOMER ID]=" & [Customers].[CUSTOMER ID] & "") & "')") AS UWF, Round(([CUSTOMERS COST]*(6/106))+0.000001,2) AS IPT, IIf([CIN]=1,IIf([Customers].[PRODUCT CODE] In ("PD","PD1G","PD1E","PD1EG","PD1"),0,2.5),0) AS [CLAIM HANDLER], Round(([IPT]/[NO OF INST])+0.000001,2) AS [IPT PM], Round(([UWF]/[NO OF INST])+0.000001,2) AS [NET PREMIUM], ([NET PREMIUM]+[CLAIM HANDLER]+[IPT PM]) AS [TOTAL PER MONTH], (DateDiff("m",[Customers].[START DATE],[EnterEndDate:])) Mod 3 AS CIN, Customers.[POLICY STATUS]
FROM Customers
WHERE (((Customers.[START DATE]) Between [EnterStartDate:] And [EnterEndDate:]) AND ((Customers.PAYMENT)="Quarterly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed')) OR (((Customers.[START DATE])<[EnterEndDate:]) AND ((Customers.PAYMENT)="Quarterly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND ((Month([Customers].[START DATE]) Mod 3)=(Month([EnterEndDate:]) Mod 3)) AND ((DatePart("d",[Customers].[START DATE]))<=DatePart("d",[EnterEndDate:]))) OR (((Customers.[START DATE])<=[EnterEndDate:]) AND ((Customers.PAYMENT)="Monthly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND ((DatePart("d",[Customers].[START DATE]))<=DatePart("d",[EnterEndDate:]))) OR (((Customers.PAYMENT)="Annually") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND (((([Customers].[START DATE] Between [EnterStartDate:] And [EnterEndDate:]) Or ((DateDiff("m",[Customers].[START DATE],[EnterEndDate:])>=12) And (IIf(Month([Customers].[START DATE])=Month([EnterEndDate:]),DatePart("d",[Customers].[START DATE])<=DatePart("d",[EnterEndDate:]),True)))) And (((DateDiff("m",[Customers].[START DATE],[EnterEndDate:])) Mod 12)<=1))<>False))
ORDER BY Customers.PAYMENT;

DavidAtWork > I am not sure how to do that. Can you guide me?
 
DavidAtWork .. Thank you.. The idea you gave works wonders, now.. is there a way to automate it? I mean i have(had) a form, from which a user clicks the button and the query executes. But now i changed it by using your idea of make table first. It works fine but i have to open the table in order to view the data.. the problem is, i have split the database and hidden the navigation control, from the users.. so the users will not have any access to view or edit the tables directly... any thoughts?
 
well thanks for all your help.. I figured out how to automate it as well.. it takes like 5 minutes.. so much better than hanging up.. Thank you ever so much.,
 
" I have split the database" are you saying it's split frontend / backend, if so it depends how you want it to work, if you want to make the exportable data available to all users regardless which user executes, the table will have to be made in the backend then linked to frontend. When it executes it has to be cleared and the fresh data appended (INSERT INTO). If it doesn't need to be made available to all users it can be MAKE TABLE query and the table is made locally and each time you execute the previous table will be overwritten locally.
All you need to do is execute 2/3 commands by pasting this code behind your button click event:
If as network setup:
Docmd.Setwarnings False
Docmd.RunSQL("DELETE newTable.* FROM newTable")
Docmd.OpenQuery "yourAppendQueryName", acViewNormal
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "newTable", "C:\Folder\newTable.xls", True
Docmd.Setwarnings True

If table will on local machine only:
Docmd.Setwarnings False
Docmd.OpenQuery "yourMakeTableQueryName", acViewNormal
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "newTable", "C:\Folder\newTable.xls", True
Docmd.Setwarnings True

I hope all this makes sense
David
 
David, Thanks for that intel. Yes I have set up as Frontend/Backend. With the main table(s) in the Network drive(hidden). When i made the Make table I created a table locally.. The code i have used is quiet similar..

Code: 
Private Sub SalesRpt_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery ("EmployeeStats")
Call simple
DoCmd.OpenTable "ReportTbl", acNormal
DoCmd.SetWarnings True
End Sub

I can actually implement to export functionality on top of my code.. But my real question is, which will be efficient, placing the table in front end or link it to back end?
 
and also, I have three other reports,(similar but different calculations), for every report(PolicyRpt,AgentRpt) i have used the same table (ReportTbl) to dump the values, so my understanding is that, whenever a query is called, the values inside ReportTbl will be deleted AND new values will be pasted, is that right?
 
If the table name your are creating (Making) is the same, then yes the table itself will be completely overwritten (design and data) each time. If you don't need to keep a copy of the values each time/month, then the most efficient way will be to create the table(s) locally. Personally I would have a different query & table for each report
David
 
i tried to use the DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ReportTbl", "C:\ReportsFolder\SalesReport.xls", True but it is not creating one. any idea why?
 
few things to check, what version of Access/Excel are you running - the version above (acSpreadsheetTypeExcel9) is equivalent to exporting in Excel 97-2003 format.
Also does the table name ReportTbl exist?
Lastly the destination folder, does that exist
If it's the version causing the problem, just go to the code and retype the Docmd.TransferSpreadsheet and adjust all the arguments according to version
David
 
Hi David, The version is 2010 for Access and Excel. Exporting to 97-2003 should not cause any trouble should it? I have used the export only after the creation of the table, just as in your example.. I have created such folder under C: then I even tried just C:.
 
All I can suggest is you put a break in the code and debug it to see if the code is executing
David
 
If you are having issues with DoCmd.TransferSpreadsheet, I might suggest you give this a try instead:

http://www.btabdevelopment.com/ts/default.aspx?PageId=48

Just paste it into a standard module and make sure the name of the module isn't the same as the function. Then call it like:

SendTQ2Excel "ReportTbl"

after you have built your temp table.
 
You must log in or register to reply here.

Similar threads

N
Solved Insert data in phpmyadmin MYSL from MS Access
Replies
4
Views
343
nector
N
H
Handling Extensive Maths in MS Access
Replies
17
Views
939
haxan786
H
X
Is 32768 the maximum number of objects in a MS Access database?
2
Replies
25
Views
1,332
Edgar_
E
B
Advice Request : Simplest method to allow multi-site access to a prototype database
Replies
10
Views
484
Pat Hartman
P
A
MS Access hanging when switching to Design View
Replies
6
Views
683
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom