Create Array From VBA Recordset

Question

I am in need of populating a VBA Array with the fields that fit the criteria in my IF statement. I can not wrap my head around creating the array from the recordset, it seems like a completely different world than a "normal" array to me. Here is what I have:

Function AlterTable()

Set rs2 = db.OpenRecordset("___TestTable")
For Each fld In rs2.Fields
If fld.Name <> "ID" Then
If FieldTypeName(fld) <> "Text" Then
    Debug.Print fld.Name  
    'Populate Array Here
  End If
End If
Next

Set fld = Nothing
rs2.Close

End Function

to move from Recordset to array you could use simple method: rs2.GetRows(). However, first you need to create correct recordset results based on SQL query where you can exclude ID field and others of Text type. — Kazimierz Jawor
– Kazimierz Jawor, Commented Jan 5, 2017 at 14:11
@KazimierzJawor - so it's even bigger than what I initially thought. Thank you for posting to allow me a starting point for google :) — StarsFlyFree FromCozyNights
– StarsFlyFree FromCozyNights, Commented Jan 5, 2017 at 14:13
A really fresh answer here: stackoverflow.com/questions/41485788/… — A.S.H
– A.S.H, Commented Jan 5, 2017 at 14:15

StarsFlyFree FromCozyNights · Accepted Answer · 2017-01-05 14:41:36Z

Thanks to the comment providing direction on where to steer by @KazimierzJawor -> This was the syntax that I was able to come up with that accomplished what I was after. (Need to add error handling, but this is 1st run through)

Function Blue()
Dim CreateTableSQL As String
Dim fld As DAO.Field
Set db = CurrentDb()

CreateTableSQL = "CREATE TABLE [GreenSocks] (FieldPK COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, fieldname TEXT);"
db.Execute CreateTableSQL

Set rs2 = db.OpenRecordset("___TestTable")
For Each fld In rs2.Fields
    If fld.Name <> "ID" And fld.Name <> "Store Number" Then
        If FieldTypeName(fld) <> "Text" Then
            Debug.Print fld.Name

                strSQL = "INSERT INTO GreenSocks (fieldname) VALUES ('" & fld.Name & "' );"
                DoCmd.RunSQL strSQL

        End If
    End If
Next

Set fld = Nothing
rs2.Close

strSQL = "select fieldname from GreenSocks"

Set rs3 = db.OpenRecordset(strSQL)
For Each fld In rs3.Fields

    Debug.Print fld.Value

    secondSQL = "ALTER TABLE __TestTable ALTER COLUMN [" & fld.Value & "] TEXT(40);"

    DoCmd.RunSQL secondSQL

 Next

  Set fld = Nothing
  rs3.Close

End Function

LeslieM · Accepted Answer · 2020-04-12 15:45:42Z

1

Dim colNames() As Variant
colNames = Array("Employee", "Client")
'rs.MoveFirst
Dim data() As Variant ' Two dimensional array
data = rs.GetRows(Fields:=colNames)
' data(0,5) is Employee for 6th row in recordset

answered Apr 12, 2020 at 15:45

LeslieM

2,3231 gold badge18 silver badges9 bronze badges

Comments

Nathan_Sav · Accepted Answer · 2017-01-05 15:30:07Z

0

You could use the following function to generate the SQL required to extract what you desire and then use .GetRows() from this. It uses ADO, so you'll need to add the reference to ADO. Based on the above you could use this to generate the INSERT INTO from (function return)

something like docmd.runsql "INSERT INTO tbl_TEST_Clone " & GEN_SQL_TABLE("tbl_test")

Option Explicit

Function GEN_SQL_TABLE(strTableName As String) As String

Dim r As New ADODB.Recordset
Dim rKeys As New ADODB.Recordset

Set r = CurrentProject.Connection.OpenSchema(adSchemaColumns, _
                Array(Empty, Empty, strTableName, Empty))

r.Filter = "[DATA_TYPE]<>" & adWChar

Set rKeys = CurrentProject.Connection.OpenSchema(adSchemaPrimaryKeys, _
        Array(Empty, Empty, strTableName))

While Not r.EOF
    If Not rKeys.BOF Then rKeys.MoveFirst
    rKeys.Filter = "[COLUMN_NAME]='" & r.Fields("COLUMN_NAME").value & "'"
    If rKeys.EOF Then
        GEN_SQL_TABLE = _
            GEN_SQL_TABLE & IIf(Len(GEN_SQL_TABLE) > 0, ",", "") & _
            r.Fields("COLUMN_NAME").value
    End If
    rKeys.Filter=""
    r.MoveNext
Wend

GEN_SQL_TABLE = "SELECT " & GEN_SQL_TABLE & " FROM " & strTableName

r.Close
rKeys.Close

Set r = Nothing
Set rKeys = Nothing

End Function

edited Jan 5, 2017 at 15:30

answered Jan 5, 2017 at 14:57

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

2 Comments

StarsFlyFree FromCozyNights Over a year ago

This looks like a good function to use, but on this project I am pretty tied to DAO and I don't believe you can use both ADO & DAO on one database. Def saving to a text file to store for later :)

Nathan_Sav Over a year ago

You can do both, I've prefixed with the class name ADODB, say your table has 100 fields, all non-text, your method will do an insert 100 times per row. Look at this, one SQL operation, whether you run on DAO runsql or ADO's execute. w3schools.com/sql/sql_insert_into_select.asp

Collectives™ on Stack Overflow

Create Array From VBA Recordset

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related