Send rst to access Table

[erock24]

I'm looking for an fast way to send a recordset created from an sql query in excel to a tempory table in access. All the code would be in excel. I've got the recordset. Is there a way to create an access table and do a copyfromrecordset to it?? Also i create the recordset using ADODB.Recordset with a library reference to the ActiveX data obj 2.8 (ADO). Prefer a solution that works similar.

Thank you

 

[Norie]

Why not just create SQL to put the results of the query into the table?

Here's a very simple example:

SELECT *
INSERT INTO OtherTable
FROM OriginalTable

Obviously you would use your query in place of the ultra-simple one here.

 

[Fazza]

Maybe, Norie, it is a fabricated recordset?

Below is some code I use to transfer data from a worksheet to an mdb table. Late bound ADO.

As written, an array (ar) stores the data from the worksheet. It should be easy enough to modify to instead refer to your recordset of data. So, there will the recordset I already use for the transfer, and the recordset you have will replace ar.

Sub ADO_ExcelToAccess(ByVal DB_PATH As String, ByVal SourceWorksheetName As String)
  'based on http://www.erlandsendata.no/english/index.php?d=envbadacexportado
  Dim r As Long, i As Long
  Dim cn As Object
  Dim rs As Object
  Dim ar As Variant
 
  With Worksheets(SourceWorksheetName).Range("A1").CurrentRegion
    ReDim ar(1 To .Rows.Count, 1 To .Columns.Count)
    ar = .Value
  End With
 
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_PATH & ";"
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open "tData", cn, 1, 3, 2
  For r = 2 To UBound(ar, 1)
    With rs
      .AddNew
      For i = LBound(ar, 2) To UBound(ar, 2)
        .fields(ar(1, i)) = ar(r, i)
      Next i
      .Update
    End With
  Next r
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  Erase ar
End Sub

PS. "tData" is my Access table name

 

[erock24]

Fazza thanks for the code sample, I will give it a try. I see we have to loop through my recordset, I'm guessing there isn't a method to do a bulk copyfromrecordset?

 

[Fazza]

Not that I know - other than Norie's suggestion to put the results straight in (without 'working' the recordset object).

It depends exactly on what you want to do. Per Norie's suggestion, there can be other ways.

 

[Norie]

erock

Why have you used a query in Excel?

Can you post the SQL for the query?

If you simply want to export data to a temporary table in Access you could just execute an SQL statement like this:

SELECT * INTO NewTable
FROM [Excel 12.0;HDR=YES;IMEX=1;Database=C:\AccessImportTest.xlsx].[Sheet1$A1:I500001];

Fazza

I'm guessing it's not a 'fabricated' recordset.