
deleting tables and updating table name
Hello,
I am running the following code after I link to SQL Server 7 tables. What I
want to do is remove the "dao_" of the linked table name but delete the
linked table with the same name (minus the dao_) first, if it exist. The
problem I have is that when I link the tables and run the following code, I
get the error:
Run-time error '3265'
Item not found in collection.
On debug, it fails at 'Set TD = db.TableDefs(ctrTblDef)'
I'll then re-run the code and it works fine. The question I have is why
does it fail the first time, but not the second, and what can I do to
prevent this?
Private Sub cmd_remove_dao_Click()
Dim db As Database, TD As TableDef, ctrTblDef As Integer, strName As
String
'Updating the Caption field for the designated tables
'---------------------------------------------------------------------------
--------
Set db = CurrentDb()
For ctrTblDef = 0 To db.TableDefs.count - 1
Set TD = db.TableDefs(ctrTblDef)
'On Error Resume Next
'Strip "dbo_" from the front of the name
If Mid(TD.Name, 1, 4) = "dbo_" Then
strName = Mid(TD.Name, 5, Len(TD.Name))
db.TableDefs.Delete (strName)
TD.Name = strName
End If
Next ctrTblDef
End Sub
Thanks, Steven