Get Table Data in RecordSet
If you study VBA after studying C# or VB.net, don’t you think like this?
“Is dataTable not in VBA? “
In fact, we can’t use dataTable in VBA.
You can use the RecordSet instead of the DataTable to do the same thing.
I leave it as a memorandum of usage
Contents
Code to Get Table into RecordSet
The outline of the process is as follows.
①SELECT data from table
②Get result to RecordSet
③Output to csv, tsv
I created an access file.
You can download from here.
https://overseas.loosecarrot.com/2019/03/25/85
How to Write Table to RecordSet
It is written like the following.
Note: The SELECT result is stored in RecordSet on line 13.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
'============================================== 'Outline of processing ：When csv output button is pressed '============================================== Private Sub output_csv_Click() Dim db As DAO.Database 'DB variable Dim RS_Supplier As DAO.Recordset 'recordset 'set DB Set db = CurrentDb 'Get table data to recordset Set RS_Supplier = db.OpenRecordset(GetSupplier()) 'Output recordset to file Call ExportRecordSet(",", RS_Supplier) End Sub</span> |
Contents of GetSupplier()
The SELECT statement is created by GetSupplier().
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
'====================================================================================================== 'purpose :Get All from Supplier Table 'argument ： 'return value :SQL '====================================================================================================== Public Function GetSupplier() As String Dim SB As StringBuilder 'Instantiate Set SB = New StringBuilder SB.Clear SB.AppendLine ("SELECT") SB.AppendLine (" * ") SB.AppendLine ("FROM Supplier") GetSupplier = SB.GetString() End Function</span> |
StringBuilder is described below.
StringBuilder Class
StringBuilder is also an existing class in C # and VB.net, but not in VBA.
So I created easily.
Once created, it is useful because it is used in other Access apps.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
'==================================================================== ' StringBuilder class '==================================================================== Option Compare Database '========================================================= ' Member declaration '========================================================= ' Forming string Private strString As String ' Line feed code Private RTN_CODE As String '========================================================= ' constructor '========================================================= Private Sub Class_Initialize() ' Initialize strString = "" RTN_CODE = Chr(13) & Chr(10) End Sub '========================================================= ' Make the formed string empty '========================================================= Public Sub Clear() strString = "" End Sub '========================================================= ' Get the formed string '--------------------------------------------------------- ' return Molded string '========================================================= Public Function GetString() GetString = strString End Function '========================================================= ' Add string '--------------------------------------------------------- ' param strAdd String to add '========================================================= Public Sub Append(ByVal strAdd) strString = strString & strAdd End Sub '========================================================= ' Add string '--------------------------------------------------------- ' param strAdd String to add '========================================================= Public Sub AppendLine(ByVal strAdd) Append (strAdd & RTN_CODE) End Sub </span> |
