Access Recordset Yielding NAME?

Question

I have an access database that connects to an SQL database via ADODB. The recordset for the access table view is set via the recordset property in the method below. This method is called from the Form_Load function for the form it is viewable in. The form is accessed via a tab on a main form.

Unfortunately, the recordset doesn't seem to be updating correctly between machines. On one machine (Access 2010) it loads up fine. On the second (Access 2010) it loads only the first line as Name?. Sometimes I can get it to load on the second machine if I open the form on it's own, then open the tab.

Any help would be appreciated. Thanks in advance!

Function LoadTblEmployeesADOtoForm()

Dim sqlStr As String

 Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim p As ADODB.Property
    Const ConnStr = "PROVIDER=SQLOLEDB;Server=SERVER;Database=DB;User ID=ID;Password=PWD;"
    Set cn = New ADODB.Connection
    cn.Open ConnStr

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockPessimistic

        'SELECT
        sqlStr = "SELECT * FROM tblEmployees ORDER BY NetworkID"

        Debug.Print sqlStr

        .Source = sqlStr

        Set .ActiveConnection = cn
        .Properties("Preserve on Abort") = True
        .Properties("Preserve on Commit") = True
    End With
    'cn.BeginTrans
    rs.Open

    Debug.Print rs.RecordCount
    Dim temp As Integer

     Set Form_frmManagetblEmployees.Recordset = rs


    cn.Close


    Set rs = Nothing


End Function

@Remou This is legacy code that I'm just maintaining. The problem arose once the users updated their computers to Access 2010. I could do a linked table, but if there was a quick fix to the preexisting code, I wanted to take that route. — steventnorris
@Remou I tried to use a pass-through query and set it as the record source, now it appears to load on the other machine, but not on the first one. — steventnorris
@Remou It's the same fields and columns in each case. The only mismatched I can see is the access table reads LoggedDateTime at the top and the SQL DB read LoggedDate. However, I can't seem to find where to change that column header to see if that's the issue. — steventnorris
@Remou There is a pass-through query that I'm using as a recordset for a form that displays a table. — steventnorris
The form title reads "LoggedDateTime" for that column, but the actual name in the SQL DB is "LoggedDate" — steventnorris

Fionnuala Fionnuala · Accepted Answer · 2013-02-19T20:10:46

When the form is used as a subform, you cannot refer to:

 Set Form_frmManagetblEmployees.Recordset = rs

However, Me will work for both a form and a subform, so:

 Set Me.Recordset = rs

Access Recordset Yielding NAME?

1 Answers