Cannot execute a query to an SQL 2000 Server

[maupiti]

Run-Time error '13'. Type mismatch
Yellow Highlight At: For Each err In DBEngine.Errors

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

Private Sub DAOAsyncQuery_1()
Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim strConnect As String
Dim err As Error

On Error GoTo Err_DAOAsyncQuery
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)
cnn.Execute "select * from Employees", dbRunAsync

'Perform other tasks

If cnn.StillExecuting Then
cnn.Cancel
End If

Exit_DAOAsyncQuery:
On Error Resume Next
cnn.Close
wrk.Close
Exit Sub

Err_DAOAsyncQuery:
For Each err In DBEngine.Errors
Debug.Print err.Number, err.Description
Next err
Resume Exit_DAOAsyncQuery
End Sub

 

[Crowley16]

erm...

you can't use dao to query external mssql database or any non-access database.

you need to declare a recordset, and open it with the connection string and sql string to return records

your connection string is wrong

Employees is not a standard table in pubs, unless you've created that table...

--------------------
Procrastinate Now!

 

[PHV]

Err is a standard VBA object, so avoid to use it as a variable name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886