Access 2013 breaks UniqueTable functionality

Question

We have a fairly large Access front-end application that has been running on Access 2010. It makes extensive use of ADO recordsets for accessing data on our SQL servers, and frequently uses the UniqueTable form property.

We are looking to move the whole office to Office 2013 early next year, but during testing we have found that Access 2013 will not work with our code that uses UniqueTable. Any attempt to set UniqueTable results in the error message:

You entered an expression that has an invalid reference to the property UniqueTable

The following code works on Access 2010 but encounters above error on Access 2013 when attempting to set UniqueTable:

dim conn AS New ADODB.Connection
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA SOURCE=server1;DATABASE=database1;Integrated Security=SSPI;"
conn.CursorLocation = adUseServer
conn.Provider = "MSDataShape"
conn.Open

Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT TOP 10 * FROM Members WHERE MemberID IS NOT NULL"

cmd.Execute

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenKeyset, adLockOptimistic

Set Recordset = rs
UniqueTable = "Members"

While searching for a solution I have found only a couple of other cases where this error has been mentioned, and no solutions so far.

Answer 1

I'm afraid that you may be out of luck on this one. I was able to recreate your issue: code that successfully set a form's UniqueTable property in Access 2010 failed in Access 2013 with the same runtime error message.

A Google search for microsoft access uniquetable yields a number hits, and the vast majority of them refer to the use of that form property in an ADP. ADP support was completely removed from Access 2013, so my guess is that UniqueTable support was removed along with it. (The IntelliSense feature within the Access 2013 VBA editor still offers Me.UniqueTable as a property of a Form object, but Access 2013 apparently does not allow us to set a value for it at runtime.)

Answer 2

You can still use Me.UniqueTable to make sure your select join into a ADO recordset is working while delete data from many-table. Also me.ResyncCommand is working from VBA code, not any more as a property in form design, but behind the form in the code like Form Load.

Answer 3

You can set Recordset.Properties("Unique Table"), e.g.:

rs.Properties("Unique Table") = "members"

see ADO Dynamic Properties