Topic awaiting preservation: Help reading Access db from web

Greetings all. At work we use an Excell spreadsheet for tracking time on projects.
http://www.wavmixer.com/access/sample.xls
What I would like to do is import the XLS timesheets into MS Access and be able to view the stats from the web via our intranet. From the little I've read about this so far, I believe that I may need to write the web pages in ASP. This all has to be done on an IIS server. My question to y'all is this. Can this be done? Am I going about this the wrong way? All though I'm pretty good at HTML, and I'm learning PHP and MySQL, this is my first crack at Access and ASP. Is there a better to accomplish my objective?



(Edited by Wavmixer on 05-30-2004 20:34)

You can acheive the same thing with php, you dont need ASP to do this. What you will need on your server is support to run the php files, which is what you need to set up, then the rest should be pretty straight forward.

Edit..
Is there any reason it has to be from a website at all? Why not just import it to MS Access and allow everyone to use that access file from the server (you can of course have multiple users).. What do you gain from using it over the web?


p.s. I dont know much about setting php up, im pretty sure its mostly a straight forward install to get it started with defaults. I set apache up on my pc once to get perl scripts working and that was pretty straight forward.

(Edited by H][RO on 05-31-2004 02:29)

I do have a LAMP server at work that I set up with red hat 9, however this will be storing valuable project cost data, my boss wants to keep this on windoze behind the glass wall where it can be backed up daily. Currently our IT department does not officially endorse anything but Bill Gates blessed products. I have never used Access before, and I want the db to be searchable and pull up stats by project, user or both. Once I import the Excel file into Access, what GUI do I use to search through the data? This is why I was thinking a web based interface might work. I would like to use a web interface for the data input instead of the Excel spreadsheet, however I am not proficient enough to make the web based time sheet perform the percentage calculations that the Excel file does. If all I needed to do was just track the hours, this would be a piece of cake.

With MS Access you can create forms, or a visual basic interface, which will 100% more flexible then doing it the web way. I have a database i have nearly completed for someone that is quite big, does everyone from a full order system, complete employee timesheet system which each employee uses to log times, quote, invoice tracking, full job tracking include individual part/assembly database, even an internal messaging system!

All with ms access and the vb code that runs it. There are -some- limitations but nothing major that would be of any restriction or that you cant get around.

I have also set some access databases up to import excel files and match it with current data to update pricing etc, uploading files straight to ftp's the lot.

Havnt really come accross much i cant do yet which is great!

So all you need is MS Office, which i assume you have, and you can set the whole thing up - you can run it of a server without installing apache or anything, just put your database file there and share a directory. If you have multiple users its best to split the frontend (GUI) and the actual data, which access even has a built in wizard for.

Like whoa! That sounds awsome. I have no experience with VB, but it is something I would like to learn.... someday. The older I get, the less time there seems to be to do things. But I will add this to my list of things I really need to learn.

Well if you havnt done any other programming before is one of the easier one to learn, ive done c++/c etc before which if anything make it harder - because i want to do the things you can do in those in vb but alot of it u cant! - heh.

I can get my employer to purchase a copy of VB for me to learn with. Do you have any, or can you suggest a place where I can download some VB scripts to play with?

Do you have MS Office? If so and it is a version with MS Access then you do not need to purchase VB separately, its built into MS Access, its actually VBA i think its slightly different. And there are plenty of tutorials on the net to cover pretty much all areas to do everything - im 100% self taught with the help of the internet (i.e no schooling on it) so anyone can do it

Yes, I do have MS Office 2000, but how do I open VBA from Access? I too am self taught. Everything I know about computers and the web, I learned over the internet. One of the very first tutorials I found was doc's "Hands On"

OK, I think I'll try this from another angle. I can write a PERL script that will write to a flat file database. Then I could import the flat file (csv) into MS Access and let Access do the calculations. I do know how to get to the step of importing the csv file, the rest will be a learning experience.

U can import the excel sheet straight into an excel table

Have you looked at my sample?

Did you mean that I can import an excel file straight into access?

Sorry i havnt had a chance, you can import an excel file straight in yes. I think all you have to do is create a button and go through the wizard. Then you just click the button, choose your excel file and it puts it in a table. Something like that anyhow. The hard bit for me was collating the data with existing data and sorting out the garbage .


If i were you i would just search for ms access tutorials in google and do a few just to get some comfort, then search for importing excel files into ms access.

Most access stuff that is on a basic level is easy to do, the hard part is when you have a large database and making sure you organise your structure correctly and your queries.

Oh by the way wavmixer - ctrl-g opens the visual basic part. The way it works is similar to javascirpt etc.. kinda. You can put buttons/fields in etc and they are events, so on those events you tell it what to do in the code (there is a bunch of already coded stuff to choose from tho, such as importing your file). You can also call custom functions/ modules from within that.

Enjoy

OK, here is what I came up with. I started with DBMan and tweaked a bit until I came up with my revision . I have set up a test account username=test password=test. This is not the actual working DB, just a clone hosted on my domain. I then imported the csv file into Access and now have a working Access database. Now what the heck do I do with it? I want to be able to run a query by employee name, project or whatever combination my boss may wish to do. I don't see anyplace in Access to set this up.

(Edited by Wavmixer on 06-17-2004 23:59)

Ok firstly why do you want the website part at all? isnt this just for your LAN?

Go into access, create a new DB. On the left you have tables/queries/forms/reports etc.

You make tables to store your data, you make queries if you like to query your data, and you create forms to edit your data.

Its very easy to do just use the wizards for the most basic stuff.

Press ctrl-g to get to the code behind it all if you want to get more detailed and you can fully do some cool queries and basically whatever you want. So far i havnt found a limitation of access (apart from the dbase not as efficient at sql etc) ... so far..

My boss wants a web based time sheet to replace the current Excel spreadsheet we have been using. He also wants to be able to track costs by project and/or employee. Mine is not to question why. Mine is but to do, not try.

I am leaving for vacation when the sun comes up and will take a look at this when I return. I'm bringing my laptop with me, but I'm going to try my best not to turn it on. I work all year round on computers, and this is supposed to be a vacation.

Thanks for your help and cya next week.

I dont understand why he wants the web based one, its a waste since its sooo much easier to do with access. I would try convince him if i were you.... imo a good employee is one who does question why just FYI :P.. I always tell my bosses what they need to do and why, as long as you know why and can back it up then it shouldnt be too hard to convince them.

Anyhow thats just me :P Have a good holiday