[AccessD] Insert Into 101

Hi Darryl

Looping and adding is the method - running multiple SQL executes is very slow. But replace ADO with DAO to speed it up.

/gustav


>>> Darryl.Collins at anz.com 12-10-2009 07:29 >>>

Hah! Thanks Darren,  

This is the process I currently have in place right now :) I have even
written a function to feed the parameters into the function so I can use
this code on just about anything.

Yeah, it works fine and fast enough (I am usually only moving a few
dozen records) but it seems to be awfully cumbersome and over
engineered.  I was hoping for something a bit slimer and sexier... But
hey, if that is the only way I will stick with it. :)



Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren -
Active Billing
Sent: Monday, 12 October 2009 4:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Insert Into 101

Hi Daz

I've just grabbed something I use to populate a local table in my access
App called "tblAccounts" with data from an SQLServer table called
"Accounts"
I've removed some bits here in the email editor so I hope it still works
I also run a small function called "f_ SetSQLSVRConnection" to connect
to the SQL Server - It's included here at the bottom - makes it easier
than typing that crap all the time Hope this is useful See ya DD
~~~~~~~~~~~~~~~~~~~~~~~~~

Dim conn1 As New ADODB.Connection
Dim conn2 As Object

Dim rs1 As New ADODB.Recordset
Dim rs2 As Object

Dim selSQL1 As String
Dim selSQL2 As String
Dim delSQL1 As String

'get all account records from the SQL Server table "Accounts"
selSQL1 = "select * from Accounts order By AccountNo" 

'Get a recordset happening for the local Access table "tblAccounts"
selSQL2 = "SELECT * from tblAccount"

'get a recordset ready to clear previous entries in our local temp table
delSQL1 = "Delete * from tblAccount"

conn1 = f_SetSQLSVRConnection 'All the Connection string stuff in this
function conn1.Open

rs1.Open selSQL1, conn1, adOpenStatic

DoCmd.RunSQL delSQL1 ' Clear previous entries
        
        Set rs2 = CreateObject("ADODB.Recordset")
        Set conn2 = Application.CurrentProject.Connection
        rs2.Open selSQL2, conn2, 1, 3

                Do Until rs1.EOF
                    With rs2
                        .AddNew
                        !AccountNo = rs1!AccountNo
                        !CompanyName = rs1!CompanyName
                        !DateCreated = rs1!DateCreated
                        !DateCancelled = rs1!DateCancelled
                        .Update
                    End With
                    rs1.MoveNext
                Loop
            End If
        
        rs1.Close
        conn1.Close
        Set rs1 = Nothing
        Set conn1 = Nothing