Here's the support engineer's response:
I think there must be some misunderstanding. Index/Primary is very important even critical for a database system. That’s why Access asks us to select a primary key if there’s none. Otherwise, the linked table is ready-only; we can not update the data without the key column. I’d like to emphasize that behavior is by design. However, there’s some workaround regarding this issue:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
1. Indexed View (Similar to add index dynamically onto a table)
We can add the index on a view in SQL Server. Please refer to following articles
Improving Performance with SQL Server 2000 Indexed Views
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Designing Indexed Views
http://msdn2.microsoft.com/en-us/library/ms187864.aspx
===================
CREATE VIEW dbo.vwName with SCHEMABINDING
AS
SELECT ID,Name FROM dbo.tbName
CREATE UNIQUE CLUSTERED INDEX [Idx]
ON [dbo].[vwName]([ID])
===================
We may create an indexed view by coding, and then link this view into Access by calling ‘TransferDatabase’. The code is similar to what I posted in the previous thread.
2. We can bypass the popup, however, as I mentioned above, the data is read-only, we can not modify the data from the linked table/view. Here is the code:
==================
Option Compare Database
Const tblLocalTableName As String = "NameList_SQL"
Function TableNotExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strConn As String
Dim db As Database, tbl As TableDef
Set db = CurrentDb
'Register DSN
DBEngine.RegisterDatabase "NorthwindSQL", "SQL Server", True, _
"Description=Nothwind Employee" & Chr(13) & "Server=SHA-LM-WK" & Chr(13) & "Database=Northwind"
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
'Create Link Table
If Not TableNotExist(tblLocalTableName) Then
'Create Link Table
strConn = "ODBC;"
strConn = strConn & "DSN=NorthwindSQL;"
strConn = strConn & "DATABASE=Northwind;"
strConn = strConn & "Trusted_Connection=yes;"
strConn = strConn & "TABLE=tbName"
Set tbl = db.CreateTableDef(tblLocalTableName, dbAttachSavePWD, "tbName", strConn)
db.TableDefs.Append tbl
Else
'Refresh Link Table
Set tbl = db.TableDefs(tblLocalTableName)
tbl.Connect = strConn
tbl.RefreshLink
End If
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
==================
I hard coded some information in the code:
SQL Server – SHA-LM-01
Database – Northwind
Table – tbName(ID as int not null, Name as nvarchar(100))
-brenda (ISV Buddy Team)
