copying ADO recordset to Access table?

[gbshahaq]

I have a stored procedure on a SQL Server which returns a set of records.
I've returned these records to an Excel sheet successfully using ADO recordsets, but i really want to append these to an Access table.

But how?
create 2 connections - 2 recordsets?
i've tried various things but nothing seems to work right....please help

 

[gbshahaq]

haha - sorted!!



(working) code below:

Function NetScoreFetch(QuestParm As Integer)


Dim cnSrc As New ADODB.Connection, cnDest As New ADODB.Connection, rs As New ADODB.Recordset
Dim strSQL As String, Fld As ADODB.Field, Cmd As ADODB.Command


cnSrc.Open "Provider=sqloledb;Data Source=gbacal;Initial Catalog=CCSurvey;Integrated Security=SSPI"

'cnDest.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\gbaslo\files\crossorg\csd\scorecard2005\db1.mdb;User Id=admin;Password=;"
cnDest.Open CurrentProject.Connection


cnDest.Execute "DELETE * FROM tmp_NetScores" & QuestParm


strSQL = ""
strSQL = " SET NOCOUNT ON " & _
        " EXEC sh_slo_netscores " & QuestParm

rs.Open strSQL, cnSrc


Dim FldList As String, ParmList As String

Set Cmd = New ADODB.Command
Cmd.ActiveConnection = cnDest

For Each Fld In rs.Fields
    If FldList <> "" Then FldList = FldList + ","
    
    FldList = FldList + "[" & Fld.Name & "]"
    If ParmList <> "" Then ParmList = ParmList + ","
    
    ParmList = ParmList + "?"
    Cmd.Parameters.Append Cmd.CreateParameter(Fld.Name, Fld.Type, adParamInput, Fld.DefinedSize)
Next

Cmd.CommandText = "INSERT INTO tmp_Netscores" & QuestParm & "(" & FldList & ") VALUES (" & ParmList & ")"
Cmd.Prepared = True



Do While Not rs.EOF

For Each Fld In rs.Fields
    Cmd.Parameters(Fld.Name).Value = Fld.Value
Next
Cmd.Execute
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

cnSrc.Close
Set cnSrc = Nothing

cnDest.Close
Set cnDest = Nothing

End Function

Found the idea from Experts Exchange:
http://www.experts-exchange.com/Programming/Q_21552431.html

seems rather long-winded though - is there a "simpler" way? It's quick as you like and works perfectly (after many many corrections....)

 

[The Stoat]

I have a stored procedure on a SQL Server which returns a set of records.
I've returned these records to an Excel sheet successfully using ADO recordsets, but i really want to append these to an Access table.

But how?
create 2 connections - 2 recordsets?
i've tried various things but nothing seems to work right....please help

Where is the current code running Excel or Access? Could you not create a DTS package on the server to append the records to Access? You could trigger this in either Excel or Access or time the package itself to do it without intervention.

Call a DTS package from VBA
http://www.sqldts.com/default.aspx?208

Your two connection/recordsets method should work.
Create your SQLServer connection and recordset and then a second connection and recordset for/of the Access table. Loop through the SQLServer recordset and append each record to the correct field in the Access Table via the Access recordset.

TS

 

[gbshahaq]

aha - DTS?
sounds like a nice way to go - but i'm on a security conscious corporate network, and the DBA's i've met so far have all been rather mean - so that's not an option anyway.

The looping records I did try - but i kept doing it wrong and getting the same record inserted a zillion times!
The way i found (above) i'm sure is faster - reading in the values to insert as a string....clever ;-)

 

[The Stoat]

aha - DTS?
sounds like a nice way to go - but i'm on a security conscious corporate network, and the DBA's i've met so far have all been rather mean - so that's not an option anyway.

The looping records I did try - but i kept doing it wrong and getting the same record inserted a zillion times!
The way i found (above) i'm sure is faster - reading in the values to insert as a string....clever ;-)

Glad your happy with it