Prev: Sharepoint linked table - can add records but can't update existing records
Next: Value of Sum() or Count() for empty recordset
From: Tim Ricard on 4 Feb 2010 09:33
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
From: Salad on 4 Feb 2010 10:31
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.
From: Tim Ricard on 4 Feb 2010 11:06
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
 | 
Pages: 1
Prev: Sharepoint linked table - can add records but can't update existing records
Next: Value of Sum() or Count() for empty recordset