Using with statement to Open aTable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
iholder said:
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.
Click to expand...

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
 
Please note the [UserId] is the id of the first intial of first name and the
last. ex:iholder CurrentUser() is giving me the system admin id. this is not
what I want
 
iholder said:
Please note the [UserId] is the id of the first intial of first name
and the last. ex:iholder CurrentUser() is giving me the system admin
id. this is not what I want
Click to expand...

Ok, then do this:

dim idUser as string

idUser = GetWhatEverYouWant()

CurrentDb.Execute "insert into tblLogin (UserID, Login) Values ('" &
idUser & "', Now());"

(Must be one line)
Please note the [UserId] is the id of the first intial of first name
and the last. ex:iholder
Click to expand...

Sorry, what is an ex:iholder?
(my english is not so good)

The Username of logged-in User you can get from:
Environ("UserName")

Acki
 
CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"
Click to expand...
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 said:
iholder said:
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.
Click to expand...

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
Click to expand...
 
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 said:
CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"
Click to expand...
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 said:
iholder said:
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.
Click to expand...

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
Click to expand...
Click to expand...
 
Hi,

iholder schreibselte:
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.
Click to expand...

Do you mean that:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUser As String

strUser = "IH"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogin", dbOpenDynaset, dbAppendOnly)

With rs
.AddNew
!UserID = strUser
!Login = Now
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing

If yes, this makes the same (a little shorter):

CurrentDb.Execute "insert into tblLogin (userID, Login) " & _
"Values ('IH', Now());"

Acki
 
Thank You Jorg,

This is what I want to do. The recordset method. The other method will not
work. It does not allow for more than two field parameters. I need to
append four fields of info. The fields are
[UserId],[LogInTime],[LogInDate],[LogOutTime]. The time field must be
separate. I need to do a time calculation. How long the user was logged on
the system.

What do you think now. Is this a okay approach?
 
I am getting an error with the code.
Dim db As DAO.Database "this line"
Error message: Compile error - user define type not defined.



iholder said:
Thank You Jorg,

This is what I want to do. The recordset method. The other method will not
work. It does not allow for more than two field parameters. I need to
append four fields of info. The fields are
[UserId],[LogInTime],[LogInDate],[LogOutTime]. The time field must be
separate. I need to do a time calculation. How long the user was logged on
the system.

What do you think now. Is this a okay approach?

Jörg Ackermann said:
Hi,

iholder schreibselte:


Do you mean that:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUser As String

strUser = "IH"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogin", dbOpenDynaset, dbAppendOnly)

With rs
.AddNew
!UserID = strUser
!Login = Now
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing

If yes, this makes the same (a little shorter):

CurrentDb.Execute "insert into tblLogin (userID, Login) " & _
"Values ('IH', Now());"

Acki
Click to expand...
Click to expand...
 
Hi,
I am getting an error with the code.
Dim db As DAO.Database "this line"
Error message: Compile error - user define type not defined.
Click to expand...

You must add a DAO reference to your project.
If you go under the 'Extra' menu in VBA and under 'References' put a
tick next to "Microsoft DAO 3.x Object Library".

If you use A2k and above it should be
Microsoft DAO 3.6 Object Library.

Acki
 
Everything work fine. Now I need to code the LogOut Process.

That table and record need to be updated upon logout the [ LogOutTime] needs
to be update with the Log Out Time.

Do I need to lock this record or search for this record by userid to insure
that the correct log out time is apply to the correct user.

What is the correct approach and code needed

Thank You
 
iholder said:
Everything work fine. Now I need to code the LogOut Process.

That table and record need to be updated upon logout the [
LogOutTime] needs to be update with the Log Out Time.

Do I need to lock this record or search for this record by userid to
insure that the correct log out time is apply to the correct user.

What is the correct approach and code needed
Click to expand...

Take the .AddNew out and set a .Findfirst... in.
Synthax and samples you can find in the Access-help Findfirst<F1>

Acki
 
Back
Top