SQL vs DAO or ADO Object Model

[jerryczarnowski]

Hello Access world:

I have an attachment representing my current challenge...use VBA to manipulate SQL or VBA to manipulate the DAO or ADO Object Model to obtain the results outlined in the attachment. I am using ACCESS 2010 and VBA as the development tool of choice. Being new to SQL, I have a bad habit of going the lengthy object model route and always feel like there is some SQL statement or statements that can accomplish the same thing. Any help would be greatly appreciated.

Thanks, JC

 

[Pat Hartman]

I don't understand why you wouldn't just use the QBE to build the two queries. I don't see anything that would require or even benefit from manipulating the object model. Queries are significantly faster than code loops although you won't see it with a set this small.

I can't tell how tableA fits into the picture at all.
The Duplicate query would be:

Select Pocket, Count(*) as PocketCount
From TableB
Group by Pocket
Having Count(*) > 1;

Then to get the results for the Unique query you would join TableB to the Duplicate query.

SELECT TableB.Pocket
FROM TableB LEFT JOIN qUnique ON TableB.Pocket= qUnique.Pocket
WHERE qUnique.Pocket Is Null;

 

[jerryczarnowski]

Hi Pat, Thanks for the quick response. It will take me some time to learn and test the SQL statements that you provided on my application. As you can see, I am not up to speed on SQL. I did not want to go the object model route as I had a feeling this was an SQL task...I just didn't know where to start. As far as the QBE, the application has around 80 TABLE A'S that are used to dynamically create queries based on user selection and I intentionally do not want the user to go through the standard Access interface. I am at the beginning level of database development and by utilizing the experienced people as yourself on this forum, it could teach me real world practical design techniques as beginners like me know enough to be dangerous. Once again thanks and after testing, I may still have a question or two.

 

[VilaRestal]

the application has around 80 TABLE A'S that are used to dynamically create queries based on user selection and I intentionally do not want the user to go through the standard Access interface.

Hi Jerry,

That quote rings a couple of alarm bells. I hope it's just a turn of phrase and really you mean:

"has around 80 tables that are used in dynamically-created queries"

And of course you don't want the user creating queries in the QBE but that doesn't mean the developer shouldn't. Create them dynamically when the SQL needs to change at runtime. If the SQL remains static then build them in the QBE (design view or SQL view, whichever is easier). Even for dynamic queries, having prepared static building blocks can be useful and make the code easier to write, read and maintain.

Be careful not to succumb to the temptation of the Inner-Platform Effect.

To quote from the wiki article wrt databases:

"In the database world, developers are sometimes tempted to bypass the RDBMS, for example by storing everything in one big table with three columns labelled entity ID, key, and value. While this entity-attribute-value model allows the developer to break out from the structure imposed by an SQL database, it loses out on all the benefits, since all of the work that could be done efficiently by the RDBMS is forced onto the application instead. Queries become much more convoluted, the indexes and query optimizer can no longer work effectively, and data validity constraints are not enforced. Performance and maintainability can be extremely poor."

I can't tell if there's a danger of that but, as I said, some of the words you used rang alarm bells in that area.

 

[Pat Hartman]

That's a great article Vila. I can't tell you how many times I have seen this happen -because it would reveal my age which a lady never does

 

[VilaRestal]

Thanks Pat. I think it should be compulsory reading.

It is sadly a very common phenomenon: you wouldn't have to live very long at all to come across it a few times. Don't let it make you feel old

 

[jerryczarnowski]

Hi Vila,

Sorry for not replying sooner...work...kids..etc. You are correct as far as the 80 tables that are used in dynamically-created queries and yes the SQL needs to change at runtime. I havn't had a chance to try Pat's code but that is the route I will pursue. Any feedback is appreciated as it either keeps me in check or provides avenues I did not know about.
Thanks, Jerry

 

[Pat Hartman]

I have seen very few situations where the SQL actually needs to be dynamic (changes at runtime). Most of the time parameters solve the problem. Dynamic SQL causes severe database bloat so you shouldn't use unless you really need to.

 

[VilaRestal]

I take it you're referring to changing existing querydefs and saving the changes Pat.

Using dynamic SQL strings in VBA (setting recordsources and rowsources to them, creating new [unsaved] querydefs and opening recordsets with them) has never caused any bloating in my experience.

In fact, you can do all those things in a read-only database without error (and of course without a single bit of bloating) so I don't see why it would in a modifiable one.

 

[Pat Hartman]

You must be compacting your database on close which is actually not recommended.

When querydefs are created and saved, Access calculates an execution plan. This takes work space that cannot be recovered until the database is compacted. Once the execution plan has been saved, Access uses it for subsequent executions.

SQL strings in the RecordSource of forms and reports and the RowSource for combos and listboxes are all stored as hidden querydefs. To see them open MSysObjects and look for queries with names starting with a tilda "~".

SQL strings in VBA must have execution plans calculated EVERY time they run. This adds a small amount of time but most apps don't suffer from that. The problem is the bloat caused by the calculation of the execution plans.

Unset the compact on close and watch the database bloat. The rate of increase will of course depend on how many dynamic queries get executed while the db is open.

 

[VilaRestal]

You must be compacting your database on close which is actually not recommended.

Nope, I never do that.

When querydefs are created and saved, Access calculates an execution plan. This takes work space that cannot be recovered until the database is compacted. Once the execution plan has been saved, Access uses it for subsequent executions.

Indeed, but I'm wondering about querydefs that aren't saved.

SQL strings in the RecordSource of forms and reports and the RowSource for combos and listboxes are all stored as hidden querydefs. To see them open MSysObjects and look for queries with names starting with a tilda "~".

Likewise, that's for the saved recordsource, rowsource, etc. When they are changed at runtime (but not saved) then there's no change in the database size. (I have just tested this - opening a database, running code that changes the recordsource on a form to a SQL string, 30 times in a row with an incrementing number, then closing it - it started as 507,904 bytes and was 507,904 bytes after. There was no compacting on close.)

And like I said, such code works fine in a read-only database where it cannot possibly bloat (the OS won't allow it) so why would it decide to bloat when it's not read-only if it's optional.

I think you must be mistaken on this Pat. I'm pretty sure execution plans are calculated in memory and would only be saved in the database for query defs that are also saved. (Which would in fact be an argument for not saving them.)

 

[VilaRestal]

And another test:

A database with
a table
an unbound form
a button on the form that sets its recordsource to a SQL SELECT string including various components in its where clause (nothing dynamic about it, but not a saved query)

I open the database, open the form, click the button, close the form, open the form, click the button, close the form, etc, etc,

I close the database and repeat the process.

The database is exactly the same size as it began.

 

[jerryczarnowski]

Hi Pat,
Learned some new features from your code and from your conversation with Vila...gives me things to consider on my project. I ran your code and it gives a listing of all the pockets that have duplicates in table B and a count of each as well. This leads me to an opportunity to explain how table A fits into the picture. I did not provide an accurate table B on the screen shot I provided (sorry). The actual table B typically has around 70 pocket values with duplicates and I want to extract only the duplicate pockets that also match a value in table A. Can you tweak the duplicate query code to where it only lists the duplicates of table B that must also match one of the values in table A? As far as the Unique query, I did not get it to work yet..curious how the qUnique works...basically how the unique query code you provided works. Once again your help is greatly appreciated and if there is any more detail I can provide (screenshots,etc.) let me know.

Thanks, Jerry

 

[Pat Hartman]

Take the second query and create a third query that joins qry2 to table A. That will restrict the output to only rows that match in both tables.

 

[jerryczarnowski]

Hi Pat, I took your advise on creating a third query from the second and combined them into a nested query which worked perfect. Also used DAO to create a temporary query of the first query which is deleted later to allow creation of new queries without the query exits warning and allowing the nested query to reference the first query. Below is the new code. Thanks to your examples and advise, my problem is solved.


SELECT [TABLE A].Pocket, [TABLE A].[Cutting Tool ID]
FROM DUPQUERY INNER JOIN [TABLE A] ON [DUPQUERY].[Pocket] = [TABLE A].[Pocket]
WHERE EXISTS (SELECT Pocket, Count (*) AS PocketCount
FROM [TABLE B]
GROUP BY Pocket
HAVING Count (*) > 1);

 

[jerryczarnowski]

I take it you're referring to changing existing querydefs and saving the changes Pat.

Using dynamic SQL strings in VBA (setting recordsources and rowsources to them, creating new [unsaved] querydefs and opening recordsets with them) has never caused any bloating in my experience.

In fact, you can do all those things in a read-only database without error (and of course without a single bit of bloating) so I don't see why it would in a modifiable one.

I know this is a late response but your statement above on dynamic SQL strings and unsaved querydefs used for setting recordsources is exactly the direction I am going with my app. It is nice to know that this does not cause bloating and dynamic SQL is awsome. I sent Pat an example of the final code used in a nested query. Between the two of you, my problem is solved. I will probably have more questions for you later and appreciate your input.

Thanks, Jerry