Best way to distribute SQL Server Express Edition with Access application. (1 Viewer)

shwan

Registered User.
Local time
Today, 09:31
Joined
Nov 18, 2017
Messages
13
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
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?
 

shwan

Registered User.
Local time
Today, 09:31
Joined
Nov 18, 2017
Messages
13
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.
 

isladogs

MVP / VIP
Local time
Today, 16:31
Joined
Jan 14, 2017
Messages
18,186
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
 

ahmed_optom

Registered User.
Local time
Today, 16:31
Joined
Oct 27, 2016
Messages
93
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.
 

Users who are viewing this thread

Top Bottom