Sql server connection - no dsn (1 Viewer)

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

in a module - global variables can only be declared in a module. Don't forget to put Option Explicit at the top.

Not so important in this case since the answer is determined in the first couple of rows, but in future please use the code tags to retain the code indentation.

Thank you CJ for your answer ! makes sense. I know this might sound stupid but could you give me a sample of what i should directly past into the module without the extra header stuff... like i mentioned im very new to programming in access. Much appreciated.

I would refer back to where you got the code from, it should be explained there.

But as far as I can see you would paste the whole thing - tho' I would comment out the sample which shows you how to call it.