Access Access 2003 VBA Open Recordset

[atran80]

Hi everyone,

I'm relatively new to VBA and definitely new to Access (2 weeks). I need some help with the following code:

Sub Repeat_ED_Visits()
Dim q1New As Object
Dim sql As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim EDRepeatTbl As TableDef
With CurrentDb
sql = ("SELECT DISTINCT ...") 'my SQL query
Set q1New = .CreateQueryDef("EDRepeatQuery", sql)
End With
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = Application.CurrentProject.Connection
rst.Open sql, CurrentProject.Connection
With rst
<<code>>
End With
rst.Close
Set rst = Nothing
End Sub

When I run this, I'm getting error msg 3704 ("Operation is not allowed when the object is closed"), but my rst.Open line is there. Any ideas?

Thanks in advance,
Anh

 

[apr pillai]

Check whether the ADODB (Microsoft ActiveX Data Object n.n Library) Library File is attached to your Project or not.

1. Open VBA Window (Alt+F11)
2. Select References from Tools Menu.
3. Browse for the above Library File (if it is not already selected) and put a check mark in the Dialog Control.