ADO Access 2003 -> SQL Server

[SCS]

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.

[CreganTur]

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.

[stanl]

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