[AccessD] Insert Into 101

If this doesn't have to be dynamic (the fields are known) then SELECT * (or field list) FROM 
SQLServerTblX and use that select as the select in the append query

INSERT INTO tblMyTblInAcess (MyFldList)
SELECT * (or field list) FROM SQLServerTblX

You have already decided to dump to a local table, just use a query directly.

If you need to deal with field names dynamically then you are stuck building the field list in code.

John W. Colby
www.ColbyConsulting.com


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