VBA :: ADODB Function to Connect to Access

Problem:
Need function to connect to an Access Database.

--make sure set a reference to ADO object in order to use
ADODB.Connection object

Solution:

Function cnConnectToAccessDB(ByVal sFileName As String) As ADODB.Connection
Dim CN As New ADODB.Connection
Dim sCS As String

'you must have ado 2.x installed on machine
' x = 5|6|7

'open this file in Exclusive mode
'comment this line out if you don't want exclusive connection
CN.Mode = adModeShareExclusive


'build connection string
sCS = "Provider=Microsoft.Jet.OLEDB.4.0;"
sCS = sCS & "Data Source=" & sFileName & ";"

'open connection
CN.Open sCS

'return to calling function
'adodb is an object, so you have to SET
Set cnConnectToAccessDB = CN
End Function