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
– steventnorris, Commented Feb 19, 2013 at 16:31
@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
– steventnorris, Commented Feb 19, 2013 at 19:29
@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
– steventnorris, Commented Feb 19, 2013 at 19:32
@Remou There is a pass-through query that I'm using as a recordset for a form that displays a table. — steventnorris
– steventnorris, Commented Feb 19, 2013 at 19:40
The form title reads "LoggedDateTime" for that column, but the actual name in the SQL DB is "LoggedDate" — steventnorris
– steventnorris, Commented Feb 19, 2013 at 19:46

Fionnuala · Accepted Answer · 2013-02-19 20:10:46Z

2

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

answered Feb 19, 2013 at 20:10

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

steventnorris Over a year ago

Slight adjustment to account for passing the Me.Subform to an external method and this worked perfectly.

Collectives™ on Stack Overflow

Access Recordset Yielding NAME?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related