[AccessD] Insert Into 101

Heh... Fair question really. I am pulling data into a local table from a
database backend via ADO.  I use a function that will automatically
populate a form based on a recordset via ADO and wanted to create a
report the same way,  however I cannot just attached the RS to the
report like you can a form (that surprised me too).

So I though I would try to dump the RS into a table run the local report
from the table.  Turns out that is more complicated that I thought too.
Found an example via google that I could mod to suit my own needs.

This is the actual code I use, it works fine and fast enough, it just
seemed awfully clunky.  Hoping for something a bit sexier.

'===========================================================

Public Sub UpdateLocalTable(strLocalTableName As String, gstrSQL As
String, intNoOfFields)

'==================================================================='
'                                                                   '
'   This procedure opens a recordset and                            '
'   Writes the recordset to a local table                           '
'                                                                   '
'==================================================================='
   
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    Dim DAO_DBLocal As DAO.Database
    Dim DAO_RSLocal As DAO.Recordset
    
    Dim intCount As Integer

    On Error GoTo ErrHandler
    Application.Echo False, "Loading the data into the Table..."
   
    ' Open the connection
    cnn.Open DbADOConStr

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open gstrSQL, cnn, adOpenForwardOnly, adLockReadOnly
    
    gstrSQL2 = ""
    gstrSQL2 = gstrSQL2 & "DELETE FROM " & strLocalTableName

    DoCmd.SetWarnings False
    On Error Resume Next
    DoCmd.RunSQL (gstrSQL2)
    On Error GoTo 0

    gstrSQL2 = ""
    gstrSQL2 = gstrSQL2 & "SELECT * FROM " & strLocalTableName
    
    Set DAO_DBLocal = CurrentDb
    Set DAO_RSLocal = DAO_DBLocal.OpenRecordset(gstrSQL2)
    
    Do While Not rst.EOF
    DAO_RSLocal.AddNew
        For intCount = 0 To intNoOfFields
            DAO_RSLocal.Fields(intCount) = rst.Fields(intCount)
        Next intCount
        DAO_RSLocal.Update
        rst.MoveNext
    Loop

    DAO_RSLocal.Close
    Set DAO_RSLocal = Nothing
    DAO_DBLocal.Close
    Set DAO_DBLocal = Nothing
    
    '=================================================
    
    rst.Close
    cnn.Close
    
ExitHere:
    On Error Resume Next
    Set cnn = Nothing
    Set rst = Nothing
    Application.Echo True
    Exit Sub

ErrHandler:
    gstrErrMsg = "modSQLServer.RefreshSubForm: "
    gstrErrMsg = gstrErrMsg & Err.Number & " - " & Err.Description
    
    ErrHandle (gstrErrMsg)
    Resume ExitHere
End Sub

'=======================================================================
==


 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, 13 October 2009 12:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Insert Into 101

I don't think so.  The obvious question you will be hammered with is
"why"?.

John W. Colby
www.ColbyConsulting.com


Collins, Darryl wrote:
> 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

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