basshead22
Registered User.
- Local time
- Today, 15:30
- Joined
- Dec 17, 2013
- Messages
- 52
Hello All,
I found this code online to be able to connect my front end to my back end SQL server tables... without having to create a DSN on each person's pc that is using my front-end. I just don't know where to put this code? in the start up form? in a module? Sorry i'm not to savy with programming in access just the basics. Any help would be much appreciated or anyother suggestions you guys may have other than using this method to connect multiple users to my back end sql server. Here is the code.
' The following are some of the parameters the connection will use.
Global gv_DBS_SQLServer As ADODB.Connection
Global gvstr_SQLServer_Name As String
Global gv_SQLServer_DSN As String
Global gvstr_SQLServer_Database As String
'Call the GetConnection Function
' Pass Name of Server; Database Name; Connection Variable; adUseServer; True if using Password; False if not using a Trusted Connection;
' Sample
If GetConnection(gvstr_SQLServer_Name, gvstr_SQLServer_Database, _
gv_DBS_SQLServer, adUseServer, True, False) = False Then
MsgBox "Unable to connect to SQL Server", vbOKOnly, "No Connection"
End If
Public Function GetConnection(ByVal strDSN As String, _
ByVal strDatabase As String, _
ByRef cnLocal As ADODB.Connection, _
ByVal CursorLoc As CursorLocationEnum, _
ByVal UsePassword As Boolean, _
ByVal blnTrusted As Boolean) As Boolean
Dim strConnectString As String
Dim strDisplay As String
On Error GoTo ERROR_HANDLER
GetConnection = False
Retry_Connection:
If cnLocal Is Nothing Then Set cnLocal = New ADODB.Connection
If cnLocal.State = adStateOpen Then
Debug.Print "Connection already open -- -will not reopen!!"
GetConnection = True
GoTo Proc_Exit
End If
With cnLocal
Debug.Print "Use TRUSTED CONNECTION (ABOVE)"
If blnTrusted = True Then
strConnectString = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes"
Else
strConnectString = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"User Id=UUUUUUU;Password=" & DecryptString("PPPPPPPP") & ""
strDisplay = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"User Id=UUUUUU;Password=PPPPPPP"
End If
Debug.Print "Will use Conn String: " & strDisplay
.ConnectionString = strConnectString
.CursorLocation = CursorLoc
.Open
End With
GetConnection = True
Proc_Exit:
Exit Function
ERROR_HANDLER:
Debug.Print Err.Number & vbCrLf & Err.Description
Err.Source = "Module_Connect: "
DocAndShowError
Resume Proc_Exit
Resume Next
Resume
End Function
I found this code online to be able to connect my front end to my back end SQL server tables... without having to create a DSN on each person's pc that is using my front-end. I just don't know where to put this code? in the start up form? in a module? Sorry i'm not to savy with programming in access just the basics. Any help would be much appreciated or anyother suggestions you guys may have other than using this method to connect multiple users to my back end sql server. Here is the code.
' The following are some of the parameters the connection will use.
Global gv_DBS_SQLServer As ADODB.Connection
Global gvstr_SQLServer_Name As String
Global gv_SQLServer_DSN As String
Global gvstr_SQLServer_Database As String
'Call the GetConnection Function
' Pass Name of Server; Database Name; Connection Variable; adUseServer; True if using Password; False if not using a Trusted Connection;
' Sample
If GetConnection(gvstr_SQLServer_Name, gvstr_SQLServer_Database, _
gv_DBS_SQLServer, adUseServer, True, False) = False Then
MsgBox "Unable to connect to SQL Server", vbOKOnly, "No Connection"
End If
Public Function GetConnection(ByVal strDSN As String, _
ByVal strDatabase As String, _
ByRef cnLocal As ADODB.Connection, _
ByVal CursorLoc As CursorLocationEnum, _
ByVal UsePassword As Boolean, _
ByVal blnTrusted As Boolean) As Boolean
Dim strConnectString As String
Dim strDisplay As String
On Error GoTo ERROR_HANDLER
GetConnection = False
Retry_Connection:
If cnLocal Is Nothing Then Set cnLocal = New ADODB.Connection
If cnLocal.State = adStateOpen Then
Debug.Print "Connection already open -- -will not reopen!!"
GetConnection = True
GoTo Proc_Exit
End If
With cnLocal
Debug.Print "Use TRUSTED CONNECTION (ABOVE)"
If blnTrusted = True Then
strConnectString = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes"
Else
strConnectString = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"User Id=UUUUUUU;Password=" & DecryptString("PPPPPPPP") & ""
strDisplay = "Driver={SQL Server};" & _
"Server=" & strDSN & ";" & _
"Database=" & strDatabase & ";" & _
"User Id=UUUUUU;Password=PPPPPPP"
End If
Debug.Print "Will use Conn String: " & strDisplay
.ConnectionString = strConnectString
.CursorLocation = CursorLoc
.Open
End With
GetConnection = True
Proc_Exit:
Exit Function
ERROR_HANDLER:
Debug.Print Err.Number & vbCrLf & Err.Description
Err.Source = "Module_Connect: "
DocAndShowError
Resume Proc_Exit
Resume Next
Resume
End Function