Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag for Zambia

asked on 

Final deployment of Ms Access Front End (FE) & SQL Server Express 2016 Back End (BE)

How to deploy The Ms Access FE with the SQL Express 2016 Back End (BE) , finally we had the serial port certified by the tax authority senior programmers after removing the stuff that were choking Ms Access and making it restarting after processing  each invoice. Now so far so good

I have created a DSN File with Microsoft ODBC 17, my understanding is that the file DSN  always moves with the front end wherever it goes unlike the system or machine DSN. I will be required to install the SQL server Express on a different server which is about some 600 miles away from the head office. At this site they have 20 workstations which need to be connected to their local server

Tables refresher VBA Code

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "ODBC;DRIVER=SQL Server; " & _
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
Exit Sub
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_Form_Open
End Sub

Open in new window

Questions & help required

(1) Do I have to install also the Microsoft ODBC 17 on each workstation, if yes what BIT is required 32 or 64 ?
(2) Please clearly help me with straight steps required to be followed from ( Enabling the TCP port SQL Server side to Opening up window fire wall) and finally to connecting the workstations. I want to follow your clear and straight to the point advice, I do not want to mess up with SQL server Express engine after a long battle with the serial port, this last part I want to follow the safer root step by step.

I know many professionals here have gone through this , henceforth, your assistance will be highly appreciated.



A screen shoot of the successful linking is attached for your reference

Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Máté Farkas

8/22/2022 - Mon