Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

how to get the data from access by using odbc

I need to design a system that use the ess db though odbc(if theres a better method, let me know).It is a existing  login/logout db. the problem is that for every person they can login/logout for several times per day. I needs to know how can i get the the earliest login and the last logout record for the particular person. And it must display also the login/logout info  of particular person for a week.
0
tseyeh
Asked:
tseyeh
  • 5
  • 4
1 Solution
 
tseyehAuthor Commented:
access db
0
 
wsh2Commented:
Ok.. it really is somewhat straightforward.. use an ADO connection, a Recordset, and an SQL statement.

<----- Code Begin ----->

Private Sub Form_Load()

  ' Customize this for you own environment
  ' ie.. DSN, UserId, Password.. etc.

  Dim cnNwind As New adodb.Connection
  With cnNwind
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "D:\Data\Compiler\TestData\Nwind.mdb"
    .Open
  End With
 
  MsgBox (xGetLog(2, #12/31/1999#, #12/31/1999#))
 
  cnNwind.Close

End Sub

Private Function xGetLog _
 (
  Dim cnESS As New adodb.Connection
  With cnESS
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "\\Server\Pathname\ESS.mdb"
    .Open
  End With
 
  Dim rsESS As New adodb.Recordset
    With rsESS
      .ActiveConnection = cnESS
      .CursorLocation = adUseClient
      .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
    End With
  End If
 
  With rsESS
    .Source = "Select * From EssLOG " _
      & "WHERE Employee = " _
      & strEmployee & " " _
      & "AND LogDate >= " _
      & dteBegin & " " _
      & "AND LogDate <= " _
      & dteEnd & " " _
      & "Order BY Employee, Date"
    .Open , , , , adCmdText
    If .RecordCount < 1 _
    Then
      MsgBox ("No Records Exist")    
    Else
      Do Until .EOF
        Debug.Print Employee & " " & LogDate
        .MoveNext
      Loop
    End If
    .Close
  End With

End Function


 
0
 
wsh2Commented:
Pssst.. my code copy didn't work in entirety <sigh>.. but that should give you the general idea. When I get back later.. I will try posting again.. <smile>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tseyehAuthor Commented:
can you tell me what is dtebigin and dteend? even my db have multiple login for the same day, all i need to display is the first login and last logout. how can i filter it out the rest
0
 
tseyehAuthor Commented:
why we need to open two adodb connect with the two different db files? btw,why the other one using \\server\****?  I will run this program from same pc as the db. How can i get the db connection while it have password built in
0
 
wsh2Commented:
Please disregard the Form_Load procdure and the Function Header / Footer.. I was in a rush and copied them in incorrectly.

As to the dteBegin and dteEnd statements that was to cover the WEEK requirement you expressed. tseyeh writes: "And it must display also the login/logout info  of particular person for a week."

The "\\server..." part was merely to show that you have to enter the path to the MDB file. I used UNC guessing that you were going after a server.. just change it to DOS notation.

To specify a User and Password change the connection .open to read:

 .open , MyUserID, MyPassword

<----- Snippet Begin ----->

  Dim cnESS As New adodb.Connection
  With cnESS
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "C:\FilePAth\ESS.mdb"
    .Open , strMyUserId, strMyPassword
  End With

<----- Snippet End ----->

BTW.. to activate ADO you need to set a Project reference (VB Menu.. click on Project and then References) to "Microsoft ActiveX Data Objects"

As to only retrieving the first and last record in an Access table.. I am uncertain of the SQL syntax to do that. As the recordset retrieved is sorted.. you may just want to read the first and last records in it.. <smile>.
0
 
tseyehAuthor Commented:
thanks wsh2
i am not  reading the first and last record in the entire table, i read the first and last record on everydays. The table may contain many days. i needs to select first(login) and last(logout) record for each day.
0
 
tseyehAuthor Commented:
btw, i have separate field for login and logout in the table
0
 
wsh2Commented:
tseyeh.. I just saw the code I sent to you under a binary1 question.. wazzup?.. <smile>. And.. <big brown puppy dawg eyes peering upwards>.. why no points here?.. <pout>.
0
 
ndedichCommented:
this code does not work guys
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now