[AccessD] Insert Into 101

Darryl:

I've had the problem myself - usually trying to copy a record or group of
records with one or two field modified - like cloning a bill of materials or
a purchase order detail.  If there's a lot of fields, I'll set up a look and
cycle through the fields.  That avoids a bit of coding and the copy/paste
from the design view of the table to avoid the typos.  

Rocky
 

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


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

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