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

[Himmel]

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:

Private Function RefTable()
Dim RefDB As DAO.Database
Dim rs As Recordset
 
Set RefDB as CurrentDB
 
if IsTable("NewTable") then RefDB.Execute "DROP TABLE [NewTable]", dbFailOnError
 
RefDB.Execute "CREATE TABLE NewTable" & _
"(ReferenceID VARCHAR NOT NULL PRIMARY KEY," & _
"Table1 BARCHAR, Table2 VARCHAR, Table3 VARCHAR)"
 
RefDB.TableDefs.Referesh
 
set rs = Recordset
 
'Pull query results.  This takes about 20 seconds
set rs = RefDB.OpenRecordset("SELECT * from Qry_All_Sources")
 
with rs
if .EOF then exit function
 
.MoveFirst
 
Do Until .EOF
 
RefDB.Execute _
"INSERT INTO NewTable" & _
" (ReferenceID, Table1, Table2, Table3) " & _
"VALUES (" & Chr$(34) & rs![ReferenceID] & Chr$(34) & "," & Chr$(34) & _
rs![Table1] & Chr$(34) & "," & Chr$(34) & rs![Table2] & Chr$(34) & "," & _
Chr$(34) & rs![Table3]  & Chr$(34) & ");"
 
.MoveNext
Loop
End With
 
set rs = Nothing
RefDB.Close
set RefDB = Nothing
End Function
 

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!

[Rabbit]

You can use an append query for a whole table rather than doing it one record at a time.

It's of the format:

INSERT INTO TableName (Optional Column List)
SELECT statement;
 

[Teecher]

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:

Private Function RefTable()
Dim RefDB As DAO.Database
Dim rs As Recordset
 
Set RefDB as CurrentDB
 
if IsTable("NewTable") then RefDB.Execute "DROP TABLE [NewTable]", dbFailOnError
 
RefDB.Execute "CREATE TABLE NewTable" & _
"(ReferenceID VARCHAR NOT NULL PRIMARY KEY," & _
"Table1 BARCHAR, Table2 VARCHAR, Table3 VARCHAR)"
 
RefDB.TableDefs.Referesh
 
set rs = Recordset
 
'Pull query results.  This takes about 20 seconds
set rs = RefDB.OpenRecordset("SELECT * from Qry_All_Sources")
 
with rs
if .EOF then exit function
 
.MoveFirst
 
Do Until .EOF
 
RefDB.Execute _
"INSERT INTO NewTable" & _
" (ReferenceID, Table1, Table2, Table3) " & _
"VALUES (" & Chr$(34) & rs![ReferenceID] & Chr$(34) & "," & Chr$(34) & _
rs![Table1] & Chr$(34) & "," & Chr$(34) & rs![Table2] & Chr$(34) & "," & _
Chr$(34) & rs![Table3]  & Chr$(34) & ");"
 
.MoveNext
Loop
End With
 
set rs = Nothing
RefDB.Close
set RefDB = Nothing
End Function
 

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