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
(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,
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
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.
ASKER
Dim blnRelink as boolean
blnRelink = AttachSqlServer( Hostname ,Database,Username,PassWor d)
Again Istill have an error , it says variables not defined ( Hostname ,Database,Username,PassWor d)
Regards
Chris
blnRelink = AttachSqlServer( Hostname ,Database,Username,PassWor
Again Istill have an error , it says variables not defined ( Hostname ,Database,Username,PassWor
Regards
Chris
ASKER
Here is the on click event
Private Sub CmdSklserver_Click()
Dim blnRelink as boolean
blnRelink = AttachSqlServer( Hostname ,Database,Username,PassWor d)
End Sub
Private Sub CmdSklserver_Click()
Dim blnRelink as boolean
blnRelink = AttachSqlServer( Hostname ,Database,Username,PassWor
End Sub
ASKER
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;D ATABASE=Sa lesAccount ing;Truste d_Connecti on=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
(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;D
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so its called like
Open in new window
Replace the arguments and you should be good to go