Steps to Reproduce Behavior

1. In Microsoft Visual Basic, create a Standard EXE project. Form1 is created by default.
2. In Form1, create two command boxes.
3. In Form1, paste the following code:

   Private Sub Command1_Click()
      On Error GoTo errorhandler
      Dim cat As New ADOX.Catalog
      Dim tbl As New ADOX.Table
      Dim Idx As New ADOX.Index
      Dim Cnn1 As New ADODB.Connection
      
      strTable = "[tbl wthspc]"  
    'Here we are setting the table name ourselves so brackets will be passed 
   'to the SQL server as the part of our query, so you will not get an error.
    
   
     'Change the server name, user ID, and password here.
      Cnn1.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;UID=UserID;PWD=Password;database=DatabaseName;"
   
      Cnn1.Open
      
      Set cat.ActiveConnection = Cnn1
      tbl.Name = strTable
      tbl.Columns.Append "ChmpIdx1", adVarWChar, 50
      tbl.Columns.Append "ChmpIdx2", adVarWChar, 10
      cat.Tables.Append tbl
     
      Test cat, tbl
      Set tbl = Nothing
      Set cat = Nothing
      Cnn1.Close
      Set Cnn1 = Nothing
      
      Exit Sub
   errorhandler:
      MsgBox Err.Description, , Err.Source
   End Sub
   Private Sub Test(cat As ADOX.Catalog, tbl As ADOX.Table)
      On Error GoTo errorhandler1
      Dim Idx As New ADOX.Index
      Dim str As String
      
      Idx.Name = "index01"
      Idx.Unique = True
      Idx.PrimaryKey = True
      Idx.Columns.Append "ChmpIdx1"
      tbl.Indexes.Append Idx
      MsgBox "index created successfully"
      Set tbl = Nothing
      
      Exit Sub
   errorhandler1:
      MsgBox Err.Description, , Err.Source
      
   
   End Sub
   
   Private Sub Command2_Click()
      On Error GoTo errorhandler1
      Dim Cnn1 As New ADODB.Connection
      Dim cat As New ADOX.Catalog
      Dim tbl As ADOX.Table
      Dim Idx As New ADOX.Index
      Dim str As String
      
      strTable = "tbl wthspc"
     'Change the server name, user ID, and password here.
      Cnn1.ConnectionString = "Provider=SQLOLEDB;Server=ServerName;UID=UserID;PWD=Password;database=Pubs;"
      Cnn1.Open
    
     'Here we are calling a catalog function to get catalog information.
     
      Set cat.ActiveConnection = Cnn1
      Set tbl = cat.Tables(strTable) 
      Idx.Name = "index02"
      Idx.Unique = True
      Idx.PrimaryKey = True
      Idx.Columns.Append "ChmpIdx1"
      tbl.Indexes.Append Idx
      MsgBox "index created successfully"
      Set tbl = Nothing
      
      Set cat = Nothing
      Cnn1.Close
      Set Cnn1 = Nothing
      Exit Sub
   errorhandler1:
      MsgBox Err.Description, , Err.Source
   
      
   End Sub
   
   					

4. Add a reference to the ADO Ext. 2.5 for DDL and Security object library in the project.
5. Change the SQL Server alias, User ID, and password in the code.
6. Run the project and click the first command box. This creates a table with spaces in the name (tbl wthspc) on the server, and it also creates the index01 index on the table because the table name is enclosed in brackets. If you trace this call in SQL Profiler, you see the table name as [tbl wthspc].
7. Click the second command box. When you click this box, an attempt is made to append the index02 index on the table. In this step, you obtain the catalog information from the database, and because ADO does not enclose the table name in quotation marks or brackets, you receive an error message.

alter table tbl wthspc add constraint index02 primary key  nonclustered(ChmpIdx1)
				

Back to the top