Solved

how to get the data from access by using odbc

Posted on 2000-03-25
10
162 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 59
vb6 connector to mongodb 2 117
Recommendation vb6 to vb.net or others 14 174
VB 6 error 5 in windows 10 but not in XP 7 63
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 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