Unable to connect to SQL Database for Database name verification

Upgraded from access adp to access 2013 accdb linked tables. Successfully linked the tables to sql server 2019 using SQL Server Driver 17. The database uses a module and lots of stored procedures in the Moduled, forms and reports. The main issue I am having right now is running some code using a connection string to check the what database I am connected to using a trusted connection. The connection string is a constant. It fails to connect every time. I have varied my connection strings, but nothing seems to work. I am not a seasoned programmer so I need some assistance. The connection string looks like this:

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabasA;Data Source=ServerA" The code in the module looks like this:

Sub CheckDBName()
Dim m As String
Dim i As Integer
Dim i2 As Integer

On Error Resume Next
m = CurrentProject.Connection.Cnn
i = InStr(m, "Initial Catalog=")
i2 = InStr(m, ";Data Provider=")
m = Mid(CurrentProject.Connection.Cnn, (i + 16), (i2 - (i + 16)))
If m <> "DatabaseA" Then
If m = "DatabaseB" Then
MsgBox "You are currently connected to the TEST DATABASE. If you feel you have received this message in error, please contact Production Control."
Else
MsgBox "Connection to the DatabaseA could not be made. Please contact production control."
End If
End If
CurrentProject.Application.SetOption "Refresh Interval (sec)", 600
End Sub

0 answers