Cant filter or sort columns in a form

[whilburn]

I am using vba with an ADO record set. I need the data in the form to be in a datasheet format.

When trying to sort, I get a message that states "Data provider cannot be initialized".

I realize that there are binding/non binding issues going on here but the data is in the controls. Therefore, why isnt it possible to sort or filter any of the data in the columns.
I have tried to bind the data to the controls by setting each value to equal the corresponding rs.field(field name) and there is only a single row returned for the whole data set.

I also realize that there are other methods that may work as well such as DAO and am willing to explore what ever works.



What will not work for me is to just bind the form data to a query. I have already done this in my real data base and because of the unique relationships I need to use a solution using vba.

I dont know if the issues lie with the properties of the form, lack of required vba code, or If my whole approach needs to be rethunk...

Thanks,
Wayne

[Micron]

try commenting out these lines
'.LockType = adLockOptimistic
'.CursorType = adOpenKeyset

EDIT - never mind. Worked for me at first, then not.

[isladogs]

What will not work for me is to just bind the form data to a query.

Really? Why not?

I have already done this in my real data base and because of the unique relationships I need to use a solution using vba.

What unique relationships could possibly require this solution?

You have created a read only form by using this convoluted approach
In fact you can SORT by all the fields except Description (and that's because its a memo field

However, I see absolutely no reason for you to do any of this.
Remove the form open event code.
Set the form record source to the table ...or if you prefer to the query
You will then be able to sort/filter/edit the form data
That will work perfectly whether the table is local or linked. Neither ADO or DAO is needed

Two other things
1. You should have the line Option Explicit as the 2nd line in EACH code module
2. Recommend you use much shorter field names - as the table is called BizTermRelationshipType (also long), you can shorten all field names to something like TypeID, AbInitIdent, TypeName, TypeDescription

[whilburn]

whew...
I appreciate the response but..
I have a larger version of the real database with over 82 tables. some of the joins are self joins, look up or reference tables and many-many join tables. I actually set up the relational diagram so that the dbms can manage all the cascade deletes.
I did not choose the field names and they are required to be set as they are. This database is part of a data standardization extract from a large COTS system that was super convoluted and this access solution is an interim solution until we can subsume all the data into another expensive COTS solution....
What may appear as wacky, redundant, and convoluted to you is the world I am required to provide a solution within. Also, the data in the database is all read-only. I have no intentions of allowing users to change/add data. There is a huge scale of work being performed but it goes through a workflow process and I use linked files to upload new linkages between two existing pieces of data, but only after following an Outside-the-system-workflow.
I cannot share the bigger real version of my database because it is proprietary and contains schema and metadata that cannot be shared.
In the big database all my code is clean and I do my best to provide the brush stroke perspective of the code that handles all the objects, repaints the forms and manages the data as necessary. Up until now I have lived with the "every now and then" bug where the query that feeds the sub-form and somehow a field ID isnt passed from a main form into the sub form. I tried to create multiple queries and sub-select queries to restructure the data in query form so that I would work, I also reduced the number of tables in a query to specifically reference a FK vs a PK. For some reason, the problem only happens every now and then. I have not duplicate values in any of the tables, and there are no data validation issues as well and there are no nulls.


Would a possible solution be to populate a different control then set one control to = another control? and not make the first control visible?

[isladogs]

OK lets forget the field names as that was peripheral to my main point.

So you have self joins and other complex relationships .... but that doesn't answer my main questions.
Why do you believe you need an ADO connection for a form based on a single table or indeed even if it was based on multiple tables?
Nothing you wrote explained that to me.

I've been using Access for 20 years.
I have databases with over 300 tables, plus all the other things you described & based on multiple BE files in both Access & SQL Server
I cannot recall a single time I've ever resorted to doing something like this but I'm happy to be educated into knowing why anyone would ever need to do so.

I've already established you can sort the fields on your form (except the memo field which can never be sorted) but agree you can't filter it.

Using your word from post #1 - this needs a rethunk

[whilburn]

Its odd that you can sort on the non memo fields in the example db.. I cannot. I get the error "Data provider cannot be initialized".

The best explanation I can give is that I have a series of 4 tables where two contain a fk from a single table, but in some instances only one of the two is referenced, and in other instances, the other table is referenced. However, in true architecture of the data, there should only be a single fk of the one table and the 4 tables should cascade through to each other. I think this introduces a bit of recursiveness due to the fact that the relationships diagram is where the joins and their types are established. However, Like I stated, there are queries where I deviate from how the relationship diagram is established and this is one of those instances. I could extend my schema or set one approach over the other, but from a business architectural perspective I have to support these conflicting scenarios. As that is how it was in the original COTS system. That doesnt mean it is correct, it just means it is...and like I stated, I only get a bug every now and then. If its not repeatable, its difficult to track down.
90% of the databases I run into for any kind of help has zero relationships established in the relational diagrams. Most people just use queries to structure the data and the tables or use vba code to create record sets and go from there; thats my experience anyway.
My goal was to keep the original schema and create a work around.
I was hoping I could bind a control to a recordset; this may not be the norm, but what is the norm? I have been programming for about 18 years in various platforms, and most of the code base and object reference material from microsoft was far better a decade ago. There seems to be so many hidden gems that can be found in the forums but they certainly dont pop up except by accident. I am limited by the tools at my disposal because of limitations imposed on the equipment I have. We pretty much either use microsoft products or million dollar COTS solutions... Neither of which are optimum for running a database for 20 users, where the bandwidth on our shared drives is too low to split it and run a back/front end and let people make changes; partly because of DISA contstraints where Share Point and Access is Throttled/inhibited due to network settings and DBMS engine regulations. So everything I do is a work around.
Back to the issue.
I chose a single table of data to try and create a least problematic approach to explore the different methods that lead to a solution.
Are you saying that there is no possible way to use an ado/dao approach to solve this issue?
No offense, but you seem to be more interested in why I cant solve the problem with the traditional way you would, and I cant say that I dont have an issue with joins/types in my big database, but I will say that I have tried all the joins from opposite directions, and the join types, even if it didnt make sense. To no avail.
Lastly, in my experience, my queries that are based on the ado/dao code seems to be more efficient than with the queries developed within the query builder and referenced within the tables. I have search as you type functionality using list boxes for quick reference and selectability and forms with unbound subforms that are all generated instantaneously and killed when another subform is selected. Unless I am missing something, If sub queries are created without the use of code, they load and stay loaded. I have upwards of 8 sub forms that can be selected and they all fire instantly and my resources are kept to a minimum.

[whilburn]

Isladogs, if for no other reason, I am glad to have posted here. I saw your website. I sent it to my work so I can take a better look. You have a lot of information on your site.

thanks
Wayne

[isladogs]

Sorting works as long as its done first
Filtering cause the data provider could not be initialised error
Once that occurs, sorting isn't possible either.

A Google search indicates that others have the same issue with this approach. For example https://stackoverflow.com/questions/...a-provider-cou

I'm still not clear whether you have a split database or not
If its not split, then it makes no sense. If it is split, then linked tables are the standard approach

Neither of which are optimum for running a database for 20 users, where the bandwidth on our shared drives is too low to split it and run a back/front end and let people make changes

If I'm reading this correctly you have multiple users sharing the same shared FE. If so, that is a guaranteed recipe for corruption

I've read your explanation in post #6 several times & am still no clearer why you want to use this method when there is a simple alternative that does work!

The main purpose of relationships is to impose referential integrity. Some would say its the only purpose
Unless you use RI, there is no significant benefit to creating relationships.
When writing queries you can either use or ignore saved relationships & you can create others just for that query. That is of course standard practice

In my opinion, if the original schema is flawed then keeping it and creating a workround is the wrong answer.
It may work for a while but eventually everything will either seize up or may come crashing down like a house of cards.

If this worked, I would say OK - fair enough though not how I would do it ... but it doesn't work!
For that reason, I'm questioning your whole convoluted approach rather than casting around looking for sticking plasters

Lastly, in my experience, my queries that are based on the ado/dao code seems to be more efficient than with the queries developed within the query builder and referenced within the tables.

Do you have real evidence to back that up e.g. speed tests, CPU load data ?
I normally use SQL statements rather than saved queries but only use/loop through recordsets (normally DAO) where there is a valid reason to do so

I hope you find the website useful.
For now, I'm unable to offer any suggestions to assist you with your current setup.

Hopefully someone else will step in and advise. Good luck