Access VBA: Scripting.Dictionary - Dump to Table?

Question

Thanks to the helpful hand provided earlier, I have a functional approach to compute Word Frequency among strings in a recordset. The following code produces the desired result.

Last step will be to dump the dictionary structure into an Access table in the currentDB. I can step thru each key as commented out below and append to a table via SQL - but it is very slow w/24K terms.

UPDATED >> w/Solution <<

Private Sub Command0_Click()

Dim counts As New Scripting.Dictionary
Dim word As Variant
Dim desc As String

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset  '<< solution
Dim strSQL As String
Dim db As Database

Set db = CurrentDb

strSQL = "SELECT DISTINCT Items.Description FROM Items ;"

Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs1.EOF
    For Each word In Split(rs1!Description, " ")
        If Not counts.Exists(word) Then
            counts.Add word, 1
        Else
            counts.Item(word) = counts.Item(word) + 1
        End If
    Next
    rs1.MoveNext
Loop

'>> .AddNew Solution inserted as suggested below <<

rs2 = db.OpenRecordset("Freq", dbOpenTable)  

For Each word In counts.Keys
    With rs2
       .AddNew             ' Add new record
       .Fields!Term = word
       .Fields!Freq = counts(word)
       .Update
       .Bookmark = .LastModified
    End With
Next

rs2.Close

'>> End Solution <<

Set rs1 = Nothing
Set rs2 = Nothing

MsgBox "Done"

End Sub

Question:

Can I dump the Dictionary into a table in BULK, vs stepping thru the Keys one-by-one?

Rationale: Easier (for me) to perform downstream presentation & manipulation using a table structure.

Thanks!

There really isn't a way to do this without looping. Even in .NET, the closest you would get from a Dictionary is an array of key/value pairs. DAO connections should give good performance with an INSERT loop. — Comintern, Sep 14 '16 at 13:49
Why not add this to the table directly, instead of writing to a Dictionary first. Then you could just group the data with a count. — Ryan Wildry, Sep 14 '16 at 13:57

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

Your code doesn't show how you tried to insert the rows - with separate INSERT INTO (...) VALUES (...) statements for each row, I assume?

For multiple inserts in a loop, don't use SQL INSERT statements. Instead use a DAO.Recordset with .AddNew, it will be much faster.

See this answer: https://stackoverflow.com/a/33025620/3820271

And here for an example: https://stackoverflow.com/a/36842264/3820271

edited May 23 '17 at 12:25

Community

1
1

answered Sep 14 '16 at 13:53

Andre

26,751
7
36
80

1

Andre, 17K ".AddNew" inserts as you suggested <1 sec. Did not expect that! Thanks (I modified my code above to reflect the ".AddNew" piece). – Mark Pelletier Sep 14 '16 at 14:38
@MarkPelletier: you may be able to shave off some more ms by removing `.Bookmark = .LastModified` - that's only needed if you want to read data from the newly added record, e.g. an Autonumber Primary key. – Andre Sep 14 '16 at 17:44

Access VBA: Scripting.Dictionary - Dump to Table?

1 Answers1