Primary key of SQL Server linked View in an Access Database

[Oren]

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 transferDatabas e 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.Connectio n

cnn.Open "DSN=DSN_Na me"
sConn = "ODBC;Driver={S QL
Server};Server= serverName;DATA BASE=SQLdbName"

DoCmd.TransferD atabase 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={S QL
Server};Server= serverName;DATA BASE=SQLdbName"
strLinkName = "viewName"
strTableName = "localName"

On Error Resume Next
cat.ActiveConne ction = CurrentProject. Connection
Set tbl = cat.Tables(strL inkName)
If Err = 0 Then
cat.Tables.Dele te (strLinkName)
Else
Err = 0
End If

tbl.Name = strLinkName
Set tbl.ParentCatal og = 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.Appe nd 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

[Oren]

A solution has been found - for all curious ones:

The ADOX catalog view-linking code was used. After the link has been
established, a unique index is added in the following manner:

DoCmd.RunSQL "CREATE UNIQUE INDEX vw1Index ON vwName(viewID)"

Oren
or**@sgdblaw.co m (Oren) wrote in message news:<2f******* *************** ****@posting.go ogle.com>...

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 transferDatabas e 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.Connectio n

cnn.Open "DSN=DSN_Na me"
sConn = "ODBC;Driver={S QL
Server};Server= serverName;DATA BASE=SQLdbName"

DoCmd.TransferD atabase 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={S QL
Server};Server= serverName;DATA BASE=SQLdbName"
strLinkName = "viewName"
strTableName = "localName"

On Error Resume Next
cat.ActiveConne ction = CurrentProject. Connection
Set tbl = cat.Tables(strL inkName)
If Err = 0 Then
cat.Tables.Dele te (strLinkName)
Else
Err = 0
End If

tbl.Name = strLinkName
Set tbl.ParentCatal og = 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.Appe nd 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