TableDef becomes read only

Discussion:

(too old to reply)

Adam Milligan

2009-09-08 23:43:02 UTC

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

2009-09-09 00:36:29 UTC

Permalink

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)

Post by 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

Adam Milligan

2009-09-09 15:33:01 UTC

Permalink

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

Post by 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)

Douglas J. Steele

2009-09-10 21:11:29 UTC

Permalink

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)

Post by Adam Milligan
Doug-
Thanks for the response. You pointed me in the right direction to fix this.
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

Post by 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