ado issue

J

JohnE

I am trying (hopefully) to speed up the filtering of a form by using the ado
code below. Unfortunately it continues to fail at the Set .ActiveConnection
= conn line. The sql is declared but not showing within this post. The
error I get is;

"The connecton cannot be used to perform this operation. It is either
closed or invalid in this context."

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn = "Data Provider=Microsoft.Access.OLEDB.10.0;Data
Source=MXSQL;Initial Catalog=MTXMain;User Id=xxxxxxx;Password=xxxxxxxxxx;"

Dim rst As ADODB.Recordset
sql = "SELECT * FROM
InventoryByLocationProjectPlusValuationAndTotalValueQ"
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = conn
.Source = sql
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open sql, conn

End With

Set Me.Recordset = rst

I have tried using the CurrentProject.Connection but that is not successful.
Does anyone have any thoughts or see what is causing the error (cuz I
don't)? This is a mdb file connected to sql2005.

Thanks.

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top