6

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.

Corey
  • 15,524
  • 2
  • 35
  • 68

3 Answers3

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 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. – Corey Jan 05 '14 at 21:48
1

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

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

see ADO Dynamic Properties

Christian d'Heureuse
  • 5,090
  • 1
  • 32
  • 28
  • 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."). – Binarus Nov 08 '20 at 10:01
  • @Binarus It works only for `ADO` recordsets. Your `Form.Recordset` is probably `DAO`. – Christian d'Heureuse Nov 08 '20 at 16:43
  • 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. – Binarus Nov 09 '20 at 07:10
  • @Binarus Normally, ADO is based on OLE-DB and DAO is based on ODBC. – Christian d'Heureuse Nov 09 '20 at 19:39
  • @Binarus Try this: `If TypeOf Me.Recordset is DAO.RecordSet2 then Debug.Print "It's DAO"` – Christian d'Heureuse Nov 09 '20 at 19:54
0

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.

Irish Joy
  • 1
  • It was attempting to set `Me.UniqueTable` in Access 2013 VBA that was causing the error. – Corey Sep 18 '14 at 07:59
  • 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 :) – Irish Joy Sep 24 '14 at 19:40