Display .mdb data help :)

[quinnwizard]

Here is my situation:

My company uses the Acroprint Proxtime terminal to keep track of employees hours for clocking in and clocking out and whatnot. It wasn't until we had the terminal purchased and installed that we realized the terminal doesn't actually display the employee's total hours. So we are looking into a web based solution.

What I need is for the employees to be able to login to a website, and from there access their total hours for the pay week. This information is stored on our local server in a microsoft access database. I have the passwords to the databse and just need help on where to begin and how to get the data to display appropriately.

Thanks in advance for any help.

[AtomLabs]

In order to display the data from an Access database there are many approaches you could take depending on what scripting language you wanted to stick to. Assuming you have a Windows server running IIS you have ASP enabled usually by default so I'll go ahead and give you an example script using that.

Just save the following in a (.asp) file and change it accordingly to meet your needs, every line that is preceded with a single quote (') is just a comment to tell you what's going on and you can just leave it in the script to help you out:

<%
'Setup variables to use for the connection, recordset, connection string, and SQL statement
dim Conn, Rs, ConnStr, SQL

'Create an ADODB connection and record set with the variables
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.RecordSet")

'Set the connection string variable to point to the physical location of the Access database
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyWebSiteFiles\Database\TimeSheet.mdb"

'With the connection object, open the connection string to your database
Conn.Open connStr

'Set your SQL statement to grab data out of the database
'The following will SELECT * (all columns) from a table called (users)
SQL = "SELECT * FROM users"

'Set your recordset object to the results from your SQL statement executed on the database
Set Rs = Conn.Execute(SQL)

'Setup a Do While loop to loop through the recordset until EOF (End Of File)
Do While NOT Rs.EOF

'Response.Write writes text out to the browser
'Rs("fieldName") grabs that named column of data in the current recordset
Response.Write Rs("firstName") & " " & Rs("lastName") & " " & Rs("hours") & "<br>"

'Moves to the next set of data in the recordset
Rs.MoveNext

'Go back up and keep looping until EOF
Loop

'Close the recordset and set your variable to nothing to clean up
Rs.Close
Set Rs = Nothing

'Close the connection and set your variable to nothing to clean up
Conn.Close
Set Conn = Nothing
%>

That should be enough to get your data from the Access database displayed on a webpage, you can of course tweak with the formating of how it gets displayed by playing around inside the loop with either text or HTML formatting of your choosing.

- Jacob Nicholson
- Atom-Labs.com