TableDef becomes read only

[Adam Milligan]

All- I am using Access 2007 with a sql server 2008 back end via ODBC. I am
trying to dynamically change which view a linked table connects to. For
example, I have a linked table named "vwStudents" which actually connects to
"vwStudents292" in the sql database. I would like to be able to change it so
that vwStudents connects to "vwStudents291" in the backend instead. The
following code works well...

Public Sub RelinkTables(sCurrName, sProgram As String)
On Error GoTo Err_Handle

Dim tdfNew As TableDef

'Create a new table def that looks exactly like the old one
Set tdfNew = CurrentDb.CreateTableDef(sCurrName)

tdfNew.Connect = CurrentDb.TableDefs(sCurrName).Connect

'connect that table def to the correct school's data
tdfNew.SourceTableName = sCurrName & sProgram

'Append a temp table to the table defs
tdfNew.Name = sCurrName & sProgram & "Temp"
CurrentDb.TableDefs.Append tdfNew

'delete the old table def
DoCmd.DeleteObject acTable, sCurrName

'rename the temp table
CurrentDb.TableDefs(sCurrName & sProgram & "Temp").Name = sCurrName

Exit Sub

Except for the fact that before I run the code, the linked table is
updateable. After I run the code, the table is linked to the correct view,
but is read only. Is there any way to set the updateable property of a
freshly linked table? Thanks.

Adam

[Douglas J. Steele]

Do you have a unique index defined for vwStudents292 in SQL Server? ODBC
linked tables are read-only unless there's a unique index.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Adam Milligan" <AdamMi...@discussions.microsoft.com> wrote in message
news:C8F71A5F-2435-4641...@microsoft.com...

[Adam Milligan]

Doug-

Thanks for the response. You pointed me in the right direction to fix this.
I have added a couple of lines to my original code:

Public Sub RelinkTables(sCurrName, sProgram, sIndex As String)
On Error GoTo Err_Handle

Dim sSQL As String

Dim tdfNew As TableDef

'Create a new table def that looks exactly like the old one
Set tdfNew = CurrentDb.CreateTableDef(sCurrName)

tdfNew.Connect = CurrentDb.TableDefs(sCurrName).Connect

'connect that table def to the correct school's data
tdfNew.SourceTableName = sCurrName & sProgram

'Append a temp table to the table defs
tdfNew.Name = sCurrName & sProgram & "Temp"
CurrentDb.TableDefs.Append tdfNew

'delete the old table def
DoCmd.DeleteObject acTable, sCurrName

'rename the temp table
CurrentDb.TableDefs(sCurrName & sProgram & "Temp").Name = sCurrName

'create a unique index to make it updateable

sSQL = "CREATE UNIQUE INDEX " & sIndex & "_idx ON " & sCurrName & " (" &
sIndex & ")"
CurrentDb.Execute sSQL

Exit Sub

This makes it work (the linked table is updateable again), but I wanted to
ask if this code is "safe" or if I am inadvertantly filling a hidden table
with index after index every time someone opens my datbase, or if the old
indexes are deleted when I delete the old table. Thanks again for you
expertise.

Adam

[Douglas J. Steele]

As far as I'm aware, the System Catalog tables in Access have referential
integrity. Deleting the old linked table should delete all other objects
connected to it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Adam Milligan" <AdamMi...@discussions.microsoft.com> wrote in message

news:DDA1EE5F-30F1-4B9B...@microsoft.com...