Appending Data from SQL Server into Access

[Geoff Codd]

Hi,

I have the following code

Cn.CursorLocation = adUseServer
Cn.Open "Driver={SQL Server};Server=MS-LON-APPS01;Database=Warehouse;Trusted_Connection=yes"
Cn.CommandTimeout = 400
 
strSQL = "SELECT [Opco Code], [SAP Account ID], [Customer Name], [Invoice Number], [Product Group 4 ID], " _
        & "[seg], [Reporting Period], [Charge Period], [Calls], [Minutes], [Billing Currency Code], " _
        & "[Revenue Post Disc & Adj (Billed)], [Revenue Post Disc & Adj (USD)] " _
        & "FROM RRR_REVENUE INNER JOIN rsi_cst ON [SAP Account ID] = [sap_id] " _
        & "WHERE ((([seg])='1' Or ([seg])='6' Or ([seg])='8') AND (([Reporting Period])='200607'));"
 
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly

Which returns data from an SQL server, I need to know the quickest way to copy this into a table in access, I have tried reading the recordset line my line and adding to a table but this takes 15 mins, even though it only take 2 mins to open the SQL.

Any help is much appreciated as always.
Thanks in advance
Geoff

 

[Geoff Codd]

can anyone help?

 

[FireStrike]

I have run into a similar problem. Depending on what you want to do you can try to simply import the sql tables to access tables. Although if you need to work with live current data, this would not be an option. There are two ways I found to speed things up a bit. The first is to use a dynamic size array, and fill in all the data you need into the array, process it, then push what you need into your access tables. This will not work if you have a huge database, and not enough memory to hold the whole thing. As mine had 2.5 million records, I ran my workstation out of memory doing this. The other option that I found was to push the data that you want into a flat file, process it there, and then push it into an access db. I really do not know why this is faster, but it made a process that took me 12 hours, to only take about 45 minutes. I hope this helps a bit. If I can think of any other tricks that I use to speed up my programs I will let you know.

 

[Pat Hartman]

Link to the SQL Server table. You can then run queries against it, including append queries or make table queries to copy the data into Jet tables.