I'm not sure if this code is compatible with VBA, but I use this (modified for security reasons) code below in VB6:

Private Function GetConnectionstring(ByVal strDatabase As String) As String

Dim strProvider As String

On Error GoTo ErrorHandler

strProvider = "Microsoft.Jet.OLEDB.4.0"

GetConnectionstring = "Provider=" & strProvider & ";Data Source=" & strDatabase & ";Jet OLEDB"

End Function

Dim strDatabase As String

Set objConnection = New ADODB.Connection

Set strDatabase = "{full_path_to_file}"

strConnection = GetConnectionstring(strDatabase)

objConnection.open strConnection

If you can't use this code I'm sure you can find many samples on the internet. Just search for "OLEDB VBA Access" or something similar.