Access to SQL server and front end

[Asbestos2]

Hi

Our company is looking to use SQL. Either MSsql or MYsql.

Presently we have Access database system that creates an MDE file with which we enter surveys into. The database then creates html report files with graphics which we then create a full PDF document and supply the customer with the PDF and html an the MDE if they request it.

The scenario is we would like to take this further and add a quote, order process, survey entry, provide html and full report and then Invoice.

Access we know can only handle 2GB size DB. So we think we need MsSql or Mysql. So a few questions I hope you can help with

1. Which would be the best DB to use MsSql or Mysql and why?
2. Would the software be able to handle graphic files and create HTML etc....
3. We need a user screens to run the system, is there any front end software that can be used and easily adapted to what we would need, without heavy programming.
4. We assume we can transfer Access Master DB into SQL easily, is this the case.

Sorry if this is the incorrect forum, but hopefully ou can point me in the right direction.

Thanks

 

[primagic]

You could create an access project file which is an MSSQL backend and access frontend.

If you currently have an access database have you tried using the Upsizing wizard in access. Not sure what versions of access this first appeared but its definately in 2007 and 2010. This will create an SQL database for you. Then you link the sql database tables to the access database. And create your forms accordingly.

Access can handle graphic files and with some strong vba code I am sure you would be able to create html files on the fly.

Me personally I dont see the point of having access as a frontend and sql as a backend. Access has so many limitations such as designing for various screen sizes, each user should probably have a copy of the frontend on their machine, getting updates or changes to the the users is a bit of a logistics nightmare (from my experience). Especially when you have 10 or more users.

I reckon you should go towards a web frontend with asp.net or php. The benefits outweigh the access frontend. Multiple users can use the web frontend at once. Only need to roll out updates to one website and all users see the changes. I use asp.net and the third party controls that can be implemented for graphs, and UI is pretty good

 

[JoeAtWork]

1. Which would be the best DB to use MsSql or Mysql and why?
I would use MSSql but only because I am familiar with it and not with MySQL. From what I've read their capabilities are similar. It also depends on what version of SQL Server you are talking about. Since you are comparing with MySQL, which AFAIK is free, then I assume you are considering SQL Server Express, which is also free but is limited to 4 GB.

2. Would the software be able to handle graphic files and create HTML etc....
Databases just handle data. SQL Server just by itself has no GUI, you need to build that with some programming tool. I'm sure it is the same for MySQL.

3. We need a user screens to run the system, is there any front end software that can be used and easily adapted to what we would need, without heavy programming.
Any major programming language can connect to SQL Server or MySQL, including Java, .NET, Visual Basic, etc. I don't know what you consider "heavy programming", but from what you describe I don't think you can get away with simple bound controls and wizard generated code. You're going to have to do programming regardless of what database you use (in fact the code will hardly be any different between either system).

4. We assume we can transfer Access Master DB into SQL easily, is this the case.
That really depends on the complexity of your database. If it's just tables and queries that don't rely on Access-specific functions, the Access Upsizing Wizard can do the bulk of the work getting it into SQL Server. I don't know what migration tools might be available for Access to MySQL.
If your queries rely on a lot of Access or custom functions, you may have a lot of work to do to convert those functions into stored procedures on the database (or write them into your front-end code).

 

[gmmastros]

JoeAtWork,

If you use SQL Express 2008 (or 2008R2) the database size limit is 10 GB, not 4.

In my opinion, it's better to use Microsoft SQL Server. I say this because it can scale up to an enterprise level database. Look at it this way... SQL Express can handle DB's up to 10 Gigs, but you will eventually run in to a limit on the number of transactions you can handle simultaneously. With SQL Express, you are limited to a single processor handling the data requests. If you run in to this limit, you can tune your DB with indexes and better queries to improve performance, allowing single SQL Server computer to process more and more concurrent transactions. Eventually you may hit another limit, at which time you could upgrade to a better (more capable) version of the database engine, including mirroring (so your DB will always be available) and clustering so you can spread the workload over many computers instead of just one.

To change DB engines is a real pain to do. Upgrading from SQL Express to SQL Standard or Enterprise is a breeze. With Microsoft SQL Server, you will be able to scale up to enterprise level functionality simply by paying money and without requiring programming.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

We used Access as a front end for several years but if possible use something like .NET.

Remember when moving from Access to MS SQL Server do NOT let Microsoft do all the work without checking what is being done. Microsoft will try and decide what a column is instead of using what the column is defined as in Access. Example: In the US postal codes are numeric which may have a leading zero. Microsoft will take the Access text field and create a float column in the MS SQL table.

Sorry this is a pet peeve of mine as I have to work with a legacy database.

Good luck,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Asbestos2]

Hi

Thanks all of you for your valuable replies. I cannot see us managing to use MS SQL enterprise due to the cost and the Express would be to small in size.

Therefore if we used MySQL (which I am informed is free)and imported and built our DB in this from Access;

1, We could use Access front end but this would be hard to adminster as nay changes we would have to put on each workstation.
2. .NET, ASP.net (is it the same). Does anyone know any good links for some basic training and perhaps aimed at using and linking to a database. I have looked on the Internet but alot of it is aimed at just creating web sites.

Many Thanks