Hi all.

An application I am working on has me generating a large recordset with SQL Server (in theory could have over 50,000 records) and then wanting to insert this data into an MS Access database. The reason: end users may download the Access database holding the data, but will have no direct contact with the information in SQL Server.

I currently have the download working and generating information in the following manner:

1) Query the information in SQL Server
2) Return that recordset to the VB6 code
3) Loop through each record, performing an insert from each line in the SQL Server recordset into the Access DB
4) After the loop has completed, offer the Access DB for download

Each query to the SQL Server database is unique, and as such a generic procedure will not work. Does anyone have any ideas on how I can mass populate the information into the Access DB, such as with a SELECT INTO or INSERT INTO statement somehow from the VB? (Currently, 1000 records takes approximately 45 to 60 seconds, depending on the number of columns to be inserted - we really do not want the end user to have to wait 20 minutes for the Access DB to be generated if we do not have to).

Just a side note: we used to generate an Excel .CSV file, but with the potential for the number of rows being higher than the maximum allowed in Excel, we opted for an Access database instead.

Any assistance would be greatly appreciated.

Kelly Cassidy
[email protected]

Hi kcassidy,

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
(code below)

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 ?!?
See:
http://www.pstruh.cz/help/RSConv/database.asp


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(A)
- 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"

' ================================================== ===========================
' Input:
' 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


'Example:
'CreateReport("SERVERNAME", "UserAccount", "Password", "ACCESSDATABASE.mdb", "select firstname, lastname from users", "Table_inAccess")


    Set objAccess = Server.CreateObject("Access.Application")
    objAccess.Visible = False
    objAccess.OpenCurrentDatabase Server.MapPath(sz_AccessDBName)
    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
            objAccess.CurrentDb.QueryDefs.Delete RESULT_QUERY_NAME
            Exit For
        End If
    Next 'qdTest


    ' Let's write the remote Query into the Access database
    Set qdResultaat = objAccess.CurrentDb.CreateQueryDef(RESULT_QUERY_NA ME)
    if DebugMode = true then
        response.write("<br>Create querydefinition.")
        response.write("<br>ConnectString="&strConnect&"<b r>")
    end if
    With qdResultaat
        .Connect = strConnect
        .SQL = sz_oraclequery
        .ODBCTimeout = 14400 ' Set timeout to 2 hours.
    End With

    ' 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
    qdResultaat.Close
    objAccess.CurrentDb.QueryDefs.Delete RESULT_QUERY_NAME

    set objDB = nothing
    objAccess.Quit acQuitSaveNone
    Set objAccess = Nothing
end function
%>

You can drive this process directly from SQL Server. Its possible to execute a query in SQL Server which inserts the records directly into an Access db - that will cut out the need for passing the records back and forth between SQL Server, Vb and Access. I think you'll find its much quicker that way.

The magic word is OPENDATSOURCE - read up on it in the SQL Server BOL.

Here's a post from the archive that talks about using it for text files -its the same principle for Access db, just slightly different settings. http://p2p.wrox.com/topic.asp?TOPIC_ID=4693

hth
Phil

sorry for my bad typing - its OPENDATASOURCE not OPENDATSOURCE.

Hi Phil,

Your solution is to perform on MSSQL server, but how to use this in asp ? (I think Kelly uses ASP as well.)

The solution I gave is from SQL direcly into access, action performed by access-appliction itself. It has only 1 datastream.


But, there must be a way to perform this on a IIS server without using acess application. You must open a connection and somehow insert into or select into an Access table.

Something like:
    MySrc.Open ("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\temp\SampleDB.mdb;")
    MySrc.Execute ("SELECT * INTO [SampleTable] FROM [ ???? ]")

Could this work too ?? (Im not sure)


grtz,
Marco

Hi Marco,

What I'm proposing is, instead of these 4 steps which Kelly is using:
1) Query the information in SQL Server
2) Return that recordset to the VB6 code
3) Loop through each record, performing an insert from each line in the SQL Server recordset into the Access DB
4) After the loop has completed, offer the Access DB for download

it can be just these 2 steps:
1) Query the information in SQL Server
2) After the query has completed, offer the Access DB for download

The important part is to construct the right query for SQL Server, i.e. one which uses OPENDATASOURCE to insert the data directly into Access db.

You can do this from ASP or VB. The point is that SQL Server, not your ASP/VB, will take care of inserting into Access.

Kelly originally asked "Does anyone have any ideas on how I can mass populate the information into the Access DB, such as with a SELECT INTO or INSERT INTO statement somehow from the VB?" What I'm saying is that the data can be mass populated from VB via SQL Server.

OK?
Phil

Maybe an example would help.

Say I have an Access db called "dest.mdb" which contains a table "OrderExtract" with 2 fields "OrderID", "ProductID" (both Long Integers) and I want to fill that table with all the OrderID/ProductID pairs from the Northwind db in SQL Server. Here's a SQL Server query which will do just that:

rgds
Phil

Hi Phil,

Thanx for your example and explination. (examples are always the best :-)

This must have helped Kelly alot.
However, we are using Oracle as SQL, so ASP must take the action.

Could your sample do the trick "datadump from Oracle -> ASP -> Access" ?!?

Marco, I know diddly squat about Oracle.

Anyone out there know if Oracle has something similar to OPENDATASOURCE?

Too bad
You helped alot though with your MSSql solution "insert into opendatasource ...", and I have the strong feeling that this method could somehow be used in asp/vb as well.

But to copy fast huge amount of data from a recordset into access table without looping is possible too:

Could anyone imagine what VB6.0-code they used in "RecordsetConverter" at:
http://www.pstruh.cz/help/RSConv/database.asp