Need help creating a disconnected recordset in VBA

[Amy Badgett]

Here is the code that I copied and pasted from the internet, and I have been trying to modify it to fit what I am doing. The code is getting stuck on what seems to be pretty simple things (on the "Set conn = New DAO.Connection" ) with a message coming up saying "Complie error: User-defined type not defined". I'm sure there are going to be quite a few more compile errors after I get this one worked out as well.

Private Sub Form_Open(Cancel As Integer)
'Sub Rst_Disconnected()
    Dim conn As DAO.Connection
    Dim myRecordset As DAO.Recordset
    Dim strConn As String
    Dim strSQL As String
    Dim strRst As String
 
    strSQL = "SELECT * FROM Clients"
 
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strConn = strConn & "Data Source = " & "(database file-path)" & "\mydb.mdb"
 
    Set conn = New DAO.Connection
    conn.ConnectionString = strConn
    conn.Open
 
    Set myRecordset = New DAO.Recordset
    Set myRecordset.ActiveConnection = conn
    ' retrieve the data
    myRecordset.CursorLocation = callForm
    myRecordset.LockType = adLockBatchOptimistic
    myRecordset.CursorType = adOpenStatic
    myRecordset.Open strSQL, , , , adCmdText
 
    Set myRecordset = db.OpenRecordset("Clients", dbOpenDynaset)
 
    Set myRecordset.ActiveConnection = Nothing
 
    If Rs.RecordCount <> 0 Then
        myRecordset.MoveFirst
        Debug.Print myRecordset.Fields(0) & " was " & myRecordset.Fields(1) & " before."
        'myRecordset.Fields("CustomerID").Value = "OCEAN"
        myRecordset.Update
    End If
 
    strRst = myRecordset.GetString(adClipString, , ",")
    Debug.Print strRst
End Sub
 

I hope this is enough for you all to work off of to help me, if you need anymore information, please let me know.

[ADezii]

DAO does not support Disconnect Recordsets, you must use ADO. I've posted the General Idea below:

Dim conn As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strRst As String
 
strSQL = "SELECT * FROM Clients"
 
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & "Data Source = " & "(database file-path)" & "\mydb.mdb"
 
Set conn = New ADODB.Connection
conn.ConnectionString = strConn
  conn.Open
 
Set myRecordset = New ADODB.Recordset
 
With myRecordset
  .ActiveConnection = conn
  .CursorLocation = adUseClient
  .LockType = adLockBatchOptimistic
  .CursorType = adOpenStatic
    .Open strSQL, , , , adCmdText
End With
 
'Disconnect
Set conn = Nothing
 
'Modify Data, Recordset is disconnected but fully functional
 
'Reconnect using the Connection Object
 
'Update Source
rstDisconnect.UpdateBatch
 
'Check any changes made while disconnected