In over my head Access 2010 -> MSSQL

[jGiblets]

I'm taking on a big project for work and I could really use some input as to the best way to proceed/set things up.

 

The company uses Access 2010 (with about 15 years of database information stored).  Access controls just about every step of our workflow (Inventory, customer management, sales, shipping, tracking, reports, etc...) and now we want to go on the web.

 

The goal is to create a dynamic website that will pull information from inventory tables at a users request and display in the browser.  At the moment, the project won't require (or allow) database changes from users (no user accounts or personalized pages).  Just little drop downs that let them select the part, size, and other to see size, specifications and availability.

 

Assuming that we convert the database to MSSQL using the upsize wizard in Access (and not Sharepoint) so that the end result is an Access front end calling on a MSSQL database, will I still be able to use PHP to get information?

 

I'm confused and crunched for time.  Will this setup work?

 

Access 2010 modified by employees -> Linked to MSSQL database -> results displayed at call in web browser by customers

[cpd]

Yes this is perfectly possible with some hiccups in the way. There are a few PHP drivers which can be used to link to a MSSQL database including the SQLSRV driver which is currently supported by Microsoft. However, this does require a windows platform and as such would probably mean an IIS server. Whilst this isn't the end of the world it can be a little outside of the comfort zone as most people are used to Apache on a Linux platform.

 

You can however, implement the PDO ODBC class extension which has the ability to connect with an MSSQL database. Having used this recently, I have found issues with retrieving out parameters in stored procedures (a major part of MSSQL Server) which has been a major set back. PDO ODBC will also work on a Windows platform so is probably the best way to go if your looking for a cross platform implementation. If your going to be using a windows platform the SQLSRV driver is generally easier to implement and write SQL classes with.

 

Bare in mind the PDO ODBC is a relatively new class extension and was only recently updated (I believe) so it may get some serious attention in the coming months as its one of very few methods of connecting to an MSSQL Server from a Linux platform. There are others such as FreeTDS (or something similar) which is actually implemented in the PDO ODBC class so you may as well go with the PDO connection.

 

Hope that helps you with your decision making.

[Muddy_Funster]

SQLSRV runs without any issue in a standard apache setup, no need to set up IIS at all.  XAMPP comes with it bundled if you want a useable development example.  What I will say is that it is hugely important to make sure that you structure your SQLServer tables and schemas properly, using views and - as CPD touched on - stored proc's for the complex lookups.  Another thing to think about is bandwidth, if your using PHP and a local server you will be allmost gauanteed to benfit from a properly cached model, if the SQL server is going to be hosted you will also find huge issues linking access to it over an internet connection for anything more than adding a single record. The other thing you will find is that Access has it's own ideas on what the SQL language is, and doesn't seem to have the first clue about optimistic locking on remote data sources (either on a LAN or WAN setup) so expect to get a lot of timeouts and "already opened exclusivly by another user" messages.

 

Personaly I suggest taking Access and having a little bonfire in the carpark with it, and use a web interface for everything on a user controlled basis.  There are a lot of good, cheep (as in less than it would cost for 10 copies of access 2010) ERP programs out there that run a propper multi-user interface over an SQL backend that will make things go much more smoothly in the long term.

[cpd]

Just to clarify, I didn't mean you require IIS for SQLSRV. I meant if your using a Windows Server you will most probably end up using an IIS server as very few hosts offer Apache in a Windows platform.

 

Additionally, I would heed Muddy's Access -> Bonfire suggestion. Microsoft have been trying to dump Access and replace it with SQL Server Express for a number of years now; due to SQL Server's complexity Access is still taught in Colleges and Universities which results in many people sticking with it.

 

The power of SQL Server is far greater then Access so I would definitely convert.

[jGiblets]

Thank you for the replies!

 

I would heed your advice (campfire) gladly, however, I've been told in no uncertain terms that we are sticking with Access.  My job is just to make the round peg fit in the square hole.

 

Here's my next question.  Can I do this without conversion?  I just want the web to pull info, not write it.

 

This is most likely my own naivete, but it would seem to me that if the db info is there and all I really want is read access to the tables, couldn't I just write simple select commands based on user input?  Would that work in any markup (including asp)?

 

*edit* I am thinking that this last modification might move this out of an mssql question, would it be better to post this somewhere else? */edit*

[jGiblets]

Found an answer.

 

http://oakleafblog.blogspot.com/2010/07/linking-microsoft-access-2010-tables-to.html