(MS Access at version 97.)
Does anyone have a definite answer.
Been trying to add a Linked Table with a primary key using VBA. (Can be accomplished mannually using New Tables Wizard (very easy)).
Here is the main issue: Since a Linked Table w/Primary key can be done manually, would seem reasonable it can be done programatically (VBA)?
If the Linked Table must be deleted first - that's ok because the database is on a server and connected via odbc. I am only interested in adding (not modifying) the Linked table w/primary key. Once in place MS Access does not allow us to modify a Linked table's primary Key(s).
********
The following code gets real close but when executed, recieve error message: Run time error'3367':
Can't Append. An object with the name already exists in the collection.
Option Compare Database
Option Explicit
Dim db As Database
Dim tdf As TableDef
Dim strDSNName As String
Dim strTableName As String
Dim Idx As Index
Dim Fld As Field
'
Sub BuildTableLink()
Set db = CurrentDb
Set strTableName = "NewTable"
'
Set tdf = db.CreateTableDef(strTableName)
tdf.Connect = "ODBC;DSN="DSN01";;TABLE=" & strTableName & ""
'
tdf.SourceTableName = "ExternalTableName"
'
db.TableDefs.Append tdf
'
Set Idx = tdf.CreateIndex("PrimaryKey")
Set Fld = tdf.Fields("FldA")
Idx.Primary = True
Idx.Unique = True
* * * * Following line Failes * * * *
Idx.Fields.Append Fld
tdf.Indexes.Append Idx
'
End Sub
Watch variable indicates 0 objects for Fields in Idx collection???
To reemphasize: tring to add a Linked Table with primary key using VBA.
Does anyone have a definite answer.
Been trying to add a Linked Table with a primary key using VBA. (Can be accomplished mannually using New Tables Wizard (very easy)).
Here is the main issue: Since a Linked Table w/Primary key can be done manually, would seem reasonable it can be done programatically (VBA)?
If the Linked Table must be deleted first - that's ok because the database is on a server and connected via odbc. I am only interested in adding (not modifying) the Linked table w/primary key. Once in place MS Access does not allow us to modify a Linked table's primary Key(s).
********
The following code gets real close but when executed, recieve error message: Run time error'3367':
Can't Append. An object with the name already exists in the collection.
Option Compare Database
Option Explicit
Dim db As Database
Dim tdf As TableDef
Dim strDSNName As String
Dim strTableName As String
Dim Idx As Index
Dim Fld As Field
'
Sub BuildTableLink()
Set db = CurrentDb
Set strTableName = "NewTable"
'
Set tdf = db.CreateTableDef(strTableName)
tdf.Connect = "ODBC;DSN="DSN01";;TABLE=" & strTableName & ""
'
tdf.SourceTableName = "ExternalTableName"
'
db.TableDefs.Append tdf
'
Set Idx = tdf.CreateIndex("PrimaryKey")
Set Fld = tdf.Fields("FldA")
Idx.Primary = True
Idx.Unique = True
* * * * Following line Failes * * * *
Idx.Fields.Append Fld
tdf.Indexes.Append Idx
'
End Sub
Watch variable indicates 0 objects for Fields in Idx collection???
To reemphasize: tring to add a Linked Table with primary key using VBA.