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.
Dim objConn As ADODB.Connection
Dim strResult As String
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Folder\db.mdb"
'Check for reserved words
strResult = CheckReservedWords(objConn)
Set objConn = Nothing
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!
TABLE names are all valid.
** 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)
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!