[AccessD] Insert Into 101

Dazza

I forgot to mention - 
This method takes ~20 secs or so to load 50K records from the SQL Server table
to my Access dB - That's quick enough for me. I am sure there are quicker ways
though.
JC's numbers about update and processing speeds the other day were a zillion
times faster but this is very basic Server (1 HDD and not a lot of ram) doing
quite a few things apart from hosting the SQL Server sessions
But of course Your Mileage May Vary
Also don't forget to 'empty' temp tables like this one if you too are going to
have many many thousands of records. Bloat goes through the roof if you don't.
See ya

DD


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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function f_SetSQLSVRConnection()

On Error GoTo Err_

Dim strdB As String
Dim strServerName As String

Dim strConnect As String

Dim strUserID As String
Dim strPassword As String

strdB = DLookup("[DatabaseName]", "tblClients", "ClientID = " &
Forms!xfrmBeast!txtClientID)

strServerName = DLookup("[DefaultServer]", "tblClients", "ClientID = " &
Forms!xfrmBeast!txtClientID)

strConnect = Application.CurrentProject.Connection

strUserID = DLookup("[ActiveUserName]", "tblOptions_LOCAL")
strPassword = DLookup("[ActivePassword]", "tblOptions_LOCAL")

f_SetSQLSVRConnection = "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=" & strUserID & ";Initial Catalog=" & strdB & ";Data Source= " &
strServerName & ";Password=" & strPassword

'Debug.Print f_SetSQLSVRConnection

Exit_:
Exit Function

Err_:
DoCmd.Hourglass False
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error in
f_SetSQLSVRConnection Routine"
Resume Exit_

End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Many thanks
 
Darren - Active Billing

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Collins, Darryl
Sent: Monday, 12 October 2009 1:55 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Insert Into 101

Hi Folks,

I know I can do this

INSERT INTO tblMyTable
SELECT * FROM tblMyOtherTable

Is there any way of using SQL to select a recordset and then using that
RS in the insert statement?

    cnn.Open DbADOConStr
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open gstrSQL, cnn, adOpenForwardOnly, adLockReadOnly

    INSERT INTO tblMyTable
    SELECT * FROM  " & rst

I have a work-around for this already, but it involves looping and
cloning recordsets and jumping thru other hoops.  Frankly it seems very
inelegant and inefficent (even though it is fast enough for my
purposes).  I am sure there must be a way of getting a recordset for the
backend and just dumping the whole thing into a local table (assuming
all the fields line up ofcourse).

Cheers
Darryl

"This e-mail and any attachments to it (the "Communication") is, unless
otherwise stated, confidential,  may contain copyright material and is for the
use only of the intended recipient. If you receive the Communication in error,
please notify the sender immediately by return e-mail, delete the Communication
and the return e-mail, and do not read, copy, retransmit or otherwise deal with
it. Any views expressed in the Communication are those of the individual sender
only, unless expressly stated to be those of Australia and New Zealand Banking
Group Limited ABN 11 005 357 522, or any of its related entities including ANZ
National Bank Limited (together "ANZ"). ANZ does not accept liability in
connection with the integrity of or errors in the Communication, computer virus,
data corruption, interference or delay arising from or in respect of the
Communication."

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com