Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

Re opening Re-linking Access to Sql server on different network

My apology our experts , I closed the the question about a code to re link  access to sql server back end on different network wrongly without proper testing. The code below could not work see the errors below:

(1) I have put the code in a module and tried to call it on a login form as follow ( Call AttachSqlServer), this was called on the on-click event, it gave me an error as argument not optional
(2) The following part of the called is also not compiling:

Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
   End Function


I the issue is on the call function

Below is how I have replaced the parameters:


Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
' 2016-04-24. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
   
    Dim strConnect      As String
    Dim strName         As String
   
    On Error GoTo Err_AttachSqlServer
   
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, PassWord)
   
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print Timer, tdf.Name, tdf.SourceTableName, tdf.Connect
                DoEvents
            End If
        End If
    Next
   
    For Each qdf In dbs.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print Timer, qdf.Name, qdf.Type, qdf.Connect
            qdf.Connect = strConnect
        End If
    Next
    Debug.Print "Done!"
   
    AttachSqlServer = True
   
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
   
Err_AttachSqlServer:
    'Call ErrorMox
    Resume Exit_AttachSqlServer
   End Function

Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String) _
    As String

' Create ODBC connection string from its variable elements.
' 2016-04-24. Cactus Data ApS, CPH.

    Const AzureDomain   As String = ".windows.net"
    Const OdbcConnect   As String = _
        "ODBC;" & _
        "DRIVER=ODBC Driver 13 for SQL Server;" & _
        "Description=Cactus TimeSag og Finans;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={USER\SQLEXPRESS};" & _
        "DATABASE={Accounting};" & _
        "UID={};" & _
        "PWD={};" & _
        "Trusted_Connection=Yes;"
       
    Dim FullConnect     As String
   
    If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
        ' Azure SQL connection.
        ' Append servername to username.
        Username = Username & "@" & Split(Hostname)(0)
    End If
    FullConnect = OdbcConnect
    FullConnect = Replace(FullConnect, "{USER\SQLEXPRESS}", Hostname)
    FullConnect = Replace(FullConnect, "{Accounting}", Database)
    FullConnect = Replace(FullConnect, "{}", Username)
    FullConnect = Replace(FullConnect, "{}", PassWord)
   
    ConnectionString = FullConnect
End Function

Kindly gustav help me again the code , could it be I'm missing something here?

Regards

Chris
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Its a function not a Sub
so its called like
Dim blnRelink as boolean
blnRelink =  AttachSqlServer( Hostname ,Database,Username,PassWord) 

Open in new window

Replace the arguments and you should be good to go
Or you could install my Linked Table Manager (LTM) addin (read the article here) on one of the clients computers and then just run it.
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Dim blnRelink as boolean
blnRelink =  AttachSqlServer( Hostname ,Database,Username,PassWord)

Again Istill have an error , it says variables not defined ( Hostname ,Database,Username,PassWord)

Regards

Chris
Here is the on click event

Private Sub CmdSklserver_Click()
Dim blnRelink as boolean
blnRelink =  AttachSqlServer( Hostname ,Database,Username,PassWord)
End Sub
Sorry people out there, maybe I did not make myself clear on this, I have a working DSN less Connection ODBC, this works very well as long as the following do not change:

(1) The network remain the same
(2) The server name remain the same

Now the problem comes when I move the application to a different network and a new server name, the DSN less connection fail to connect. I have tried to come up with a code below to be used at start up, well it appear to work as long all the parameters remain the same

(1) The network remain the same
(2) The server name remain the same.

Private Sub CmdSklserver_Click()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=SalesAccounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "salesAccounting Package"
End Sub

Now is there a way I can pass the connections information for the new server and new network  in Ms Access run-time environment. I have no problem where there is a full Ms access, it can be a question of recreating the DSN Less Connection. This may mean buying a full Ms Access at-least for one machine to accomplish this, but surely there should a way to do it at start up form .

Plan B if the above code fail and there is no other solution, then we will have to budget for atleast one full license for MS Access per client for configuration purpose.


Regards

Chris
ASKER CERTIFIED SOLUTION
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag of Zambia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial