Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 19th, 2004, 05:27 PM
Registered User
 
Join Date: Jan 2004
Location: Winnipeg, MB, Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kcassidy
Default INSERT INTO from a SQL Server recordset?

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
kellypcassidy@yahoo.com

 
Old February 2nd, 2004, 02:51 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>








 
Old February 3rd, 2004, 05:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old February 3rd, 2004, 06:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

sorry for my bad typing - its OPENDATASOURCE not OPENDATSOURCE.
 
Old February 3rd, 2004, 01:32 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by pgtips
 (...) OPENDATASOURCE (...)
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

 
Old February 4th, 2004, 05:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old February 4th, 2004, 05:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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:

Code:
USE Northwind
GO

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\path\dest.mdb";User ID=Admin;Password=;')...OrderExtract
SELECT OrderID, ProductID FROM [Order Details]
rgds
Phil
 
Old February 4th, 2004, 11:14 AM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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" ?!?



 
Old February 4th, 2004, 11:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marco, I know diddly squat about Oracle.

Anyone out there know if Oracle has something similar to OPENDATASOURCE?
 
Old February 4th, 2004, 02:09 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by pgtips
 Marco, I know diddly squat about Oracle.(...)


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








Similar Threads
Thread Thread Starter Forum Replies Last Post
INSERT DATE INTO SQL SERVER prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 January 4th, 2007 08:27 AM
insert multiple record in sql server annie_stwg ASP.NET 2.0 Basics 4 September 19th, 2006 08:56 AM
Help about insert row in SQL Server 2000 dimeanel Beginning VB 6 3 January 19th, 2006 02:55 PM
How to insert a Picture in SQL Server using VB 6.0 swadhinm Pro VB Databases 0 September 21st, 2005 06:52 AM
ADO/SQL Server RecordSet and the OrderBy Property Dataman Access 3 February 9th, 2004 11:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.