ADO Recordset as Form's Data Source - Removing a Filter Raises Error

  • Thread starter Thread starter rm
  • Start date Start date
R

rm

I hate to be this in-exact in a post but I have limited access to this
fantastic news group while I am here in Iraq. I have to take advantage
when I can.

I am using a disconnected ADO recordset as a Forms RecordSource
(DataSource, Control Source I cannot remember the exact property). I
do know that the approach works just fine for displaying and searching
for data in my read only form.

When viewing the data in a datasheet view I can filter the data
without problem. However when I attempt to delete the filter an error
message to the effect of "Data provider not found" is displayed on
screen. Do I need to change a property on the ADO recordset? Any
suggestions?
 
I don't know that you are aware of this, but there are likely only 3 people
on the planet who are using disconnected ADO recordsets are the record
source of Access forms. In that you are 1/3 of the entire population of
people who are doing this (strange?) sort of thing, that does not leave many
others who have the experience, and know intuitively what the issue is.

You really need to post the code you are using with adequate comments
showing where it breaks and what the error(s) are. Folks around here are
more likely to provide you some help if they do not have to write the whole
app from scratch to see what's happening.

Also there are likely other (more Access centric) ways to do what you are
doing. How about stepping back a little, and explain to us what you are
doing, and why (how?) you came upon this obscure method. I and others are
willing to help if you'll give us a small leg up.

Rdub
 
Thanks for the response. I am in a difficult situation here. I work on
a secured network & do not have access to the newsgroup from my
typical place of work. And I cannot take anything that is on our
secured network and place it on an unsecured network. So what I do is
dodge bullets and mortar rounds to travel to a place where I can find
answers to my questions to get my job done.

Also, I am a "old timer". I spent the last 10 years working on large
scale systems that support thousands of users. ADO was an old stand-by
until ADO.NET came along. I am using it because that is what I no
(when I am not in the realm of VBA). Now I am in a situation where all
I have is Access, a tool that I have not used since 1998.

I am stuck with Access and am in a situation where the Access database
is in a Read Only location. I CANNOT change that. Nothing I can do. I
am stuck with that situation.

What I figured out is that as long as the entire population of users
of the application has read only access then I am ok. So I built a
lookup application that retrievs a recordset from a db the presents
the result set in an interface. I want the user to be able to slice
and dice the dataset.

I built a "lookup form" that accepts a few parameters. The parameters
are stuffed into a SQL statement. A connection is opened, the sql
statement executed against the datastore, then the results are
presented in a datasheet.

From that point the users can set filters on the dataset as expected.
Yet when the user attempts to remove the filter and error is thrown.

That is pretty much it.

Thanks for the response.
 
I am also an old timer, and dam thankful that there are guys like you who
are willing to go to places where there are bombs and bullets. I appreciate
your service.

All I can say is that this seems to work:

I created a Form with four text boxes bound to the four columns in the sql
statement. I set the forms Default View to a "DataSheet", and entered this
code behind the from. Watch for word wrap!

' -=-=-=-=-= Code Starts -=-=-=-=-=
Option Compare Database
Option Explicit

Dim rsTest As ADODB.Recordset ' Form level scope

Private Sub Form_Load()
' Purpose Open a ADODB recorset and bind it to the form
' Then disconnect it from the data source

Dim cn As ADODB.Connection
Dim strConnection As String, strSql As String

'Create a connection to a data store somewhere
Set cn = New ADODB.Connection
strConnection = "A Valid connection string to your Data Source"
cn.Open strConnection

' Create recordset
strSql = "Select SessionDate, StartTime, Descr, Venue " _
& "From tblSession Where SessionID < 1200"
Set rsTest = New ADODB.Recordset
rsTest.CursorLocation = adUseClient
rsTest.Open strSql, cn, adOpenStatic, adLockBatchOptimistic

' Bind form to recordset.
Set Me.Recordset = rsTest

' Disconnect it
Set rsTest.ActiveConnection = Nothing
cn.Close
Set cn = Nothing
End Sub


Private Sub Form_Unload(Cancel As Integer)
' Close the recordset.
rsTest.Close
Set rsTest = Nothing
End Sub

' -=-=-=-=-= Code Ends -=-=-=-=-=

I then set the database as Read Only. I open the database and dismiss the
"This database is read only!" warning. From the Database window I am able
to double click my form and see all of the appropriate records. I am able
filter the records using the standard Access right click gui interface. I
can Apply and Un-Apply filters without error. All of the data in the
datasheet is read only as expected.

Perhaps you can memorize this code (or carry it into the secured facility as
hard copy) and try it with your environment. Without more information from
you, or at least an example of how you are coding this, I am afraid that I
can not be of more help.

Anyway now I guess that there are four people on the planet that have at
least some knowledge of binding Disconnected ADODB Recordsets to Access
forms.

Keep your head down and come back safe!

Rdub

Thanks for the response. I am in a difficult situation here. I work on
a secured network & do not have access to the newsgroup from my
typical place of work. And I cannot take anything that is on our
secured network and place it on an unsecured network. So what I do is
dodge bullets and mortar rounds to travel to a place where I can find
answers to my questions to get my job done.

Also, I am a "old timer". I spent the last 10 years working on large
scale systems that support thousands of users. ADO was an old stand-by
until ADO.NET came along. I am using it because that is what I no
(when I am not in the realm of VBA). Now I am in a situation where all
I have is Access, a tool that I have not used since 1998.

I am stuck with Access and am in a situation where the Access database
is in a Read Only location. I CANNOT change that. Nothing I can do. I
am stuck with that situation.

What I figured out is that as long as the entire population of users
of the application has read only access then I am ok. So I built a
lookup application that retrievs a recordset from a db the presents
the result set in an interface. I want the user to be able to slice
and dice the dataset.

I built a "lookup form" that accepts a few parameters. The parameters
are stuffed into a SQL statement. A connection is opened, the sql
statement executed against the datastore, then the results are
presented in a datasheet.

From that point the users can set filters on the dataset as expected.
Yet when the user attempts to remove the filter and error is thrown.

That is pretty much it.

Thanks for the response.
 
Back
Top