Query to table

[wrightyrx7]

Hi all,

I have a query that takes around 10-20 seconds to produce due to all the queries that run before it. This data is displayed within a form I have created. However the problem is that it runs the query each time the form is opened.

What I have done is put in an 'Update' button that will re-run the query for more up to date data.

However I still need data for the form.

What I was thinking having a table with a copy of the query data on it (this is what the form will use). Then when the update button is pressed the query runs then replaces the data on the table.

Do you think this is the best way to do it? If so whats the best way to approach it?


Thanks in advance
Chris.

 

[Guus2005]

Hi Chris,

Even if you have a lot of subqueries perfomance can be fast. If you have a lot of records (>millions) then you should consider using another RDBMS.

Your query(s) might be slow because there are a lot of them or you are not using the table indexes or they are too complex or you are using iif statements or domain functions (dlookup, dsum, dcount, d....) in your queries.

Please post your query statement to get a specific answer.


HTH

 

[Minty]

On top of Guus2005 advice;
If the sub query data is related to a small subset of the main data, I would be tempted to only pull the small main data and display the underlying query data in subforms. If you can get away with it you can even use a tabbed form to really restrict how much data is loaded on first open and only load the tabbed sections if/when they are selected.

 

[wrightyrx7]

THank you both for your replies. Sorry about the delay i had finished work when you replied.

Hi Chris,

Even if you have a lot of subqueries perfomance can be fast. If you have a lot of records (>millions) then you should consider using another RDBMS.

Your query(s) might be slow because there are a lot of them or you are not using the table indexes or they are too complex or you are using iif statements or domain functions (dlookup, dsum, dcount, d....) in your queries.

Please post your query statement to get a specific answer.


HTH

Yes there are a few iif statements, these are the queries that seem to take a while and are probably the cause of the slowness.

On top of Guus2005 advice;
If the sub query data is related to a small subset of the main data, I would be tempted to only pull the small main data and display the underlying query data in subforms. If you can get away with it you can even use a tabbed form to really restrict how much data is loaded on first open and only load the tabbed sections if/when they are selected.

Sorry Minty, im not sure what you mean. The queries in this project are pulling data from tables linked to our SQL database.

I must admit I new to using Access. Im used to writing VBA in Excel, but have small amount of knowledge when writing code in SQL.

 

[Minty]

If the tables are stored in SQL then I would be tempted to create the whole thing as a SQL view and link that back to your FE. The performance difference will be staggering as all the processing is done server side.

 

[wrightyrx7]

If the tables are stored in SQL then I would be tempted to create the whole thing as a SQL view and link that back to your FE. The performance difference will be staggering as all the processing is done server side.

Hi Minty,

This is something we want to do (write these in SQL), but at the minute we do not have the privileges/permissions. All we can do at the minute is view existing tables which we link into access then make the queries.

Hopefully our IT department will authorise us to do the above, fingers crossed!

 

[Guus2005]

If you want to keep working on the current problems, loose the immediate ifs (IIF). They are extremely slow.
You would better create a table and join it instead of using IIFs. IIFs are executed for each record. Which puts a brake on overall query performance.

Once you have migrated to MS SQL Server, you could/should use pass-thru queries which again are faster than regular Access queries because they are passed thru to SQL Server. Hence the name. Bear in mind that regular Access functions don't work with pass-thru queries. You must use SQL Server functions instead.

HTH

 

[RAJUMATHEW]

can you check error for this code when I Run its saying If CB > 0 Then mismatch error?

Public Function modCreditmove()
Dim fso, CB As String
Set fso = CreateObject("Scripting.FileSystemObject")
CB = ("E:\BackupTesting\luckData2003.mdb")
If CB > 0 Then
fso.MoveFile ("E:\BackupTesting\backupuptodate"), "E:\Credit_Archive\"
End If
End Function

 

[Minty]

Firstly - what has this got to do with this thread?
Secondly - Why would you check the value of CB immediately after you explicitly set it to a value?
And finally - you are setting a string value and comparing an integer...