0

I'm using Access 2013 as a front end with a SQL Server back end, and I'm trying to bind an ADO Recordset to a form, however, the Recordset will only open as Static. So I can see all the records in a datasheet, I just can't edit them.

I can use the connection object to execute an update query, so I don't think that is the issue. I've tried using Dynamic, Keyset and Forward-Only and locking as Batch Optimistic, Optimistic, and Pessimistic to open the Recordset, but still opens as static.

Below code is condensed, but essentially the same, and most importantly, still doesn't work.

Public Sub TestConnect()
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

    Set objConn = New ADODB.Connection
    objConn.ConnectionString = "Data Provider=sqloledb;Provider=Microsoft.Access.OLEDB.10.0;Server=REDACTED;Database=REDACTED;Trusted_Connection=Yes"
    objConn.CursorLocation = adUseClient
    objConn.Open

    Debug.Print objConn.state = adStateOpen

    Set objRS = New ADODB.Recordset
    objRS.CursorLocation = adUseClient
    objRS.Open "SELECT * FROM Table1;", objConn, adOpenDynamic, adLockOptimistic

    Debug.Print objRS.CursorType

    objRS.Close
    objConn.Close
End Sub

I've tried looking at a few place, such as https://learn.microsoft.com/en-us/office/vba/access/Concepts/ActiveX-Data-Objects/bind-a-form-to-an-ado-recordset and MS Access Form Bound to ADO Disconnected Recordset, but I'm not having any luck identifying where my issue is.

Any help would be amazing, I'm tearing my hair out here. Thanks!

djennings
  • 9
  • 1
  • 2
  • 1
    What the ???? is that connection string? It looks like a weird hybrid between an Access connection string and an SQL server one. I'm truly amazed that you don't get a connection error. Try using a standard SQL server connection string from https://www.connectionstrings.com/sql-server/. – Erik A Jun 23 '20 at 15:47
  • I know, but if you remove the provider tag, it won't bind to the form. And, it still opens as static. – djennings Jun 23 '20 at 15:57
  • I've often bound recordsets with standard SQL server providers to forms. `Microsoft.Access.OLEDB.10.0` = Microsoft Access. `Server=REDACTED;Database=REDACTED;Trusted_Connection=Yes` = SQL Server. That doesn't make sense. Have you tried an SQL server one, e.g. `MSOLEDBSQL`? – Erik A Jun 23 '20 at 16:03
  • Excuse me while I find a brick wall, that worked, and I *know* I've tried that before. Looks like I need to get better gremlin repellent. Thanks! Interestingly enough, it still thinks it is static, but allows updates. – djennings Jun 23 '20 at 16:25

0 Answers0