Question Which SQL Server version to choose when upsizing Ms-Access 2010 apps.

[matiyas]

I have the following questions regarding this topic:

1.I want to upsize my Ms-Access app to SQL server back-end and then PACKGE AND DISTRIBUTE it. So after i upsize my app, how will i distribute it in one package and or "server and client"
the issue is how will i package the back-end which is in SQL server.

• I use "Advanced Installer" to package my apps.

2.I have seen in certain post that not all SQLserver versions integrate well with Ms Access applications. if so, which one should i use, i.e server 2008,2012

 

[Guus2005]

I don't think SQL Server is your problem but your "Advanced Installer" is.

I use SQL Server as a backend since version 6.5. Never had any problems. Ofcourse it all depends on what you do with the SQL Server tables in SQL Server. If you use the table/row triggers to do some actions using SP in SQL Server you might end up with a useless database in Access. So you have tyo be very carefull with that. But as long as you use the tables for what they are, just tables without any fancy stuff Access works well with any SQL Server version.
I'd go with an Express version. They are well suited for a limited number of users with lots of data.

HTH

 

[matiyas]

thanks, I Chose SQL server2008 express, now the upsizing part is the problem. do i need to convert anything to SQL statements for me to start using it? My app has many tables/relationships to other tables and queries...will it be difficult to Upsize? ..because I only need the back-end to hold tables and the front-end to be Ms-Access. also what about Adp's is it better than linked tables in SQLserver?:banghead:

 

[mdlueck]

Sounds like you are very much a SQL BE DB newbie, so I offer...

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[Pat Hartman]

ADPs are not an option since they have been deprecated and besides, you would need to convert your existing application and that would not be easy. Are you really intending to install SQL Server as part of your application install? The built-in tool and the commonly used SageKey tool can't do it. You will need to use a different packaging product because lots of prompts need to be responded to as part of an SQL installation.

An Access app linked to SQL Server tables does NOT need to include SQL Server as part of its install package. Generally the database would be installed on the server as a separate process by the customer's DBA. Then your install package would only install the FE and possibly the Access runtime for each user.

 

[matiyas]

Thanks pat for the detailed info, i think now i have understood. Anyway, I don't want to include the SQL Server back-end database as part of the install package, but then how do i package the back-end?
Lets say i have a school management app and i want to distribute it/ make it available for download and easy for the end user to install and run. The front end is not a problem but what about the back-end?

 

[mdlueck]

The front end is not a problem but what about the back-end?

You could build a SQL script suite to build the desired database. SQL Server schema is quite easily exported to SQL DDL scripts. Then you can build a script to drive all of those separate SQL DDL scripts. I drive my SQL DDL scripts with NT Command Script (Batch):

Sample of my driver script:

@ECHO OFF
REM /************************************************************************************/
REM /* FILENAME       :  1_Fandango_Setup.cmd                                           */
REM /* TYPE           :  Windows NT Command Script                                      */
REM /*                                                                                  */
REM /* AUTHOR         :  Michael D Lueck                                                */
REM /*                   mlueck@lueckdatasystems.com                                    */
REM /*                                                                                  */
REM /* NEEDS          :                                                                 */
REM /*                                                                                  */
REM /* USAGE          :                                                                 */
REM /*                                                                                  */
REM /* REVISION HISTORY                                                                 */
REM /*                                                                                  */
REM /* DATE       REVISED BY DESCRIPTION OF CHANGE                                      */
REM /* ---------- ---------- -------------------------------------------------------    */
REM /* 04/17/2011 MDL        Initial Creation                                           */
REM /************************************************************************************/

SET DBhst=sqldev1
SET DBdb=Fandango
SET DBuid=dbuser
SET DBpwd=dbpasswd

SET DblQuote="
REM Reset the color coding with a pair matching Double Quote character"

SET ScriptPath=C:\Documents and Settings\c_mlueck\My Documents\Dev\Fandango\Development\2.1.0.38\Source\DDL\

SET CMDLineTemplate=sqlcmd.exe -S %DBhst% -d %DBdb% -U %DBuid% -P %DBpwd% -X -i %DblQuote%%ScriptPath%

REM Shared UDF's
SET CMDline=%CMDLineTemplate%fTRIM.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%frevconv_ConvA2N.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%frevconv_ConvN2A.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%fverconv_ConvDB2Disp.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%fverconv_ConvDisp2DB.sql%DblQuote%
%CMDline%

REM srvcfg table related scripts
SET CMDline=%CMDLineTemplate%srvcfg.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%srvcfg.clsObjSrvCfgTbl_LocateKeyValue.sql%DblQuote%
%CMDline%
SET CMDline=%CMDLineTemplate%srvcfg.clsObjSrvCfgTbl_LocateRunTimeEnvironment.sql%DblQuote%
%CMDline%

 

[Pat Hartman]

I generally deliver a .bak file for the SQL Server backend. From SSMS, just right-click on the database and choose the backup option from the tasks menu. The user's DBA can just restore it. I do that because in addition to the schema, many of the lookup tables are populated when the app is installed and this is a pretty simple way to do it.

Once you involve SQL Server, it is unlikely that the user can actually install the BE himself. Most folks don't have the necessary permissions or knowledge of SQL Server Management Studio to do the restore. The client DBA will need to install the BE before the user installs the FE so that he has a database to link to. You also may need to worry about DSNs unless you are controlling the linking with code and not using DSNs at all.