There are 2 ways to do it.

First, you can create a DSN in the Control Panel (ODBC applet) and then using that DSN to connect:

Code:
Dim cn As New ADODB.Connection

cn.Open "DSN=MyDSN"
Where MyDSN is a DSN you've created.

Or you can use DNSless connection:
Code:
Dim cn As New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "C:\MyDB.mdb", "admin", ""
Assuming that C:\MyDB.mdb is your database.