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.

Ciscaucasia

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.)

Sop

I was afraid that was going to be the answer sigh. No Access 2013 for the office then, until we rebuild this damn thing as a web service. – Ciscaucasia

Answer 2

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

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

see ADO Dynamic Properties

Palpitation

That does not seem to work with Me.Recordset or Me.RecordsetClone (where Me is a form), so it is probably not helpful in the context of the question (Citation from OP, formatting mine: "[...] and frequently uses the UniqueTable form property."). – Landsturm

@Landsturm It works only for ADO recordsets. Your Form.Recordset is probably DAO. – Maundy

Thanks for caring ... However, my forms are bound to tables which are linked to an SQL server via ODBC, which means that the recordsets behind the forms are ADO. I did not test whether it works with ADO recordsets you have created "manually" in your code, because I believe you :-) But I did verify that it neither works for Form.Recordset nor for Form.RecordsetClone, although these should be ADO in my case. I am still facing the same problem as the OP. – Landsturm

@Landsturm Normally, ADO is based on OLE-DB and DAO is based on ODBC. – Maundy

@Landsturm Try this: If TypeOf Me.Recordset is DAO.RecordSet2 then Debug.Print "It's DAO" – Maundy

Answer 3

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.

Kamerad

It was attempting to set Me.UniqueTable in Access 2013 VBA that was causing the error. – Ciscaucasia

ok, maybe something in ur code lines can be fixed so u can use ex Me.UniqueTable = "dbo.Customer" in ur stored procedure sql that use join to merge two tables :) – Kamerad