ADODC w/Access Query - How to switch to another query? 4

[Sideman]

Hi, everyone.
I'm a struggling learner, so please be patient with my simplistic question.

I'm using VB6(SP5) and Access 2000 in my project.
I have an ADO Data Control which is assigned an Access stored query. From the resulting recordset I fill some text boxes and a Data Grid. Works very well and it's fast.

Here's my problem...I want to change to a different query when a user option is selected on the form. I can't find the way to do this with the Data Control. (I know I can use a SQL style query string and code the entire connection setup, but I'd like to use this format if possible)

Is there a way to change the ADO Data Control's DataSource to different stored querys which will replace the previous recordset?

Yes, I did ask a similar question in the VB5 and 6 forum, but my question was poorly formed. I hope this is clearer. I'm appologize if this was improper to do.

Thanks for any advice.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[pweegar]

Unfortunetly, a data control is tied to a data source. One way to get around your problem would be to add a second data control (along with it's data source), and disable and hide this second source. Then, when a user selects, via your form, a different query, hide and isable the current control and then enable and make visable the second datacontrol. Place the second one over the top of the first so that it looks like they are one in the same. This SHOULD work.

 

[Sideman]

Thanks for the reply pweegar.

I think I'm just going to have to hard code the recordsets and abandon the Data Control altogether. Can't seem to find a simple technique for using it the way I described.

Thanks anyway.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[CCLINT]

1. Do not set the ConnectionString or RecordSource properties in the data control's property window.

2. On the Form_Load event, set the following:
Adodc1.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\MyDB.mdb;Persist Security Info=False"

Adodc1.CommandType=adCmdStoredProc

Adodc1.RecordSource="spMyStoredProcName"

Adodc1.Refresh


3. When changing to another query, do this:

Adodc1.RecordSource="spMyOTHERStoredProcName"

Adodc1.Refresh

 

[Sideman]

As usual, I can always count on you to have a good idea, CCLINT. I'll try your suggestion and post back how it worked out...will probably be tomorrow.

Thanks again.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Sideman]

Well, CCLINT, once again your suggestion was spot on.

The suggestion worked exactly as I wanted, and when coupled with the Filter property, I was able to add some nice extra functionality.

A star for your expert help.

Thanks a milliion.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[CCLINT]

I'm happy it worked for you!
Thank you for reporting back as you have - this is how the value of a Tek-Tips thread, and Tek-Tips itself, increases many-fold - others can see a confirmation of what actually has worked out.
And, it gives ones like myself the boost often needed to carry on...
So many people take (free) help often for granted (often just because of thoughtlessness), only thinking of themselves and forgetting others in the community, and that can lead to discouragment, and a waste of resources.
AFAIK, the ones who act this way will either be gently reminded, and/or if necessary, will have their future questions ignored.
Thank you again!

 

[cyberbiker]

CCLint,
I appreciated this post also. I was having a similar problem with SQL Server connection and did not interpret the error message I was getting properly. Your post clearified my problem

Your pocedure worked perfectly again.

Terry (cyberbiker)

 

[vb6novice]

CCLINT,

Iv'e been wanting to try your soultion and I will as soon as I can get to a computer with VB6. I have a question, though, about how this will function when creating data bound controls. If Connection and RecordSource properites are left blank, then there won't be any fields to choose from in the text boxes and other data bound controls.

All I can think of at this moment is to specify a Connection and RecordSource during design that will likely be used at run time, create the other controls so I can pick the fields, and then remove the Connection and Recordsource before compiling. Is that going to be a problem?

 

[CCLINT]

You only need to set the controls datasource and datafield at run time:

Text1.DataField = "SomeFieldName"
Text1.Datasource = Adodc1.Recordset

Make sure to set them in that order.

 

[vb6novice]

Setting those properties at run time is exactly what I am trying to avoid. I want to be able switch between 2 Access 2000 databases which have the exact same tables with the exact same structure (just different data). Even the RecordSource SQL statement will be identical. All I really want to do is change the DatabaseName property. I expect that I would have to close or unload the form, change the property, and reload and show the form, or some other type of refresh.

I have now tried your solution and the adodc1.ConnectionString is not even available in the methods/properties drop down for the control. What am I missing.

 

[CCLINT]

>adodc1.ConnectionString is not even available

Sure it is.

 

[vb6novice]

I wish it was. CommandType isn't there either, but RecordSource is. Could it have something to do with the References?

 

[CCLINT]

Well, check the component references and see if the "Microsoft ADO Data Control 6.0" is checked.

I have a feeling you are using a DAO data control and not an ADO data control. This would be the case if the control has a Connect property and a DatabaseName property.

 

[vb6novice]

CCLINT,

I've been reviewing a number of the posts on this issue, and some of the MSDN information from Microsoft on the difference between DAO and ADO and the pros and cons of each. It still seems like a great many people are still confused. Your posts in most of the questions do provide good insight into the intricacies of the data realm. I am learning and will endeavor to utilize that knowledge in my next VB project.

The current problem, however, is in a project I am helping someone else with. He is from the pre-punch card era and is code-averse. He has developed a rather decent VB6 accounting application based on a DOS application that he created in the 80's. The company he wrote it for has been using the DOS one for 20 years but wants a Windows version. I talked him into using Access 2000 to create the database so that he could create and test queries using the Access application, and then use the VB6 app as the front end. What he has created so far relies heavily on the DataControl which has the Property 'DatabaseName'. Based on my reading of related threads and the MSDN topic 'Porting DAO Code to ADO with the Microsoft Jet Provider', it appears that the DataControl and many of the other controls are DAO based. I'm not where he is so I can't just go and verify that. Here's the real problem.

The application is essentially complete. It is to be used by an accounting firm for a number of their clients. The application needs to be able to switch between clients and between different years of information for the clients. The data will reside in a number of databases, one for each year for each client (some of the clients go back 20 years) so the number of databases is going to be close to a hundred. All the databases have the same tables and all the tables have the same names and identical structures. The only thing that is needed is to be able to select a different client/year's database during runtime, perhaps picking them from a series of combo boxes or a treeview control on his main form. I've told him it is possible. What's it going to take to be able to do that? Is it possible using the DAO controls? If so, how? If not, what will be involved making a switch to ADO, and how will that switch affect all the other databound controls? Code needs to be kept to a minumum for him.

Hope this helps clarify where we are trying to go. I appreciate your input.

 

[CCLINT]

If the Tables are identicle in names and structure, and the DB's are both JET 4/2000 MDBs, then just change the Data1.DatabaseName to the path and name of the db and then do a refresh: Data1.Refresh

You might want to leave ALL properties for the data control as defalut (no recordsource, no databasename, etc) and create the database and recordset objects in code.

This gives alot more flexibility.

Then you only need to do this: Set Data1.Recordset = myRS

 

[vb6novice]

Thanks CCLINT,

The system designer confirms that your solution works for him. Another star.

Thanks.

 

[MichaelRed]

Seems like (from here anyway) like all "similar" tables could easily be combined and the Company / year used as a "filter' which could greatly reduce the overall proliferation of dbs and tbls. I keep information on literally hundreds of Stocks on a daily basis in the same table(s) for my investment club and easily extract data in a wide variety of patterns.

Company names (ticker symbols for me) and date for various information categories provides the 'keys' to retrieve all of the subsets of info necessary.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places