ADO connection, Preferred method

[timothyl]

Good morning, in connecting with ADO is there a preferred method

a) Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

b) Dim cnn As ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "MyPath\MyDatabaseName.mdb"

or a third method ?
any comments or insights is appreciated, thanks

 

[boblarson]

If you are connecting to the same database that the code is in, you use

Set cnn = CurrentProject.Connection

or else you will find yourself with a "too many connections" error.

For external Access files you would use one of the connection strings at

www.connectionstrings.com

depending on the version (Access 2007 or other) and whether you are using User Level Security and all there are a bunch of different ways to do it. So I would check there and look for the exact situation you are in.

 

[timothyl]

Thanks for the link and comments, both are helpful