RE: CursorType issue with .mdb and Excel VBA, ADO

Okay, I figured it out. Nowhere in 5 books, have I been told that setting
the cursor type of the recordset would be useless when populating the same
from a command object.

So, as a solution to my problem, I must set the cursor type at the objConn
level first.

Finally.



"TempestFyre" wrote:

> For the sake of Brevity, here is a modified, limited version of a datapull
> I'm using.
>
> Option Explicit
> Sub Get_Rate_Defs ()
> ' \\Turn on error handling
> On Error GoTo RetrieveError
>
> '\\Establish connection.
> Dim strCN As String
> Dim cnUnit7_Log_Data As New Adodb.Connection
> strCN = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
> "Data Source = V:\Unit7\Data\Electronic_Logsheets_Data.mdb"
> cnUnit7_Log_Data.ConnectionString = strCN
> cnUnit7_Log_Data.Open
>
> '\\Establish recordset.
> Dim rstRate_Defs As New Adodb.Recordset
> rstRate_Defs.CursorType = Adodb.adOpenStatic
> rstRate_Defs.CursorLocation = Adodb.adUseClient
> rstRate_Defs.LockType = Adodb.adLockOptimistic
> '\\ *** If I write the three preceding property values out at this point,
> they do show up as 3,3,3, respectively, which is what they were just set to.
> ***
>
> '//Establish command object.
> Dim cmdGet_Rate_Defs As New Adodb.Command
> Dim strCMD_Text As String
>
> strCMD_Text = "SELECT [Rate_Restrict] FROM Restrictions "
> strCMD_Text = strCMD_Text & "Order by [Rate_Restrict]"
> cmdGet_Rate_Defs .ActiveConnection = cnUnit7_Log_Data
> cmdGet_Rate_Defs .CommandText = strCMD_Text
> cmdGet_Rate_Defs .CommandType = adCmdText
>
> '//Populate Recordset via execution of ADO Command/SQL Statement -->
> Set rstRate_Defs = cmdGet_Rate_Defs .Execute
> Exit sub
> RetrieveError:
> '\\*** Various error handling routines.
> End SubGet_Rate_Defs
>
> After the pull, the recordset only [supports] "Find" and "Notify".
> Record count is -1, as though this is a forward only or dynamic cursor.
>
> cursor type changes from 3 to 0
> Cursor location changes from 3 two 2
> Lock type changes from 3 to 1
>
> References are set to:
> VBA
> Excel 10 Object Library
> VBA Extensibility Package
> OLE Automation
> ADO 2.8
> ADOX 2.8
>
> I hope someone has a clue as to what is going on with this.
> And, the V:\ drive is a mapped drive, which is constant throughout my
> client's campus.
>
> :)
>
>
>
>
>
>
> "TempestFyre" wrote:
>
> > I'm in the process of trying to implement ADO in some new Excel apps, and
> > lead others here away from DAO, so that my future scaling to a SQL server may
> > be less frustrating. However, I've been unable to retrieve anything other
> > than a forward-only cursor from ANY .mdb we use. I need a static cursor.
> > Have set the props for the recordset, I can then write the cursor/lock
> > properties to a work*** before the recordset is opened, and then write them
> > again, following the recordset's retrieval. They always revert back to
> > forward-only!
> > I thought it is possible to use static cursors with Jet 4.0. Is this not
> > true?
> >
> > Any ideas would be nice.
> >
> > .mdb created using Access 2002 (or 2003, for some), Excel 2003, ADO 2.8
> > Jet 4.0 SP8
> >
> > Thank you in advance.
.