Cursertype and Lock Type with Recordsets opened from command (1 Viewer)

[jon_sg]

I want to get an ADO recordset opened with a cmd.execute statement to have the none default adOpenStatic and AdLockOptimistic. even when specified prior to the execute cmd it opens with the defaults which cause the RecordCount property to always read -1 and worse the recordset is created as read only which prevents updates. It dos not seem to be possible to specify this as options with the command

here is the code.

Private Function GetTimeSheetData() As Boolean
'checks as to existence of Data
Dim rstGTSD As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim intI As Integer

Dim dteWkSt As Date
Dim strEmpId As String
'Set up cmd object to run query to test for records
Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
cmd.CommandText = "qryTimeCardSub"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh
Debug.Print cmd.Parameters.Count
'Eval Params
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
'Next two lines the problem
rstGTSD.CursorType = adOpenStatic
rstGTSD.LockType = adLockOptimistic
Set rstGTSD = cmd.Execute 'Always opens as forward - read only
Debug.Print rstGTSD.CursorType 'Shows 0
Debug.Print rstGTSD.LockType 'Shows -1
' Test for records
Select Case rstGTSD.RecordCount
Case 7
GetTimeSheetData = True
GoTo Clean_up
Case 0
'Adds next 7 days to the table
dteWkSt = Me.txtSetDte
strEmpId = Me.txtEmpId
For intI = 1 To 7
With rstGTSD
.AddNew
!fldEmpId = strEmpId
!fldDteWrk = dteWkSt
.Update
End With
dteWkSt = dteWkSt + 1
Next intI
GoTo Clean_up
Case Else
MsgBox "Please Add or Delete Records Manually", vbOKOnly, "Data Error"
GetTimeSheetData = False
GoTo Clean_up
End Select

Clean_up:
rstGTSD.Close
Set rstGTSD = Nothing
Set cmd = Nothing
If GetTimeSheetData = True Then
Call LoadRecords
Else
MsgBox "You blew it! redo it like you were shown", vbOKOnly + vbInformation, "LOSER"
End If
End Function

Any help would be appreciated

Jon