Re: Using with statement to Open aTable

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brendan Reynolds (brenreyn)
Date: 02/17/05 

Date: Thu, 17 Feb 2005 13:38:11 -0000

You can still use the Execute method, you just have to replace the reference
to CurrentUser() with a reference to wherever your user ID is stored. And
this will be the most efficient way to do it. if you really want to use a
recordset though ...

Public Sub AddRecord()

    'Using DAO

    Dim db As DAO.Database
    Dim rstd As DAO.Recordset

    Set db = CurrentDb
    Set rstd = db.OpenRecordset("SELECT TestText, TestDate FROM tblTest", ,
dbAppendOnly)
    With rstd
        .AddNew
        .Fields("TestText") = "My User ID 1"
        .Fields("TestDate") = Now()
        .Update
        .Close
    End With

    'Using ADO
    Dim rsta As ADODB.Recordset

    Set rsta = New ADODB.Recordset
    With rsta
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockOptimistic
        .Source = "SELECT TestText, TestDate FROM tblTest"
        .Open
        .AddNew
        .Fields("TestText") = "My User ID 2"
        .Fields("TestDate") = Now()
        .Update
        .Close
    End With

End Sub 

-- 
Brendan Reynolds (MVP)
"iholder" <iholder@discussions.microsoft.com> wrote in message 
news:29413D83-E891-41F7-8753-F339912DA797@microsoft.com...
> CurrentDb.Execute "insert into tblLogin (userID, Login) Values
>> (CurrentUser(), Now());"
> This code is not working for me. All I need is a easy way to open a table
> and add a record.  I am not trying to get network user info.  using a
> recordset and "with statement.
>
>
> "Joerg Ackermann" wrote:
>
>> iholder wrote:
>>
>> > I am setting up login tracking.  Need to open table in the add mode
>> > and data to the following fields . [UserID], [LogInTime], [LogDate]
>> >
>> > Table is not attached any form or report.
>> > Is the "with" statement the right approach, if not please suggest.
>>
>> I would not split Logdate and logintime in two fields.
>> It's simple to get Date or time using Format-Function if needed.
>>
>> CurrentDb.Execute "insert into tblLogin (userID, Login) Values
>> (CurrentUser(), Now());"
>>
>> Acki
>>
>>

Relevant Pages

  • RE: open arguments.
    ... Can you add a value to the Reference column? ... Dim rst As Recordset ... This opens the form specified by stDocName in data entry mode and passes the ...
    (microsoft.public.access.modulesdaovba)
  • Re: rs.Close vs. Set rs = Nothing
    ... "Rob" wrote in message ... method of the recordset object to which rs is a reference. ... Dim rs1 As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query error after converting
    ... If removing the reference to ADO solved the problem, ... I already mentioned that Recordset is an object in both the ADO and DAO ... libraries for much of its functionality, and any external library it uses is ...
    (microsoft.public.access.conversion)
  • Re: rs.Close vs. Set rs = Nothing
    ... only reference to ... >that recordset object, then destroying the reference ... > Dim rs1 As DAO.Recordset ... >can't be cleared from memory. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Export text file
    ... It is better to use just DAO and no ADO reference at all. ... recordset objects in the drop down list. ...
    (microsoft.public.access.externaldata)