Is this script in this page for real?

[George Hester]

http://support.microsoft.com/kb/304724

I just cannot seem to verify the argument of the MSODSC.Execute command nor
can I determine what is happening with LoopCnt. Can anyone shed some light
on this script besides the fact that Microsoft wishes DAP would die
gracefully? Thanks.

What I am doing is trying to make a "dynamic" query in a DAP page. And
something tells me I do this with a Server filter and RecordsetDef.

 

[Ken Snell \(MVP\)]

Looks like a typo in the script to me.

Try this script instead (I have replaced strRecordsetDef with rsDef):

Dim rs
Dim rsDef
Dim LoopCnt

LoopCnt = 0
rsDef = MSODSC.RecordsetDefs.Item(0).Name
Set Rs = MSODSC.Execute(rsDef)

Rs.MoveFirst
MsgBox "Company: " & Rs("CompanyName") & vbNewLine & "Contact: " &
Rs("ContactName") & _
vbNewLine & "Phone: " & Rs("Phone")

Set Rs = Nothing

 

[George Hester]

Ah that makes a little more sense thanks Ken. Now for the counter LoopCnt.
Doesn't that need to be incremented somewhere or is that native to
customers.htm somewhere? I don't have that html. Do you think you could send
that to me? I don't need the accompanying data. I just need the source code
to see how they are using it. Thanks.

 

[Ken Snell \(MVP\)]

Quite honestly, I'm not sure that LoopCnt plays a meaningful role in this
script. The example seems to be an "old" one that has not been
updated/reviewed by MS in quite a while, and likely it was originally
written for a different purpose.

How are you wanting to use this script as a dynamic query? Where are you
putting the results from the opened recordset (I assume that you're not
wanting to just display them in a MsgBox popup?)?

 

[George Hester]

No nothing in a message box. I think I found a better example of what I want
to do. I could just make a query and write the return to a web page and that
is OK I may have to do that. But I found this:

http://support.microsoft.com/kb/266384/en-us

Near the bottom of that page you will see how to use SQL in a DAP.

Mine was this:

'sSQL = "SELECT [tblTest4].[Caption_Name] " & _
"FROM tblTest4 " & _
"WHERE ([tblTest4].[Caption_Name]) LIKE '%Rice%';"

This just hopes to find Anne Rice and other Rice's in the table tblTest4 and
the field Caption_Name; it will also find Rice Krispies but that's fine. I
wittled it down to just what you showed me before. The one from Microsoft
only works in Query in Access. Anyway my two list boxes one bournded to
Caption_Name and the other bounded to Caption_Title flip through each entry
when I activate this SQL. Trouble is I do not get a new recordset. The one
the page opens with is all that results. The rest of the code for this is:

'MSODSC.Connection.Execute sSQL
'Set rs = MSODSC.DefaultRecordset
'MSODSC.CurrentSection.DataPage.Requery
'MSODSC.CurrentSection.DataPage.MoveFirst
'rs.MoveFirst

The example from the KB article just resulted in the same recordset as the
page has when it opens.

As you can see it doesn't work for I have commented it out for another day.
I thought MSODSC.Connection.Execute sSQL would give a new recordset but Set
rs = MSODSC.Connection.Execite sSQL didn't do it. So I just putzed around
trying to see if I could get a new recordset from the SQL and well nada.

That's what I am trying to do. I set up a filter that would have given me
what I want but it took too long for a 1000 recordset database. So it needs
to work and it can't take all day.

 

[Ken Snell \(MVP\)]

I have found DAPs to be puzzling, exasperating, and downright fickle at
times. You end up spending so much time trying to figure out how to do
something that can be coded/designed in a few minutes in a form.

What I've done, in the few DAPs that I've programmed, for filtering a query
is to use a ServerFilter property in script when the DAP is being bound to a
recordset. I'm sure there are other ways to do what is desired, but this is
what I stumbled across and have used it. I have an example DAP database here
that might be helpful to you:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterDAP

I wish you success and good luck!
--

Ken Snell
<MS ACCESS MVP>

No nothing in a message box. I think I found a better example of what I
want
to do. I could just make a query and write the return to a web page and
that
is OK I may have to do that. But I found this:

http://support.microsoft.com/kb/266384/en-us

Near the bottom of that page you will see how to use SQL in a DAP.

Mine was this:

'sSQL = "SELECT [tblTest4].[Caption_Name] " & _
"FROM tblTest4 " & _
"WHERE ([tblTest4].[Caption_Name]) LIKE '%Rice%';"

This just hopes to find Anne Rice and other Rice's in the table tblTest4
and
the field Caption_Name; it will also find Rice Krispies but that's fine. I
wittled it down to just what you showed me before. The one from Microsoft
only works in Query in Access. Anyway my two list boxes one bournded to
Caption_Name and the other bounded to Caption_Title flip through each
entry
when I activate this SQL. Trouble is I do not get a new recordset. The one
the page opens with is all that results. The rest of the code for this is:

'MSODSC.Connection.Execute sSQL
'Set rs = MSODSC.DefaultRecordset
'MSODSC.CurrentSection.DataPage.Requery
'MSODSC.CurrentSection.DataPage.MoveFirst
'rs.MoveFirst

The example from the KB article just resulted in the same recordset as the
page has when it opens.

As you can see it doesn't work for I have commented it out for another
day.
I thought MSODSC.Connection.Execute sSQL would give a new recordset but
Set
rs = MSODSC.Connection.Execite sSQL didn't do it. So I just putzed around
trying to see if I could get a new recordset from the SQL and well nada.

That's what I am trying to do. I set up a filter that would have given me
what I want but it took too long for a 1000 recordset database. So it
needs
to work and it can't take all day.

--

George Hester
_________________________________

Quite honestly, I'm not sure that LoopCnt plays a meaningful role in this
script. The example seems to be an "old" one that has not been
updated/reviewed by MS in quite a while, and likely it was originally
written for a different purpose.

How are you wanting to use this script as a dynamic query? Where are you
putting the results from the opened recordset (I assume that you're not
wanting to just display them in a MsgBox popup?)?

 

[George Hester]

Hi Ken. I finally got it. Now I have a new recordset that is just the
records I want. How do I get the page to show the new recordset I made? I
can probably find some way to do it best but could use your help on that.
Thanks. What you have done for me has been a major help. Couldn't have got
this far without you. I had to use your sql just as you wrote it.

--

George Hester
_________________________________

I have found DAPs to be puzzling, exasperating, and downright fickle at
times. You end up spending so much time trying to figure out how to do
something that can be coded/designed in a few minutes in a form.

What I've done, in the few DAPs that I've programmed, for filtering a query
is to use a ServerFilter property in script when the DAP is being bound to a
recordset. I'm sure there are other ways to do what is desired, but this is
what I stumbled across and have used it. I have an example DAP database here
that might be helpful to you:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterDAP

I wish you success and good luck!
--

Ken Snell
<MS ACCESS MVP>

No nothing in a message box. I think I found a better example of what I
want
to do. I could just make a query and write the return to a web page and
that
is OK I may have to do that. But I found this:

http://support.microsoft.com/kb/266384/en-us

Near the bottom of that page you will see how to use SQL in a DAP.

Mine was this:

'sSQL = "SELECT [tblTest4].[Caption_Name] " & _
"FROM tblTest4 " & _
"WHERE ([tblTest4].[Caption_Name]) LIKE '%Rice%';"

This just hopes to find Anne Rice and other Rice's in the table tblTest4
and
the field Caption_Name; it will also find Rice Krispies but that's fine. I
wittled it down to just what you showed me before. The one from Microsoft
only works in Query in Access. Anyway my two list boxes one bournded to
Caption_Name and the other bounded to Caption_Title flip through each
entry
when I activate this SQL. Trouble is I do not get a new recordset. The one
the page opens with is all that results. The rest of the code for this is:

'MSODSC.Connection.Execute sSQL
'Set rs = MSODSC.DefaultRecordset
'MSODSC.CurrentSection.DataPage.Requery
'MSODSC.CurrentSection.DataPage.MoveFirst
'rs.MoveFirst

The example from the KB article just resulted in the same recordset as the
page has when it opens.

As you can see it doesn't work for I have commented it out for another
day.
I thought MSODSC.Connection.Execute sSQL would give a new recordset but
Set
rs = MSODSC.Connection.Execite sSQL didn't do it. So I just putzed around
trying to see if I could get a new recordset from the SQL and well nada.

That's what I am trying to do. I set up a filter that would have given me
what I want but it took too long for a 1000 recordset database. So it
needs
to work and it can't take all day.

--

George Hester
_________________________________

Quite honestly, I'm not sure that LoopCnt plays a meaningful role in this
script. The example seems to be an "old" one that has not been
updated/reviewed by MS in quite a while, and likely it was originally
written for a different purpose.

How are you wanting to use this script as a dynamic query? Where are you
putting the results from the opened recordset (I assume that you're not
wanting to just display them in a MsgBox popup?)?

--

Ken Snell
<MS ACCESS MVP>



Ah that makes a little more sense thanks Ken. Now for the counter LoopCnt.
Doesn't that need to be incremented somewhere or is that native to
customers.htm somewhere? I don't have that html. Do you think you could
send
that to me? I don't need the accompanying data. I just need the source
code
to see how they are using it. Thanks.

--

George Hester
_________________________________
Looks like a typo in the script to me.

Try this script instead (I have replaced strRecordsetDef with rsDef):

Dim rs
Dim rsDef
Dim LoopCnt

LoopCnt = 0
rsDef = MSODSC.RecordsetDefs.Item(0).Name
Set Rs = MSODSC.Execute(rsDef)

Rs.MoveFirst
MsgBox "Company: " & Rs("CompanyName") & vbNewLine & "Contact: " &
Rs("ContactName") & _
vbNewLine & "Phone: " & Rs("Phone")

Set Rs = Nothing


--

Ken Snell
<MS ACCESS MVP>


http://support.microsoft.com/kb/304724

I just cannot seem to verify the argument of the MSODSC.Execute command
nor
can I determine what is happening with LoopCnt. Can anyone shed some
light
on this script besides the fact that Microsoft wishes DAP would die
gracefully? Thanks.

What I am doing is trying to make a "dynamic" query in a DAP page. And
something tells me I do this with a Server filter and RecordsetDef.

 

[Ken Snell \(MVP\)]

Do you want to show the "new" recordset on the same page that runs the
script to create the recordset? I was not successful, in my limited
attempts, to do that -- which is why, in my sample database, I navigate to a
new DAP and let its recordset be the one I want. Can you make this work for
your setup?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken. I finally got it. Now I have a new recordset that is just the
records I want. How do I get the page to show the new recordset I made? I
can probably find some way to do it best but could use your help on that.
Thanks. What you have done for me has been a major help. Couldn't have got
this far without you. I had to use your sql just as you wrote it.

--

George Hester
_________________________________

I have found DAPs to be puzzling, exasperating, and downright fickle at
times. You end up spending so much time trying to figure out how to do
something that can be coded/designed in a few minutes in a form.

What I've done, in the few DAPs that I've programmed, for filtering a query
is to use a ServerFilter property in script when the DAP is being bound
to a
recordset. I'm sure there are other ways to do what is desired, but this is
what I stumbled across and have used it. I have an example DAP database here
that might be helpful to you:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterDAP

I wish you success and good luck!
--

Ken Snell
<MS ACCESS MVP>

No nothing in a message box. I think I found a better example of what I
want
to do. I could just make a query and write the return to a web page and
that
is OK I may have to do that. But I found this:

http://support.microsoft.com/kb/266384/en-us

Near the bottom of that page you will see how to use SQL in a DAP.

Mine was this:

'sSQL = "SELECT [tblTest4].[Caption_Name] " & _
"FROM tblTest4 " & _
"WHERE ([tblTest4].[Caption_Name]) LIKE '%Rice%';"

This just hopes to find Anne Rice and other Rice's in the table
tblTest4
and
the field Caption_Name; it will also find Rice Krispies but that's
fine. I
wittled it down to just what you showed me before. The one from Microsoft
only works in Query in Access. Anyway my two list boxes one bournded
to
Caption_Name and the other bounded to Caption_Title flip through each
entry
when I activate this SQL. Trouble is I do not get a new recordset. The one
the page opens with is all that results. The rest of the code for this is:

'MSODSC.Connection.Execute sSQL
'Set rs = MSODSC.DefaultRecordset
'MSODSC.CurrentSection.DataPage.Requery
'MSODSC.CurrentSection.DataPage.MoveFirst
'rs.MoveFirst

The example from the KB article just resulted in the same recordset as the
page has when it opens.

As you can see it doesn't work for I have commented it out for another
day.
I thought MSODSC.Connection.Execute sSQL would give a new recordset but
Set
rs = MSODSC.Connection.Execite sSQL didn't do it. So I just putzed around
trying to see if I could get a new recordset from the SQL and well
nada.

That's what I am trying to do. I set up a filter that would have given me
what I want but it took too long for a 1000 recordset database. So it
needs
to work and it can't take all day.

--

George Hester
_________________________________
Quite honestly, I'm not sure that LoopCnt plays a meaningful role in this
script. The example seems to be an "old" one that has not been
updated/reviewed by MS in quite a while, and likely it was originally
written for a different purpose.

How are you wanting to use this script as a dynamic query? Where are you
putting the results from the opened recordset (I assume that you're
not
wanting to just display them in a MsgBox popup?)?

--

Ken Snell
<MS ACCESS MVP>



Ah that makes a little more sense thanks Ken. Now for the counter
LoopCnt.
Doesn't that need to be incremented somewhere or is that native to
customers.htm somewhere? I don't have that html. Do you think you could
send
that to me? I don't need the accompanying data. I just need the source
code
to see how they are using it. Thanks.

--

George Hester
_________________________________
message
Looks like a typo in the script to me.

Try this script instead (I have replaced strRecordsetDef with rsDef):

Dim rs
Dim rsDef
Dim LoopCnt

LoopCnt = 0
rsDef = MSODSC.RecordsetDefs.Item(0).Name
Set Rs = MSODSC.Execute(rsDef)

Rs.MoveFirst
MsgBox "Company: " & Rs("CompanyName") & vbNewLine & "Contact: "
&
Rs("ContactName") & _
vbNewLine & "Phone: " & Rs("Phone")

Set Rs = Nothing


--

Ken Snell
<MS ACCESS MVP>


http://support.microsoft.com/kb/304724

I just cannot seem to verify the argument of the MSODSC.Execute
command
nor
can I determine what is happening with LoopCnt. Can anyone shed some
light
on this script besides the fact that Microsoft wishes DAP would die
gracefully? Thanks.

What I am doing is trying to make a "dynamic" query in a DAP
page.
And
something tells me I do this with a Server filter and RecordsetDef.

 

[George Hester]

Yes man all done. Trick is to use the ServerFilter. That refreshes the page
by default. The way it works is your SQL finds stuff that is LIKE. Then find
out the complete field values that gave the return from the SQL. Then use
the ServerFilter object with the complete field values obtained with the SQL
and ouila. Refreshed and a bona fide LIKE query. And fast too. I posted what
it looks like below. I called it a Query without the popup.

--

George Hester
_________________________________

Do you want to show the "new" recordset on the same page that runs the
script to create the recordset? I was not successful, in my limited
attempts, to do that -- which is why, in my sample database, I navigate to a
new DAP and let its recordset be the one I want. Can you make this work for
your setup?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken. I finally got it. Now I have a new recordset that is just the
records I want. How do I get the page to show the new recordset I made? I
can probably find some way to do it best but could use your help on that.
Thanks. What you have done for me has been a major help. Couldn't have got
this far without you. I had to use your sql just as you wrote it.

--

George Hester
_________________________________

I have found DAPs to be puzzling, exasperating, and downright fickle at
times. You end up spending so much time trying to figure out how to do
something that can be coded/designed in a few minutes in a form.

What I've done, in the few DAPs that I've programmed, for filtering a query
is to use a ServerFilter property in script when the DAP is being bound
to a
recordset. I'm sure there are other ways to do what is desired, but

this
is

what I stumbled across and have used it. I have an example DAP database here
that might be helpful to you:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterDAP

I wish you success and good luck!
--

Ken Snell
<MS ACCESS MVP>



No nothing in a message box. I think I found a better example of what I
want
to do. I could just make a query and write the return to a web page and
that
is OK I may have to do that. But I found this:

http://support.microsoft.com/kb/266384/en-us

Near the bottom of that page you will see how to use SQL in a DAP.

Mine was this:

'sSQL = "SELECT [tblTest4].[Caption_Name] " & _
"FROM tblTest4 " & _
"WHERE ([tblTest4].[Caption_Name]) LIKE '%Rice%';"

This just hopes to find Anne Rice and other Rice's in the table
tblTest4
and
the field Caption_Name; it will also find Rice Krispies but that's
fine. I
wittled it down to just what you showed me before. The one from Microsoft
only works in Query in Access. Anyway my two list boxes one bournded
to
Caption_Name and the other bounded to Caption_Title flip through each
entry
when I activate this SQL. Trouble is I do not get a new recordset.

The
one

the page opens with is all that results. The rest of the code for

this
is:

'MSODSC.Connection.Execute sSQL
'Set rs = MSODSC.DefaultRecordset
'MSODSC.CurrentSection.DataPage.Requery
'MSODSC.CurrentSection.DataPage.MoveFirst
'rs.MoveFirst

The example from the KB article just resulted in the same recordset

as
the

page has when it opens.

As you can see it doesn't work for I have commented it out for another
day.
I thought MSODSC.Connection.Execute sSQL would give a new recordset but
Set
rs = MSODSC.Connection.Execite sSQL didn't do it. So I just putzed around
trying to see if I could get a new recordset from the SQL and well
nada.

That's what I am trying to do. I set up a filter that would have

given
me

what I want but it took too long for a 1000 recordset database. So it
needs
to work and it can't take all day.

--

George Hester
_________________________________
Quite honestly, I'm not sure that LoopCnt plays a meaningful role in this
script. The example seems to be an "old" one that has not been
updated/reviewed by MS in quite a while, and likely it was originally
written for a different purpose.

How are you wanting to use this script as a dynamic query? Where are you
putting the results from the opened recordset (I assume that you're
not
wanting to just display them in a MsgBox popup?)?

--

Ken Snell
<MS ACCESS MVP>



Ah that makes a little more sense thanks Ken. Now for the counter
LoopCnt.
Doesn't that need to be incremented somewhere or is that native to
customers.htm somewhere? I don't have that html. Do you think you could
send
that to me? I don't need the accompanying data. I just need the source
code
to see how they are using it. Thanks.

--

George Hester
_________________________________
message
Looks like a typo in the script to me.

Try this script instead (I have replaced strRecordsetDef with rsDef):

Dim rs
Dim rsDef
Dim LoopCnt

LoopCnt = 0
rsDef = MSODSC.RecordsetDefs.Item(0).Name
Set Rs = MSODSC.Execute(rsDef)

Rs.MoveFirst
MsgBox "Company: " & Rs("CompanyName") & vbNewLine & "Contact: "
&
Rs("ContactName") & _
vbNewLine & "Phone: " & Rs("Phone")

Set Rs = Nothing


--

Ken Snell
<MS ACCESS MVP>


http://support.microsoft.com/kb/304724

I just cannot seem to verify the argument of the MSODSC.Execute
command
nor
can I determine what is happening with LoopCnt. Can anyone shed some
light
on this script besides the fact that Microsoft wishes DAP would die
gracefully? Thanks.

What I am doing is trying to make a "dynamic" query in a DAP
page.
And
something tells me I do this with a Server filter and RecordsetDef.

 

[Ken Snell \(MVP\)]

Good work! I saw that other post, and will be keeping a copy for future
use/reference.

 

[George Hester]

Thank you Ken. There is a small cavet here. The ServerFilter command does
not really do a complete refresh (recalc is the technical term). If it did
this script would be called:

<SCRIPT language=vbscript event=Current(x) for=MSODSC>
<!--
'Initialize controls
-->
</SCRIPT>

which should be in every DAP page. I am assuming that this script gets all
the events for MSODSC but there must be some it does not receive because,
well, some controls have to be reinitialized after the ServerFilter command.
The literature on this control is sparse at best and I wouldn't be surprised
if that is why this technology never took off. I like it because the use of
these controls can all be written in Notepad which for me is a good
programming environment. But without literature it can be a headache. Well
last to do is put a progress bar in the page so I can know when things are
"happening" and then look for another "project." Thanks for all your help.