|
|
In the code snippet below I'm attempting to set the .Bookmark property
of a Form to match the bookmark of an ADO recordset:
Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim mctlFieldName As Control
Set rst = New ADODB.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Open Me.RecordSource, CurrentProject.Connection, _
adLockReadOnly, adCmdTable
Set rst = rst.Clone(adLockReadOnly)
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark
When the code is run by opening the Form, I get an error:
Not a valid bookmark.
The error occurs on the line that starts Me.Bookmark. The odd thing
is that when I step through the exact same code in Debug I do not get
the error and the form loads properly.
I'm using Office 2003 SP3 on Vista SP1. This code worked before I
changed all my recordsets from DAO to ADO, so perhaps that is a clue?
Thank you if you can shed some light.
John
|
|
<figaro[ at ]mozart.org> wrote in message
news:k9e1i4d16mj53klkfkv563fqtipd0lp3r7[ at ]4ax.com...
[Quoted Text] > In the code snippet below I'm attempting to set the .Bookmark property
> of a Form to match the bookmark of an ADO recordset:
>
> Private Sub Form_Load()
> Dim rst As ADODB.Recordset
> Dim mctlFieldName As Control
> Set rst = New ADODB.Recordset
> Set mctlFieldName = Forms!frmCDs!cboFiledUnder
> rst.Open Me.RecordSource, CurrentProject.Connection, _
> adLockReadOnly, adCmdTable
> Set rst = rst.Clone(adLockReadOnly)
> rst.Find "ID=" & mctlFieldName.Value
> Me.Bookmark = rst.Bookmark
>
> When the code is run by opening the Form, I get an error:
>
> Not a valid bookmark.
>
> The error occurs on the line that starts Me.Bookmark. The odd thing
> is that when I step through the exact same code in Debug I do not get
> the error and the form loads properly.
>
> I'm using Office 2003 SP3 on Vista SP1. This code worked before I
> changed all my recordsets from DAO to ADO, so perhaps that is a clue?
>
> Thank you if you can shed some light.
>
> John
Your last paragraph says it all. An Access form's recordset is a DAO
recordset. Since DAO and ADO recordsets are not compatible, you get the not
valid error. If you want to persue this technique, you'll have to recode for
DAO. Most likely not the answer you wanted, but...
|
|
Him, my best guess is that you using the conneciton object...
why not try:
[Quoted Text] > Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim mctlFieldName As Control
Set rst = me.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark
Try using the SAME recordset as the forms object. Access will actually
return a dao, or ado reocrdset depending on the type of reocrdset you stuff
it into. And, even better is if you move the recordset to a new record...the
form will follow (you actually don't need the bookmark code at all). If you
use reocrdSetClone (not me.clone, but me.ReocrdSetClone).
Of couse the real question at the end of the day is why convert everything
to ado? Ado is on it's way out and have been replaced by ado.net.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
|
|
On Mon, 17 Nov 2008 00:49:29 -0000, "Stuart McCall"
<smccall[ at ]myunrealbox.com> wrote:
[Quoted Text] ><figaro[ at ]mozart.org> wrote in message
>news:k9e1i4d16mj53klkfkv563fqtipd0lp3r7[ at ]4ax.com...
>> In the code snippet below I'm attempting to set the .Bookmark property
>> of a Form to match the bookmark of an ADO recordset:
>>
>> Private Sub Form_Load()
>> Dim rst As ADODB.Recordset
>> Dim mctlFieldName As Control
>> Set rst = New ADODB.Recordset
>> Set mctlFieldName = Forms!frmCDs!cboFiledUnder
>> rst.Open Me.RecordSource, CurrentProject.Connection, _
>> adLockReadOnly, adCmdTable
>> Set rst = rst.Clone(adLockReadOnly)
>> rst.Find "ID=" & mctlFieldName.Value
>> Me.Bookmark = rst.Bookmark
>>
>> When the code is run by opening the Form, I get an error:
>>
>> Not a valid bookmark.
>>
>> The error occurs on the line that starts Me.Bookmark. The odd thing
>> is that when I step through the exact same code in Debug I do not get
>> the error and the form loads properly.
>>
>> I'm using Office 2003 SP3 on Vista SP1. This code worked before I
>> changed all my recordsets from DAO to ADO, so perhaps that is a clue?
>>
>> Thank you if you can shed some light.
>>
>> John
>
>Your last paragraph says it all. An Access form's recordset is a DAO
>recordset. Since DAO and ADO recordsets are not compatible, you get the not
>valid error. If you want to persue this technique, you'll have to recode for
>DAO. Most likely not the answer you wanted, but...
>
>
Thanks Stuart. I was not aware that forms still had native DAO behind
them.
|
|
On Sun, 16 Nov 2008 23:34:33 -0700, "Albert D. Kallal"
<PleaseNOOOsPAMmkallal[ at ]msn.com> wrote:
[Quoted Text] >Him, my best guess is that you using the conneciton object...
>
>why not try:
>
>> Private Sub Form_Load()
>
> Dim rst As ADODB.Recordset
> Dim mctlFieldName As Control
>
> Set rst = me.Recordset
> Set mctlFieldName = Forms!frmCDs!cboFiledUnder
> rst.Find "ID=" & mctlFieldName.Value
> Me.Bookmark = rst.Bookmark
>
>Try using the SAME recordset as the forms object. Access will actually
>return a dao, or ado reocrdset depending on the type of reocrdset you stuff
>it into. And, even better is if you move the recordset to a new record...the
>form will follow (you actually don't need the bookmark code at all). If you
>use reocrdSetClone (not me.clone, but me.ReocrdSetClone).
>
>Of couse the real question at the end of the day is why convert everything
>to ado? Ado is on it's way out and have been replaced by ado.net.
Thanks for the suggestion. And as to ado.net it seems I'm behind the
times, working as I am from an Access 2002 manual.
ado.net, gee whiz something new to learn, I was afraid I'd run out...
;-)
|
|
"Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
news:u8GpP6HSJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Try using the SAME recordset as the forms object. Access will actually
> return a dao, or ado reocrdset depending on the type of reocrdset you
> stuff it into.
In the hope of learning something new, how do you change a form's
RecordsetClone from DAO to ADO?
(not that I'd ever do that, just curious)
|
|
"Stuart McCall" <smccall[ at ]myunrealbox.com> wrote in message
news:uXeC4sRSJHA.4372[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > "Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
> news:u8GpP6HSJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
>> Try using the SAME recordset as the forms object. Access will actually
>> return a dao, or ado reocrdset depending on the type of reocrdset you
>> stuff it into.
>
> In the hope of learning something new, how do you change a form's
> RecordsetClone from DAO to ADO?
>
> (not that I'd ever do that, just curious)
Actually, I was not 100% clear. If you set the forms reocrdsource to an
adoReocrdset, then
me.ReocrdSet, and me.ReocrdSetClone and me.Recordset.Clone will all return
an ado reocrd set.
So, if your form is bound, or even unbound you can simply create a reocrdset
in code, and stuff it right into the form (by the way, since access 2002,
you can do this for a list/combo box also! - dao or ado works).
Anway, say in forms on load you go:
Dim rstAD As New ADODB.Recordset
rstAD.Open "tblAnswers", CurrentProject.AccessConnection
Set Me.Recordset = rstAD
At this point in time, me.ReocrdSet, me.RecordSetClone, me.recordset.Clone
will all return an ado reocrdset.
So, what I was saying if the form is based on a ado reocrdset, then the
standard properties such as me.ReocrdSet will return ado reocrdsets.
My post hints that you can stuff "me.ReocrdSetClone" into either type of
reocrdset (and access will change the reocrdset type on the fly -- this is
wrong and it does not convert on the "fly". However, the form **does**
return an ado reocrdset if that's what your form is based on. So a form can
return either type of reocrdset.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
|
|
"Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
news:eI7c%231SSJHA.5860[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > "Stuart McCall" <smccall[ at ]myunrealbox.com> wrote in message > news:uXeC4sRSJHA.4372[ at ]TK2MSFTNGP04.phx.gbl... >> "Albert D. Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message >> news:u8GpP6HSJHA.1448[ at ]TK2MSFTNGP04.phx.gbl... >>> Try using the SAME recordset as the forms object. Access will actually >>> return a dao, or ado reocrdset depending on the type of reocrdset you >>> stuff it into. >> >> In the hope of learning something new, how do you change a form's >> RecordsetClone from DAO to ADO? >> >> (not that I'd ever do that, just curious) > > Actually, I was not 100% clear. If you set the forms reocrdsource to an > adoReocrdset, then > > me.ReocrdSet, and me.ReocrdSetClone and me.Recordset.Clone will all return > an ado reocrd set. > > So, if your form is bound, or even unbound you can simply create a > reocrdset in code, and stuff it right into the form (by the way, since > access 2002, you can do this for a list/combo box also! - dao or ado > works). > > Anway, say in forms on load you go: > > Dim rstAD As New ADODB.Recordset > > rstAD.Open "tblAnswers", CurrentProject.AccessConnection > > Set Me.Recordset = rstAD > > At this point in time, me.ReocrdSet, me.RecordSetClone, me.recordset.Clone > will all return an ado reocrdset. > > So, what I was saying if the form is based on a ado reocrdset, then the > standard properties such as me.ReocrdSet will return ado reocrdsets. > > My post hints that you can stuff "me.ReocrdSetClone" into either type of > reocrdset (and access will change the reocrdset type on the fly -- this is > wrong and it does not convert on the "fly". However, the form **does** > return an ado reocrdset if that's what your form is based on. So a form > can return either type of reocrdset. > > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal[ at ]msn.com
Well I did learn something new. I wasn't aware that you could do that (I
knew you could with DAO). As I said it's not something I'd ever do, being a
confirmed DAO user, but its certainly worth knowing. Thanks Albert.
|
|
"figaro[ at ]mozart.org" <figaro[ at ]mozart.org> wrote in
news:klv3i49jenpv1j8c7t61ntrabc8oheqsha[ at ]4ax.com:
[Quoted Text] > And as to ado.net it seems I'm behind the
> times, working as I am from an Access 2002 manual.
ADO.NET is not usable in Access, and probably never will be.
ADO was MS's generic data access interface, introduced into Access
2000 in 1999. MS went on a campaign to try to convince developers to
switch from ADO to DAO, but most developers were smart enough to
realize that for Jet data (and for data accessed via Jet, e.g., all
ODBC data sources), DAO was simply much better than ADO. In the end,
MS realized its mistake and reversed course. ADO is no longer the
preferred data interface, except for certain kinds of operations
with databases via OLEDB interfaces (ADO is a wrapper around OLEDB),
which mostly means SQL Server, and in Access Data Projects (which
work only with SQL Server).
In short, in 2000, there was no real justification for converting
ADO code to DAO, though MS was putting out a lot of propaganda to
make developers think there was some magical benefit to doing so.
In 2008, there is still no justification whatsoever, and it's really
not very wise, given that classic ADO exists only in Access and
classic ASP.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
On 18 Nov 2008 21:56:43 GMT, "David W. Fenton"
<XXXusenet[ at ]dfenton.com.invalid> wrote:
[Quoted Text] >"figaro[ at ]mozart.org" <figaro[ at ]mozart.org> wrote in
>news:klv3i49jenpv1j8c7t61ntrabc8oheqsha[ at ]4ax.com:
>
>> And as to ado.net it seems I'm behind the
>> times, working as I am from an Access 2002 manual.
>
>ADO.NET is not usable in Access, and probably never will be.
>
>ADO was MS's generic data access interface, introduced into Access
>2000 in 1999. MS went on a campaign to try to convince developers to
>switch from ADO to DAO, but most developers were smart enough to
>realize that for Jet data (and for data accessed via Jet, e.g., all
>ODBC data sources), DAO was simply much better than ADO. In the end,
>MS realized its mistake and reversed course. ADO is no longer the
>preferred data interface, except for certain kinds of operations
>with databases via OLEDB interfaces (ADO is a wrapper around OLEDB),
>which mostly means SQL Server, and in Access Data Projects (which
>work only with SQL Server).
>
>In short, in 2000, there was no real justification for converting
>ADO code to DAO, though MS was putting out a lot of propaganda to
>make developers think there was some magical benefit to doing so.
>
>In 2008, there is still no justification whatsoever, and it's really
>not very wise, given that classic ADO exists only in Access and
>classic ASP.
Thanks David for your clear and concise statement of some relevant and
interesting facts!
John
|
|
|