[AccessD] Insert Into 101

Collins, Darryl Darryl.Collins at anz.com
Mon Oct 12 00:29:50 CDT 2009


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

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

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




More information about the AccessD mailing list