Solved

how to get the data from access by using odbc

Posted on 2000-03-25
10
163 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
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!

 
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

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!

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

749 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