By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,967 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,967 IT Pros & Developers. It's quick & easy.

How to write query results to a new table with Access 2003

P: 21
Hello!

The reference database I currently use runs queries that pull data from hundreds of tables in order to create user-friendly form view. The problem is that these queries can take upwards of 30 seconds to return.

The solution that I've devised is to create an admin function to cache all these query results and then dump that data into a new, consolidated table, and then have the users search through those new tables. Since our database only changes once a week, this is expected to drastically increase performance and in turn productivity.

The problem is, the admin function that caches the query data and then writes it to the new tables is slow. Very slow.

Here is an example of the current function. This is for one query which returns over 45k results, and takes roughly 45-60 minutes to write out:
Expand|Select|Wrap|Line Numbers
  1. Private Function RefTable()
  2. Dim RefDB As DAO.Database
  3. Dim rs As Recordset
  4.  
  5. Set RefDB as CurrentDB
  6.  
  7. if IsTable("NewTable") then RefDB.Execute "DROP TABLE [NewTable]", dbFailOnError
  8.  
  9. RefDB.Execute "CREATE TABLE NewTable" & _
  10. "(ReferenceID VARCHAR NOT NULL PRIMARY KEY," & _
  11. "Table1 BARCHAR, Table2 VARCHAR, Table3 VARCHAR)"
  12.  
  13. RefDB.TableDefs.Referesh
  14.  
  15. set rs = Recordset
  16.  
  17. 'Pull query results.  This takes about 20 seconds
  18. set rs = RefDB.OpenRecordset("SELECT * from Qry_All_Sources")
  19.  
  20. with rs
  21. if .EOF then exit function
  22.  
  23. .MoveFirst
  24.  
  25. Do Until .EOF
  26.  
  27. RefDB.Execute _
  28. "INSERT INTO NewTable" & _
  29. " (ReferenceID, Table1, Table2, Table3) " & _
  30. "VALUES (" & Chr$(34) & rs![ReferenceID] & Chr$(34) & "," & Chr$(34) & _
  31. rs![Table1] & Chr$(34) & "," & Chr$(34) & rs![Table2] & Chr$(34) & "," & _
  32. Chr$(34) & rs![Table3]  & Chr$(34) & ");"
  33.  
  34. .MoveNext
  35. Loop
  36. End With
  37.  
  38. set rs = Nothing
  39. RefDB.Close
  40. set RefDB = Nothing
  41. End Function
  42.  

Please note, the query takes 20 seconds to pull, but the "INSERT INTO" process takes 30-60 minutes, for just this one query!

This process works, but I would appreciate if someone could tell me a faster or more efficient way to do this. Can data in a recordset be dumped directly into a table? Or is it necessary to go through one record at a time as I'm doing?

Thanks for the help!
May 1 '07 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You can use an append query for a whole table rather than doing it one record at a time.

It's of the format:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName (Optional Column List)
  2. SELECT statement;
  3.  
May 1 '07 #2

Teecher
P: 1
Hello!

The reference database I currently use runs queries that pull data from hundreds of tables in order to create user-friendly form view. The problem is that these queries can take upwards of 30 seconds to return.

The solution that I've devised is to create an admin function to cache all these query results and then dump that data into a new, consolidated table, and then have the users search through those new tables. Since our database only changes once a week, this is expected to drastically increase performance and in turn productivity.

The problem is, the admin function that caches the query data and then writes it to the new tables is slow. Very slow.

Here is an example of the current function. This is for one query which returns over 45k results, and takes roughly 45-60 minutes to write out:
Expand|Select|Wrap|Line Numbers
  1. Private Function RefTable()
  2. Dim RefDB As DAO.Database
  3. Dim rs As Recordset
  4.  
  5. Set RefDB as CurrentDB
  6.  
  7. if IsTable("NewTable") then RefDB.Execute "DROP TABLE [NewTable]", dbFailOnError
  8.  
  9. RefDB.Execute "CREATE TABLE NewTable" & _
  10. "(ReferenceID VARCHAR NOT NULL PRIMARY KEY," & _
  11. "Table1 BARCHAR, Table2 VARCHAR, Table3 VARCHAR)"
  12.  
  13. RefDB.TableDefs.Referesh
  14.  
  15. set rs = Recordset
  16.  
  17. 'Pull query results.  This takes about 20 seconds
  18. set rs = RefDB.OpenRecordset("SELECT * from Qry_All_Sources")
  19.  
  20. with rs
  21. if .EOF then exit function
  22.  
  23. .MoveFirst
  24.  
  25. Do Until .EOF
  26.  
  27. RefDB.Execute _
  28. "INSERT INTO NewTable" & _
  29. " (ReferenceID, Table1, Table2, Table3) " & _
  30. "VALUES (" & Chr$(34) & rs![ReferenceID] & Chr$(34) & "," & Chr$(34) & _
  31. rs![Table1] & Chr$(34) & "," & Chr$(34) & rs![Table2] & Chr$(34) & "," & _
  32. Chr$(34) & rs![Table3]  & Chr$(34) & ");"
  33.  
  34. .MoveNext
  35. Loop
  36. End With
  37.  
  38. set rs = Nothing
  39. RefDB.Close
  40. set RefDB = Nothing
  41. End Function
  42.  

Please note, the query takes 20 seconds to pull, but the "INSERT INTO" process takes 30-60 minutes, for just this one query!

This process works, but I would appreciate if someone could tell me a faster or more efficient way to do this. Can data in a recordset be dumped directly into a table? Or is it necessary to go through one record at a time as I'm doing?

Thanks for the help!
Himmel,
Did you get your insert query to work?
I have a similar issue and would like to resolve it in the same way. Your assistance would be appreciated.

Thanks for any help

Allan
Feb 15 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.