[AccessD] Cannot open second Recordset?!

Hi group,
 
when I change the SQL:
strSQL = "SELECT InterfaceID, FieldID, FieldName, Start, End, 
Mandatory " & _
            "FROM tblInterfaceLayout " & _
            "WHERE InterfaceID = " & InterFaceID

strSQL = "SELECT * FROM tblInterfaceLayout WHERE InterfaceID = " & InterFaceID

it does work?! How is this possible? BTW i did NOT misspel the fieldnames because when I use the debug window to display strSQL I can run the query!!
 
Sander


S D <accessd667 at yahoo.com> wrote:
Hi group,

I keep getting the error:
Run-time error '-2147467259(80004005)
Method 'Open' of object '_Recordset' failed

*************** FORM / Button **********************
Dim arrFileLayout() As Variant
Set cInterface = New cInterface
With cInterface
Call .Init(cnnConnection)
Call .GetOpenLines(strSQL)
Call .GetFileLayout(arrFileLayout(), 44)
...
****************************************************
I get the error when calling the GetFileLayout (GFL) Method..
However the query in GFL is correct. When dropped in a query window it returns the expected records.
As you can see I call the method GetOpenLines (GOL) This method works exactly the same but does work?!
Skipping GOL also returns the same error?!
What am I missing?

TIA
Sander

**** Class cInterface *****
Dim m_oCnn As ADODB.Connection

Public Sub Init(Connection As ADODB.Connection)
Set m_oCnn = Connection
'm_intInterfaceID = InterfaceID
End Sub

Function GetFileLayout(ByRef arrFileLayout() As Variant, InterFaceID As Integer) As String
'Create a rst based on table tblInterfaceLayout
Dim rstInterfaceLayout As ADODB.Recordset
Dim strSQL As String
Dim intCounter As Integer
Dim cnnConnectIOn2 As ADODB.Connection

Set rstInterfaceLayout = New ADODB.Recordset
'Set cnnConnectIOn2 = CurrentProject.Connection()

strSQL = "SELECT InterfaceID, FieldID, FieldName, Start, End, Mandatory " & _
"FROM tblInterfaceLayout " & _
"WHERE InterfaceID = " & InterFaceID
'"WHERE InterfaceID = " & intInterfaceID
ReDim arrFileLayout(1, 3)

intCounter = 0

With rstInterfaceLayout
.ActiveConnection = m_oCnn 'cnnConnectIOn2
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Source = strSQL
.Open <= ERROR OCCURS HERE?!
If Not (.BOF And .EOF) Then
...
...
Function GetOpenLines(strSQL As String) As String
Dim rstOpenLines As ADODB.Recordset
Dim varLine As Variant

Set rstOpenLines = New ADODB.Recordset

With rstOpenLines
.ActiveConnection = m_oCnn 'cnnConnection
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Source = strSQL
.Open
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
Do While Not .EOF
varLine = !InterfaceData
'Debug.Print varLine
'TODO: Add the data to the specified table
.MoveNext
Loop
Else
'no records to proces
End If
End With
End Function



---------------------------------
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---------------------------------
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.