upgrading access database

[ENEMAL]

Please can someone help me to clearify some issues that have confused me and sorry if this question is not ok for this forum.
I have always read that upgrading from ms access database to sql server is always better idea because perfomance is better in sql server , but i'm confused.
1. with ms access, i can create a database with forms,reports and copy the file ( accdb) to a friend to use on his computer .can that be done in sql server express?
2. I heard of another called SQL Server Compact , can everything done in ms access ( forms , report,attach image......) be done with SQL Server Compact and then distributed as an executable file to a friend?

I know with visual studio i can achieve the all the objectives above but, i just want to know if i can achieve them with just sql server, SQL Server Compact ,mysql or any other one.

note---I'm interested in a single user offline database.

Thanks in advance

 

[Lightwave]

1. NO - SQL Server express or otherwise is only a BACK end platform. It holds tables and you can write code in it equivalent to queries. It stores ZERO graphical interfaces though. If people are talking about transferring an access database to SQL Server or Express they are talking about moving the backend only. Chances are all the forms will still be in Access and held locally. This is classically what is referred to as a Two tier system. The Back Tier being a database - and the front end being the forms. Because this is an Access forums we tend to design the forms in Access but they can be designed in any number of differing frameworks - C++, VB.net, Web forms etc... My SQL Postgres SQL are exactly the same just back end databases.

2. I think you are talking about SQL Lite which is a variant of SQL Server designed to have a smaller footprint for things like mobile devices. Again SQL Lite will be a paired down database which will need to have a link to some other framework to design any user interface in. Chances are you design the UIs in another framework - eg Java , VB.Net, HTML , C++ and then link this design to the instance of SQL either locally or over the web.

If you are talking about a single user offline database then the only options that you really have are Access, Alpha 5, Filemaker Pro - if you go down the Visual Studio line you will need to choose a programming framework AND very likely choose a database ontop of that to link your framework to.

 

[ENEMAL]

Thanks lightwave I'm very clear now. Thanks again

 

[Pat Hartman]

It's SQL Server Express and it's free. The full version of SQL server is quite expensive depending on what level you purchase.

Upgrading the BE to SQL Server isn't "better" unless you have run into some limitation of Jet/ACE. Since you are obviously talking about a small, single-user application, there is nothing to be gained by converting the BE from Jet/ACE to SQL Server and doing so will seriously limit your distribution options since I would guess that the number of folks who have even the Express version of SQL Server installed on their personal computer is so small as to be statistically null.

I do recommend that even single-user apps be split into FE and BE to facilitate future upgrades.

 

[ENEMAL]

Thanks pat, but anytime I split my ms access app into FE and BE, I find it difficult to distribute as the link between them will be broken and cannot link them via vba. I think I will have to find away of doing that.

 

[Pat Hartman]

If the app is intended for single users only, then the simplest thing to do is to have the app verify the link to a table when the opening form runs. If the link is broken, it can silently relink the tables to the BE located in the same directory as the FE.

It gets a little more complicated with a shared app since the FE and BE are never located in the same directory. The BE is always on a server share drive and the FE is always local on the user's C: drive. In this situation, you need to create a form the user can use to "find" the BE for you and then your code will relink it.

 

[ENEMAL]

I will do exactly like you suggested