Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-03-2019, 03:09 AM   #1
shwan
Newly Registered User
 
Join Date: Nov 2017
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
shwan is on a distinguished road
Best way to distribute SQL Server Express Edition with Access application.

Hello,
I have built an Access application that is a frontend to an SQL server backend. Now I want to distribute this application to end users, those users will, of course, have different server machines.
I have some experience with installers like SSE Setup but it would be very much appreciated if somebody can tell me what is the best way to distribute and configure end-user machines. Some users are far away from me and it would be preferable to make installation and configuration as simple as possible, so, for example, I can make a video or something, and they can follow.

Although I'm using SQL server, most users will use the application on single machines. My initial thought was using code like below after installation of SQL server express on the user's machine; I believe it is called DSN less:

Code:
Private Sub btnChangeSource_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qry As DAO.QueryDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        ' Only make a change if the table is a linked table
        If Len(tdf.Connect) Then
            tdf.Connect = Me.txtNewSorce
            tdf.RefreshLink
        End If
    Next
    
For Each qry In db.QueryDefs
    If Left(qry.Connect, 4) = "ODBC" Then
    qry.Connect = Me.txtNewSorce
    'Print what it is after the amendment
End If
Next qry
    MsgBox "Done"
End Sub
This code resides in a form that the user needs to run only one time.The connection string will be something like this:

Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost;Database=MyDB;UID=user;PWD=pass;Trusted_Connection=No;
I'm not sure about this whole procedure and need some advice.

Regards

shwan is offline   Reply With Quote
Old 08-03-2019, 03:54 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,996
Thanks: 114
Thanked 3,011 Times in 2,738 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to distribute SQL Server Express Edition with Access application.

Have a look at this thread from another forum.
Its a long thread but post #18 is the most relevant
It includes an explanation of how I use DSN less connections and the code involved https://www.accessforums.net/showthr...140#post435140

Hope that helps
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
shwan (08-03-2019)
Old 08-03-2019, 07:28 AM   #3
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,819
Thanks: 57
Thanked 1,274 Times in 1,255 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Best way to distribute SQL Server Express Edition with Access application.

Hi. Would you be distributing/deploying/installing your app to users without SQL Server installed? If so, do you already have a step for installing SQL Server on their machine?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-03-2019, 01:09 PM   #4
shwan
Newly Registered User
 
Join Date: Nov 2017
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
shwan is on a distinguished road
Re: Best way to distribute SQL Server Express Edition with Access application.

Quote:
Originally Posted by theDBguy View Post
Hi. Would you be distributing/deploying/installing your app to users without SQL Server installed? If so, do you already have a step for installing SQL Server on their machine?
No SQL server is installed on users' machines, and I want to automate this process and make it painless as much as possible.
While the code provided above is very helpful when you want to update the front end, it does not involve the hassles of new SQL server installatiion and deploying the database files.
shwan is offline   Reply With Quote
Old 08-03-2019, 02:13 PM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,996
Thanks: 114
Thanked 3,011 Times in 2,738 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to distribute SQL Server Express Edition with Access application.

I would be very surprised if you are able to automate the installation of SQL Server from a script file as there are too many decisions that need to be made by each client to match their individual circumstances.
Suggest you ask that question on a specialist SQL Server forum
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 09-20-2019, 09:25 AM   #6
ahmed_optom
Newly Registered User
 
Join Date: Oct 2016
Posts: 58
Thanks: 16
Thanked 1 Time in 1 Post
ahmed_optom is on a distinguished road
Re: Best way to distribute SQL Server Express Edition with Access application.

you should really be thinking about using azure/ cloud sql.

Easier for everyone. You can distribute a pre set up FE which connects to the database for that client in the cloud.
ahmed_optom is offline   Reply With Quote
Old 09-20-2019, 01:18 PM   #7
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 664
Thanks: 9
Thanked 53 Times in 51 Posts
kevlray will become famous soon enough
Re: Best way to distribute SQL Server Express Edition with Access application.

SQL Server Express does have silent install (I have never done it myself). Check out this link, https://social.msdn.microsoft.com/Fo...rum=sqlexpress


kevlray is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL server express edition aman SQL Server 12 07-31-2017 04:52 AM
vb.net application (sql server express) Surjer SQL Server 1 12-01-2016 01:16 PM
SQL Server Express Edition DBinPhilly General 2 05-28-2013 02:06 PM
SQL Server 2008 Express Edition on Vista Rockape SQL Server 10 10-16-2010 07:56 AM
Sql Server 2005 Express Edition Support Multiple User jaydwest SQL Server 1 11-28-2006 09:03 AM




All times are GMT -8. The time now is 06:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World