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:
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
For Each qry In db.QueryDefs
If Left(qry.Connect, 4) = "ODBC" Then
qry.Connect = Me.txtNewSorce
'Print what it is after the amendment
This code resides in a form that the user needs to run only one time.The connection string will be something like this:
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.