Database Connection to SQL from Access 2003

[Barry]

I have a SQL 2000 database with many tables and views. I am writing a front
end in Access 2003 for an adp project. In Access, I want to create code
modules for functions that query and update SQL tables. Following the help
files, I have created (for example) the following code:

Sub Find_Prod_Order()
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQLStmt As String
SQLStmt = "SELECT COUNT(DISTINCT prod_order_number) FROM prod_order_hdr"

CurConn.Open "DSN=SQL_Server;uid=dbuser;pwd=123456;database=production"
Set rst = CurrCon.OpenRecordset(SQLStmt)
End Sub

This code fails on the Set rst = ... line with "Run-time error: 424 Object
required" message. I've tested the sql code in query analyzer and it works
fine. I'd appreciate it id someone could help me correct my code. TIA,
Barry

 

[Ralph]

Aren't you missing an n in CurrConn?
Set rst = CurrCon.OpenRecordset(SQLStmt)

 

[Douglas J. Steele]

And the error message implies that VBA hasn't been told to require
declaration of all variables, or else you would have got a "Variable not
defined" error.

While in the VB Editor, select Tools | Options from the menu. Make sure that
the Require Variable Declaration check box is selected on the Editor tab.
This will cause the line "Option Explicit" to be placed at the top of all
future modules. You'll need to go back through your existing ones and add
that line.

 

[Ralph]

You also do not want to use openrecordset instead use Open
Set rst = CurrCon.Open SQLStmt

 

[Barry]

Ralph, I added the missing 'n' in CurConn and enclosed the SQLStmt in
parentheses, but still get the exact same error. Have I missed something
else?

 

[Barry]

Ralph, I also corrected "Currconn" to CurConn to match the declaration. The
statement is now:
Set rst = CurConn.Open(SQLStmt)
Now I get the error "Compile error. Expected function or variable" with the
".open" highlighted. ???