CODEWITHSUNDEEP
sqlms-accessvbaadodbrecordset
steventnorris
steventnorris

Reputation: 5896

Access Recordset Yielding NAME?

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

Upvotes: 2

Views: 209

Answers (1)

Fionnuala
Fionnuala

Reputation: 91336

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

Upvotes: 2

Related Questions