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
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