Solved

how to get the data from access by using odbc

Posted on 2000-03-25
10
161 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
  • 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 50 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

785 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