troubleshooting Question

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

Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia asked on
Microsoft AccessMicrosoft SQL ServerSQL
4 Comments1 Solution40 ViewsLast Modified:
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; " & _
"SERVER=NECTORPRIME\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
Exit_Form_Open:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_Form_Open
End Sub


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.

Regards

Chris


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


SqlLinking.png
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros