Results 1 to 1 of 1

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

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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:
    1. Dim objConn As ADODB.Connection
    2. Dim strResult As String
    3.                 '(open connection)
    4.   Set objConn = New ADODB.Connection
    5.   objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    6.                            & "Data Source=C:\Folder\db.mdb"
    7.   objConn.Open
    8.  
    9.               'Check for reserved words
    10.   strResult = CheckReservedWords(objConn)
    11.   MsgBox strResult
    12.  
    13.               '(close connection)
    14.   objConn.Close
    15.   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!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width