ADO Access 2003 -> SQL Server

I'm a relatively new developer and am having a difficult time creating a connection to my database.
The application that I am working with is essentially a bunch of forms created in Access 2003. The forms make connections to a sql server database. They currently use DAO to connect to the database but we need to change the connection to ADO.
I am wondering if anyone wouldn't mind taking a look at my connection and telling me where it is I am going wrong:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

cn.CursorLocation = adUseClient
cn.Open "Provider=SQLOLEDB; Server=myServerName; Database=myDatabase; UID=myUsername; PWD=myPassword"

When I run I get this error:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Does SQL Server contain all of the tables you're going to work with? If yes, then I would suggest using an Access Project instead of a regular Access database.

If that's not the case, then I suggest you look at www.connectionstrings.com and review the connection string for SQL Server- you might be missing a parameter.

for ADO I generally use

cn.Open ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user;Password=password;Initial Catalog=database;Data Source=dbsource"

and I find it more efficient to use a server cursor. .02 Stan