Cannot execute a DAO Code accessing an SQL 2000 Server

[maupiti]

Run-Time Error '3146'
ODBC - Call Failed
Error at statement cnn.Execute strSQL

//////////////////////////////////

Private Sub DAO_SP()
Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim qdf As DAO.QueryDef
Dim strConnect As String, strSQL As String

Set wrk = DBEngine.CreateWorkspace("NewODBCWrk", "admin", "", dbUseODBC)
strConnect = "ODBC;DSN=WIN-2000-Server;DATABASE=Pubs;UID=linh;PWD=password;"
Set cnn = wrk.OpenConnection("", dvDrivernoprompt, False, strConnect)
strSQL = "CREATE PROCEDURE UpdateAuthors @state Char(2) AS " _
& "UPDATE Authors " _
& "SET state = 'FL'" _
& "WHERE state = @state"
cnn.Execute strSQL
Set qdf = cnn.CreateQueryDef("qry", "{ call UpdateAuthors(?) }")
qdf.Parameters(0).Value = "NC"
qdf.Execute
cnn.Close
wrk.Close
End Sub

 

[cmmrfrds]

Why do you want to use DAO to go against sql server?

 

[maupiti]

I want to do this for learning purposes. When I see that it work, I move on to ADO and other method. Also, I have databases that all have DAO code. Running existing DAO code on SQL server saves time, though of cource it is not as effecient as ADO.

 

[cmmrfrds]

Sorry, can't help with the DAO.