We have exactly the same problem with almost 1 million records (couple of times a month).
We used your method and it has a run-time of 1 hour each time to finish.
I solved it with a procedure in ASP; this is how it works:
1. Delete possible old remote links in access mdb
2. Create a remote link in this new database (pass-through-query)
3. Tell Access to "select <remote query> into <a new table>"
4. Delete the remote link from access
Disadvantage is: you must install Access on your server
Second: For each pass-through query Access will make a new connection to your sql server. So you have different sessions. This makes it hard to work with parameters you want to parse to those queries.
Third: account and password might
be stored in the mdb file (despite you deleted the link, you can check this with a hex-editor. Maybe you can solve this by "not storing" the remote link in access).
Advantage: It's as fast as possible. I decreased runtime from 1 hour to less then 2 minutes
on a dump of almost 1 million records.
Second: You can easily dump more queries into tables of the same access database .
Third: you do not have to create a table in Access. Select into will do that for you.
I know it must
be possible to write a procedure (in VBA) to dump a recordset directly from ADO into an access table (without starting up the access application).
I searched the net but found no solution except for a commercial program. They build a procedure in VBA to dump a recordset into an access table or dbf file.
I have tested the full trial version, and it's working fine until I use large
amount of records (bug!!). But it's also fast.
Anybody knows how they did it ?!?
Alright, If you don’t mind the disadvantages of using access to get the data for you, you can check my procedure.
- Procedure name: CreateReport
- Works in ASP (remove "server." to get it to work in VB
- Change connection string to get it to work on MSSQL instead of Oracle (see constant "strConnect").
- First create "REPORT" as a ODBC connection (DSN) (use odbcad32.exe or cntrl-panal). Only the driver is important, you dont have to name the server, account or whatever.
Here is the code:
function CreateReport(sz_database, sz_username, sz_password, sz_AccessDBName, sz_oraclequery, sz_AccessTableName)
Const RESULT_QUERY_NAME = "RESULTAATQUERY"
Const DNS_NAME = "REPORT"
' ================================================== ===========================
' sz_database String Oracle Server name
' sz_username String Oracle Account name
' sz_password String Oracle Password
' sz_AccessDBName String Destination msAccess Database (including pathname)
' sz_oraclequery String SQL statement
' sz_AccessTableName String Resultaat tabel naam
' ================================================== ==========================
' Date : 12-01-2004
' CopyWrong 2004
'CreateReport("SERVERNAME", "UserAccount", "Password", "ACCESSDATABASE.mdb", "select firstname, lastname from users", "Table_inAccess")
Set objAccess = Server.CreateObject("Access.Application")
objAccess.Visible = False
Set objDB = objAccess.CurrentDb()
strConnect = "ODBC;DSN=" & DNS_NAME & ";UID=" & sz_username & "/" & sz_password & "@" & sz_database & ";DBQ=;ASY=OFF;"
' First see if Query already exists and delete it.
For Each qdTest In objAccess.CurrentDb.QueryDefs
If qdTest.Name = RESULT_QUERY_NAME Then
' Let's write the remote Query into the Access database
Set qdResultaat = objAccess.CurrentDb.CreateQueryDef(RESULT_QUERY_NA ME)
if DebugMode = true then
.Connect = strConnect
.SQL = sz_oraclequery
.ODBCTimeout = 14400 ' Set timeout to 2 hours.
' Let's run the remote query from within Access
objAccess.DoCmd.RunSQL "SELECT " & RESULT_QUERY_NAME & " .* INTO " & sz_AccessTableName & " FROM RESULTAATQUERY;", False
' Let's close and delete the remote query from the Access database
set objDB = nothing
Set objAccess = Nothing