Creating an ADO Recordset 2

[Blondie96]

I am trying to create an ADO recordset from a table that exists within the current database, when I execute the code I get:

Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

the code is:

Dim rstData As ADODB.Recordset
Dim strSQL As String

Set rstData = New ADODB.Recordset

strSQL = "Select * From tblClassroomReservations"
rstData.Open strSQL, , adOpenDynamic, adLockOptimistic, adCmdText

any help is appreciated.

Thanks,
Tamra

 

[Crowley16]

you need to define the connection...

dim cn as adodb.connection

set cn = currentproject.activeconnection

set rsdata.connection = cn

not sure the syntax is 100% correct though, use F1 to check...

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

 

[Blondie96]

Im confused, the connection type is "supposed" to be optional..
Even so,

I tried adding the code w/the various changes:
set cn = ... or cn = ...
and
rstData.Open strSQL, rsdata.Connection, adOpenDynamic, adLockOptimistic, adCmdText
or
rstData.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

Both ways I get:
Run-time error '438':
Object doesn't support this property or method

 

[Blondie96]

additional:
that error (on debug) occurs on the
set cn = or cn = line

 

[cmmrfrds]

The dim just declares the ADO object, it does not create an instance of the object. The NEW keyword creates the instance. Notice how you used the New keyword on the recordset object.

Dim cn as New ADODB.Connection
set cn = currentproject.connection

OR just plug in the current project connection.

rstData.Open strSQL, currentproject.connection, adOpenDynamic, adLockOptimistic, adCmdText

 

[Blondie96]

Great, adding currentproject inline with the open solved that error.

Now, I'm trying to sort the recordset, but got this error

Run-time error '3251'
Current provider does not support the necessary interfaces for sorting or filtering.

How do I need to change the open to allow me to sort the recordset?

Thanks again,
Tamra

 

[PHV]

You may try to replace adCmdText by adCmdTable

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

 

[Blondie96]

Thanks PHV, that worked however now it doesn't like the select:

strSQL = "Select * From tblClassroomReservations"

it gives
Run-time error
'-2147217900(800r0e14)':
Syntax error in From Clause.

 

[cmmrfrds]

Can you show your code for sorting the recordset.

 

[Blondie96]

My code is:

Dim rstData As ADODB.Recordset
Dim strSQL As String

Set rstData = New ADODB.Recordset

strSQL = "Select * From tblClassroomReservations"
rstData.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable

SortXprint "Initial Order", rstData
rstData.Sort = "roomID ASC, StartDay ASC"
SortXprint "roomID StartDay ", rstData
End Sub

Sub SortXprint(title As String, rstp As ADODB.Recordset)
Debug.Print "-----------" & title & "------------"
Debug.Print "roomID StartDay & StartHour " & vbCr & _
"-------------------------"
rstp.MoveFirst
While Not rstp.EOF
Debug.Print rstp!roomID & " " & rstp!StartDay
rstp.MoveNext
Wend
End Sub

tblclassroomReservations looks like:
schedID
TrackingNumber
RoomID
PriorityID
UserName
StartDay
EndDay
EndHour

 

[cmmrfrds]

adOpenDynamic
This needs to be static also the cursor location needs to be client side.

 

[Blondie96]

this is a standalone DB (no client/server) does cursor location matter? & if so, how do I set it?

 

[Blondie96]

it is already opened as adOpenDynamic, or should this be added somewhere else that I missed?

 

[cmmrfrds]

Default location is server, which in this case are the same thing, but ADO may not coerce the adOpenDynamic to Static which is necessary.

rstData.CursorLocation = adUseClient

This will force static regardless of what you put.

 

[cmmrfrds]

rstData.Open strSQL, currentproject.connection, adOpenStatic, adLockOptimistic

adCmdText is the default, so not needed in this case.

 

[Blondie96]

I have:

strSQL = "Select * From tblClassroomReservations"
rstData.CursorLocation = adUseClient
rstData.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdTable

but I still get an error on the sql from clause

 

[Blondie96]

now, I have a really dumb question...
i got rid of the adcmdtable & apparently the code executed without error.

i expected to see the print results of the pre-sort & post sort recordset thru this code:

SortXprint "Initial Order", rstData
rstData.Sort = "ChannelID ASC, StartDay ASC"
SortXprint "ChannelID StartDay ", rstData

End Sub
Sub SortXprint(title As String, rstp As ADODB.Recordset)
Debug.Print "-----------" & title & "------------"
Debug.Print "ChannelID StartDay & StartHour " & vbCr & _
"-------------------------"
rstp.MoveFirst
While Not rstp.EOF
Debug.Print rstp!ChannelID & " " & rstp!StartDay
rstp.MoveNext
Wend
End Sub

where do I need to look to see these results?

 

[PHV]

In the Immediate window.
Go to VBE (Alt+F11) and then press Ctrl+G

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

 

[cmmrfrds]

Control G
brings up the immediate window.

 

[Blondie96]

Thank you PHV & cmmrfrds