Continuous Form, how to add/update records with external connection

Question

EDIT After some more research I found that I cannot use a continuous form with an unbound form since it can only reference a single record at a time. Given that I've altered my question...

I have a sample form that pulls out data to enter into a table as an intermediary.

Initially the form is unbound and I open connections to two main recordsets. I set the listbox's recordset equal to one of them and the forms recordset equal to the other.

The problem is that I cannot add records or update existing ones. Attempting to key into the fields does nothing almost as if the field was locked (Which it is not). Settings of the recordsets are OpenKeyset and LockPessimistic.

Tables are not linked, they come from an outside access database seperate from this project and must remain that way. I am using an adodb connection to get the data. Could the separation of the data from the project be causing this?

Sample Code from the Form

Option Compare Database
Option Explicit

Private conn As CRobbers_Connections
Private exception As CError_Trapping
Private mClient_Translations As ADODB.Recordset
Private mUnmatched_Clients As ADODB.Recordset
Private mExcluded_Clients As ADODB.Recordset

//Construction
Private Sub Form_Open(Cancel As Integer)
    Set conn = New CRobbers_Connections
    Set exception = New CError_Trapping

    Set mClient_Translations = New ADODB.Recordset
    Set mUnmatched_Clients = New ADODB.Recordset
    Set mExcluded_Clients = New ADODB.Recordset

    mClient_Translations.Open "SELECT * FROM Client_Translation" _
                              , conn.RBRS_Conn, adOpenKeyset, adLockPessimistic

    mUnmatched_Clients.Open "SELECT DISTINCT(a.Client) as Client" _
                          & "  FROM Master_Projections a " _
                          & " WHERE Client NOT IN ( " _
                          & "       SELECT DISTINCT ClientID " _
                          & "         FROM Client_Translation);" _
                          , conn.RBRS_Conn, adOpenKeyset, adLockPessimistic

    mExcluded_Clients.Open "SELECT * FROM Clients_Excluded" _
                           , conn.RBRS_Conn, adOpenKeyset, adLockPessimistic

End Sub

//Add new record to the client translations
Private Sub cmdAddNew_Click()
    If lstUnconfirmed <> "" Then
        AddRecord
    End If
End Sub

Private Function AddRecord()
    With mClient_Translations
        .AddNew
        .Fields("ClientID") = Me.lstUnconfirmed
        .Fields("ClientAbbr") = Me.txtTmpShort
        .Fields("ClientName") = Me.txtTmpLong
        .Update
    End With
    UpdateRecords
End Function

Private Function UpdateRecords()
    Me.lstUnconfirmed.Requery
End Function

//Load events (After construction)
Private Sub Form_Load()
    Set lstUnconfirmed.Recordset = mUnmatched_Clients   //Link recordset into listbox
    Set Me.Recordset = mClient_Translations
End Sub

//Destruction method
Private Sub Form_Close()
    Set conn = Nothing
    Set exception = Nothing
    Set lstUnconfirmed.Recordset = Nothing
    Set Me.Recordset = Nothing
    Set mUnmatched_Clients = Nothing
    Set mExcluded_Clients = Nothing
    Set mClient_Translations = Nothing
End Sub

Mohgeroth, I think your question is a bit confusing and not very clear. I "THINK" what you are asking is how do I use Access continuous forms without using data binding? Is that what you are asking? See if you can re-phrase your question to one sentence. Maybe change your title. Seth — Seth Spearman

Mohgeroth Mohgeroth · Accepted Answer · 2010-05-17T21:34:04

I found that you cannot update/add to a recordset without the connection string containing both a Provider and a Data Provider for microsoft access database files (external). As stated above I was able to pull and display the records but unless manually using ADO Add/Update methods through hardcode, I don't have those capabilities.

This link has the solution for both SQL and JET connections to do this with external database files using these two provider types. The confusing part was adding the password parameters (for a global file password) in the connection string in the connection class I am using.

http://support.microsoft.com/kb/281998

Continuous Form, how to add/update records with external connection

1 Answers