Get DB table fields


Since function getTableFields uses some non-built VBA functions, they also must be included in your code for the function to work properly.

Otherwise the following error will occur: Compile error: Sub or Function not defined.

Required functions are listed below. You can get to each function's source code by clicking its name:

When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
'**********************************************************************************************************
' Name:                 getTableFields
' Author:               mielk | 2014-12-14
'
' Comment:              Function to return the list of fields defined in the given database table.
'
' Parameters:
'   connection          Object of ADODB.Connection class, used to connect to the database.
'   tableName           The name of a table for which fields name are to be returned.
'
' Returns:
'   String()            Array containing the names of fields defined in the given table.
'
'
' Exceptions:
'   TableNotFoundException          Thrown if there is not table with the given name.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-12-14        mielk           Function created.
'**********************************************************************************************************
Public Function getTableFields(connection As Object, tableName As String) As String()
    Const METHOD_NAME As String = "getTableFields"
    '------------------------------------------------------------------------------------------------------
    Const SQL_STRING As String = "SELECT * FROM {0} WHERE 1 = 2"
    Dim fields() As String
    Dim rs As Object    '.................................................................. ADODB.Recordset
    Dim sqlString As String
    '------------------------------------------------------------------------------------------------------
    Dim i As Long
    Dim fld As Object
    '------------------------------------------------------------------------------------------------------



    'Insert the given table name to the template SQL query... -------------------------------------------|
    sqlString = formatString(SQL_STRING, tableName)                                                     '|
                                                                                                        '|
    '... and execute this query on the given Connection. --------------------------------------------|  '|
    On Error GoTo TableNotFoundException                                                            '|  '|
    Set rs = connection.execute(sqlString)                                                          '|  '|
    On Error GoTo 0                                                                                 '|  '|
    '------------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    '----------------------------------------------------------------------------------------------------|

    'Read the fields headers from the recordset obtained above and insert them into the result array. ---|
    ReDim fields(1 To rs.fields.Count)                                                                  '|
                                                                                                        '|
    'Iterate through each fields from the recordset and put each value into the result array.--------|  '|
    For Each fld In rs.fields                                                                       '|  '|
        i = i + 1                                                                                   '|  '|
        fields(i) = VBA.LCase$(fld.name)                                                            '|  '|
    Next fld                                                                                        '|  '|
    '------------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    getTableFields = fields                                                                             '|
    '----------------------------------------------------------------------------------------------------|


'==========================================================================================================
ExitPoint:

    'Clear reference to recordset, since it is not needed anymore. --------------------------------------|
    If Not rs Is Nothing Then                                                                           '|
                                                                                                        '|
        'Sometimes happends that this operations crashes. Since it is not very crucial, any error ---|  '|
        'in this process will be just ignored.                                                      '|  '|
        On Error Resume Next                                                                        '|  '|
        Call rs.Close                                                                               '|  '|
        On Error GoTo 0                                                                             '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
        Set rs = Nothing                                                                                '|
                                                                                                        '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    Exit Function

'----------------------------------------------------------------------------------------------------------
TableNotFoundException:
    'Error handler for the case if table with the given name has not been found using the given connection.
    GoTo ExitPoint

End Function