Hi,
I've encountered difficulties in maintaining the primary key of a SQL
Server view linked dynamically in VBA to an Access database. If I use
the transferDatabase function, then the user is prompted to choose the
unique identifyer. If I create the link via the ADOX catalog, then the
key is lost and the data isn't updatable. The goal is to create a
dynamically linked updatable view without prompting the user.
Any suggestions?
By the way, here is the code I've used:
----------------------------------------
Public Function viewLinkTest()
Dim sConn As String
Dim cnn As New ADODB.Connection
cnn.Open "DSN=DSN_Name"
sConn = "ODBC;Driver={SQL
Server};Server=serverName;DATABASE=SQLdbName"
DoCmd.TransferDatabase acLink, "ODBC Database", _
sConn, acTable, vwName, linkedTableName
cnn.Close
End Function
-----------------------------------------
Public Function adoxLinker()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim strLinkName, strTableName, sConn As String
sConn = "ODBC;Driver={SQL
Server};Server=serverName;DATABASE=SQLdbName"
strLinkName = "viewName"
strTableName = "localName"
On Error Resume Next
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strLinkName)
If Err = 0 Then
cat.Tables.Delete (strLinkName)
Else
Err = 0
End If
tbl.Name = strLinkName
Set tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = _
"ODBC; DSN = " & sConn
tbl.Properties("Jet OLEDB:remote Table Name") = strTableName
cat.Tables.Append tbl
Dim k As New Key
'''' Tried to create a unique key, but didn't manage to do so
' k.Columns = "tblID"
' k.Name = "tblID"
' k.Type = adKeyPrimary ' have also tried - adKeyUnique
tbl.Keys.Append k, adKeyPrimary, "tblID"
Set cat = Nothing
End Function
----------------------------
Thanks a lot,
Oren
