Creating Web Pages

[prstoessel]

Hello all,

Currently, we have a large number of clients where our Access database outputs information to an Excel spreadsheet and then emails the spreadsheets to the respective client (each spreadsheet is unique to each respective client).

The client fills out the appropriate fields and then emails the spreadsheet back to us, where we open the file, copy what we need out of it, paste it into a import-template table in Access, and then run the appropriate macros to manipulate the data back into our system for analysis.

This is being done monthly for dozens of clients. As you can imagine, the latter part of this process is very time consuming. I am thinking there is a way to automate this process via some kind of HTML front end.

I am very proficient in web design and know all about what it takes to make web pages live and visible on the web. I, however, am a novice when it comes to Access. But, I'd like to share my vision and then perhaps get some tips on how to get started:

The vision is for the data that the clients normally receive in a spreadsheet now is visible through an Access form (via the web) that is tied directly to their respective table in our database. Each client has its own secure login password and facility code to call up their own unique data, they make whatever additions/corrections necessary, and when they close their web browser, the data is automatically saved without the hassle of opening email attachments and cutting and pasting. In essence, the data never leaves our server.

Does that sound reasonable? Where might I start?

Also, is Sharepoint Server the only means in which this can happen, or can I use an existing IIS server?

Thanks!

[TheShabz]

Also, is Sharepoint Server the only means in which this can happen, or can I use an existing IIS server?

That right there is your biggest hurdle. If you have Sharepoint then you can use Access 2007-10 to take care of it. In general, though, anytime you need web interfacing, php/MySQL is the better choice. Even if you need everything in Access at the end, you can always connect to the MySQL db afterward. Access just isn't all that great with web connectivity unless you're using Sharepoint.

[prstoessel]

So I take it my vision is certainly possible. Can I create everything in Access and then import to MySQL for web publishing? What resources can I use to learn how to do all of that?

[TheShabz]

I've never actually done a web db so take this with a grain of salt. What I would envision is a php/mysql web setup that merely serves the purpose of interfacing with the users. the db would hold the very minimum. The data that the user enters plus whatever is needed to maintain referential integrity. I would then connect to the db via ODBC in Access. I'd create some queries to bring in the data from the mysql and then once they're in, continue as if the web interface doesn't exist. Information to the other party can then be sent back to the mysql db and displayed on the web page. Mysql would essentially be a middle-man between Access and your PHP web page.

This is assuming you have your system in place and want to continue using Access. You can totally abandon Access as well, and stick with the MySQL db holding everything.

[prstoessel]

Well, I think ultimately we'd like to completely get away from Access and do everything in something more robust, like SQL. For now, though, we have a great deal of our processes that run in Access so the migration will have to be gradual.

In the meantime, setting up an HTML front end for clients to enter data into our tables would cut back on a great deal of time, so we've purchased the latest version of Access to hopefully make that happen.

I am going to look into getting Sharepoint. I know it comes with the premium version of Small Business Server (or at least it used to), so my might do a complete server upgrade to make this happen.

I know nothing of how to use SQL, but would very much like to learn.

[greegan]

Hello I'm new here so I was hoping everyone and anyone to take the hypothetical situation as if prstoessel's was using sharepoint with access.
I am trying to do something similar but I DO want to stick with Access 2010 even if it means using SharePoint. Could someone go into some explanation of how this would be done? Same scenario but the site owner wants to use access with sharepoint. Thought it would be better not creating a new thread. Thank you.

-- g

[Matrix]

I don't know anything about SharePoint, if you want to change your Access application to PHP/MySQL, the process would be like this:

1. Install Apache and PHP.

2. Setup ODBC datasource for your Access database.

3. Build web pages with HTML/PHP to access/update your data through ODBC.

The above steps seem enough for your requirement in your first post. If you want to move away from Access completely:

4. Build more pages to do more Access front-end work, until you are confident that all work can be done with web pages.

So far, you are switching the Access front end, you can continue using your Access application like before.

5. Copy all data into MySQL. The back end switch is a one-time deal.

6. Update PHP code to access MySQL database instead of ODBC datasource.

You can install and test everything on your desktop.

[greegan]

I don't know anything about SharePoint, if you want to change your Access application to PHP/MySQL ...

Thank you for the reply.

I don't know anything about PHP or MySQL. I would LIKE to use Access for the database and website. I have used vba in Excel to program tax calculations. I'm intending to move this over to Access and a website via Access forms hoping the many users I expect to have would have a simple GUI. (By moving over I mean to translate most of the formula vba to expressions and queries within Access)
I am "computer savvey" but not a computer expert. I've taught myself over several years vba and would still consider myself a beginner.
I would REALLY like to use Access for this project.

-- g