f



"Object name exists" error

Hey guys,

It has been a couple of years since I have coded in MS Access and I
have hit a snag early on that I can't quite remember what to do! I am
running Access 2003 (file in 2000 format) and using DAO. I want to
copy the tabledefs from one database (dbSource below) into my current
database (db below). I am trying to flex my DAO muscles so DAO
solutions only please (i.e. I can do this in SQL already; i just like
to know different ways to attack a problem). Here is my code. I have
marked the line where things go to heck:

Public Sub LinkTables()

Dim tdfSource As DAO.TableDef, tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As DAO.Database
Dim dbSource As DAO.Database
Dim str As String
Dim dlgOpen As FileDialog

    Set db = CurrentDb
    Set dlgOpen = FileDialog(msoFileDialogFilePicker)

    dlgOpen.AllowMultiSelect = False
    If dlgOpen.Show = -1 Then
        str = dlgOpen.SelectedItems(1)
        Set dbSource = OpenDatabase(str)
        str = ""
        Set tdfSource = dbSource.TableDefs("tbl")
        For Each fld In tdfSource.Fields
---->      tdf.Fields.Append fld  <---- RESULTS IN RUN-TIME ERROR
3367: OBJECT ALREADY EXISTS IN THIS COLLECTION
        Next

 --->     db.TableDefs.Append tdf <---- THIS WOULD RESULT IN THE SAME
ERROR WITHOUT THE FOR LOOP ABOVE
    End If

    Set tdf = Nothing
    Set tdfSource = Nothing
    Set db = Nothing
    Set dbSource = Nothing
    Set dlgOpen = Nothing

End Sub

I have double checked that there doesn't exist a duplicate object name
after this error occurs. I have a feeling it has something to do with
my workspace but I am having a time remembering the purpose of the
workspace objects.

Thanks in advance for the help

Tim
0
tricard (37)
2/4/2010 2:33:19 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
602 Views

Similar Articles

[PageSpeed] 8

Tim Ricard wrote:
> Hey guys,
> 
> It has been a couple of years since I have coded in MS Access and I
> have hit a snag early on that I can't quite remember what to do! I am
> running Access 2003 (file in 2000 format) and using DAO. I want to
> copy the tabledefs from one database (dbSource below) into my current
> database (db below). I am trying to flex my DAO muscles so DAO
> solutions only please (i.e. I can do this in SQL already; i just like
> to know different ways to attack a problem). Here is my code. I have
> marked the line where things go to heck:
> 
> Public Sub LinkTables()
> 
> Dim tdfSource As DAO.TableDef, tdf As DAO.TableDef
> Dim fld As DAO.Field
> Dim db As DAO.Database
> Dim dbSource As DAO.Database
> Dim str As String
> Dim dlgOpen As FileDialog
> 
>     Set db = CurrentDb
>     Set dlgOpen = FileDialog(msoFileDialogFilePicker)
> 
>     dlgOpen.AllowMultiSelect = False
>     If dlgOpen.Show = -1 Then
>         str = dlgOpen.SelectedItems(1)
>         Set dbSource = OpenDatabase(str)
>         str = ""
>         Set tdfSource = dbSource.TableDefs("tbl")
>         For Each fld In tdfSource.Fields
> ---->      tdf.Fields.Append fld  <---- RESULTS IN RUN-TIME ERROR
> 3367: OBJECT ALREADY EXISTS IN THIS COLLECTION
>         Next
> 
>  --->     db.TableDefs.Append tdf <---- THIS WOULD RESULT IN THE SAME
> ERROR WITHOUT THE FOR LOOP ABOVE
>     End If
> 
>     Set tdf = Nothing
>     Set tdfSource = Nothing
>     Set db = Nothing
>     Set dbSource = Nothing
>     Set dlgOpen = Nothing
> 
> End Sub
> 
> I have double checked that there doesn't exist a duplicate object name
> after this error occurs. I have a feeling it has something to do with
> my workspace but I am having a time remembering the purpose of the
> workspace objects.
> 
> Thanks in advance for the help
> 
> Tim


Maybe look at CreateTableDef() in help for a starter.  CreateField() as 
well.
0
Salad
2/4/2010 3:31:45 PM
Thanks for the tip Salad. I figured out my issue after reading the
help files you suggested. I completely disregarded proper referencing
with my code. After putting in proper TableDef Creation routines, all
seems to work well.

Thanks again,

Tim
0
Tim
2/4/2010 4:06:11 PM
Reply: