deleting tables and updating table name 
Author Message
 deleting tables and updating table name


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

'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

Sun, 28 Jul 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. DELETED tables cause error on table Connect?

2. Retaining styles in table cells when deleting table


4. PROBLEM: Accessing Foxpro table(delete, update, insert)

5. Update the number of recordset in a table after deleting one

6. Help on Insert,Update and Delete tables.

7. Cannot add record to Visual Foxpro table after deleting all records in table

8. create table and delete table with ADO

9. Update a Table from another Table without duplicates

10. update table field from xref table

11. !how VB update Table A INNER JOIN Table B, Set

12. how VB update Table A INNER JOIN Table B, Set


Powered by phpBB® Forum Software