?
Solved

how to get the data from access by using odbc

Posted on 2000-03-25
10
Medium Priority
?
166 Views
Last Modified: 2010-05-02
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
Comment
Question by:tseyeh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 1

Author Comment

by:tseyeh
ID: 2656665
access db
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2657221
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
 
LVL 14

Expert Comment

by:wsh2
ID: 2657224
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
Technology Partners: 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!

 
LVL 1

Author Comment

by:tseyeh
ID: 2657829
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
 
LVL 1

Author Comment

by:tseyeh
ID: 2657877
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
 
LVL 14

Expert Comment

by:wsh2
ID: 2658005
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
 
LVL 1

Author Comment

by:tseyeh
ID: 2658062
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
 
LVL 1

Author Comment

by:tseyeh
ID: 2658066
btw, i have separate field for login and logout in the table
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 150 total points
ID: 2663119
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
 

Expert Comment

by:ndedich
ID: 1549
this code does not work guys
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question