VB6 - find database table/field names that are Reserved Words

We often get asked on the forums why an SQL statement is giving "random" errors, and far too often it turns out to be because the name of a table/field/view is a Reserved Word.

The function in the attached module uses an ADODB connection (which you need to Open and pass to it) to find out what the reserved words are for your database system, and check if any of your table/field/view names match them.

If any names are found to be reserved words, you are warned (as shown in the example below), and you should change them if possible - otherwise you are very likely to get errors from SQL statements and/or any programs that work with the database.


Example usage:

VB Code:

Dim objConn As ADODB.Connection
Dim strResult As String
                '(open connection)
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                           & "Data Source=C:\Folder\db.mdb"
  objConn.Open
 
              'Check for reserved words
  strResult = CheckReservedWords(objConn)
  MsgBox strResult
 
              '(close connection)
  objConn.Close
  Set objConn = Nothing

Example output:

Code:

---

TABLE names are all valid.
[10 checked]

** Note - VIEW list could not be loaded, so names not checked! **
  (if there are none in your database, this is to be expected!)

** FIELD names which are NOT valid: **
  money  (in: tblFunds)
[131 checked]

---

Note that several reserved words are only reserved because they are the names of system tables ('hidden' tables that the database system creates/uses), so this function tries to avoid warning about system tables!

So far this has been tested on Access and SQL Server 2005 Express, if you use this on another system please let me know - if there are problems (such as warnings about the names of system tables) I'll try to fix them!