Solved DAO Workspace Database Collection

[GPGeorge]

Spoiler:

Access does NOT use stored procedures. Stored procedures are a construct specific to actual RDBMS' such as SQL Server and Oracle. At the moment we are not speaking the same language. Access uses embedded SQL - which means queries that you create on the fly using VBA code. OR it uses QueryDefs which are a construct specific to and limited to MS Access the RAD tool. Since Access treats querydefs and tables as the same type of object for most purposes, other front ends such as Excel can "see" querydefs provided they are SELECT queries and do not use any VBA.

I don't believe any of us have any idea what you are using as a front end - platform which runs your queries and produces your forms and reports. What are you using as the user interface to show/edit your data since you are not using Access. In your mind you have confused Access the RAD tool with Jet and ACE the desktop database engines.

Spoiler:

Access the RAD tool is dependent on Jet (.mdb format databases) or ACE (.accdb format databases) to hold object such as forms, reports, querydefs, macros, and code modules. Access is completely independent from Jet and ACE when it comes to storing actual data. Access can use ODBC to link to ANY RDBMS that supports ODBC. You are using Jet to store your data but are calling it Access which is a fairly typical misconception. But you are not using Access and Access doesn't even need to be installed on your computer to use Jet. Looking at it from the other direction, Jet and ACE have their own .exe and Jet can exist without Access at all. ACE is more tightly bound to Access the rad tool. Jet is still an independent desktop database engine and is managed by the SQL Server team. The Access team wanted more control so with A2007, the Access team took a copy of Jet and made it into ACE which gives them independence from the SQL Server team and the ability to deviate if necessary. But Jet and ACE can be used via DAO or ADO from any platform that supports DAO or ADO and you would never have to open MS Access at all. In this case, Access functions as the equivalent of SSMS (SQL Server Management Studio) and gives you a GUI to manipulate table objects so you don't have to use code to define or modify them.

So, please clarify your platform because you need to think about how much of your infrastructure you want to rebuild if you switch from Jet to SQL Server. You could rebuild your application interface using Access the RAD tool and either stick with ACE or switch to SQL Server.

There have been contradictions between -- and even within -- posts about what tools the OP actually uses or doesn't use, or how they are used, if at all.

Other than tables and queries in either an mdb or accdb (and I'm not sure even that much was ever clarified), we have no clear picture what is going on.

We've reached an admirable number of posts, though, and that's a significant achievement in itself.

 

[dw85745]

Personally we are beyond the point of this thread and in a debate over whether one calls a "toma'to" a "to'mato".
Since I am unable to post a link, hopefully putting it in quotes will work. ERRORED even as a quote.
I'm deleting the internet prefix in the hopes that works. It does.

This addresses stored and unstored queries (procedures).

learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/querydef-object-dao

Specifically

You can also create temporary QueryDef objects. Unlike permanent QueryDef objects (HERE M$ HAS ADDRESSED BOTH UNSTORED (Temporary) AND STORED (Permanent) QUERIES/PROCEDURES) , temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection ...

You can think of a permanent QueryDef object in a Microsoft Access workspace as a compiled SQL statement. If you execute a query from a permanent QueryDef object,, (NOTE THE WORDING -- PERMANENT and COMPILED)

LASTLY, As Ive addressed above, a compiled object can be early or late bound depending on how it is declared.

As far as the front end, I believe I've addressed this. I use various languages to create objects (e.g. Pictureboxes, Textboxes, Treeviews, etc).
and Reports, mostly using API calls.

HTH

 

[The_Doc_Man]

Since you are satisfied that you have learned sufficiently from the information presented here, I can only wish you success in what you are doing. We will be here if other questions come up and you think we might be able to help.

 

[Pat Hartman]

Personally we are beyond the point of this thread and in a debate over whether one calls a "toma'to" a "to'mato".

There is no debate. this is a technical topic and technical topics use specific language. Words are not interchangeable. You can't just call a querydef a stored procedure because they are two very different types of object. For example a SP actually supports programming logic and can work with multiple recordsets at one time whereas a querydef is a declarative object limited to the SQL language. It can't take logic paths. It does what it does to every record in the SINGLE recordset. Access expands the scope of querydefs by allowing them to reference VBA or UDF functions. THOSE can work with multiple record sets but ONLY if you are actually using Access. If you are using Jet, you CANNOT use VBA functions or UDF's because VBA is simply not available to Jet.

Pointing us to a knowledge base article that defines DAO objects didn't clarify anything.

Early vs Late binding refers to library references, not to hard coded vs parameterized arguments in queries. The term early binding says that at compile time you specify that you are using Office 2010 libraries and Late binding says that you are using whatever Office library is found at runtime.

So, we are no further with understanding your environment 40+ posts in. Have you found what you were looking for so we can stop asking you questions?

If you are using Access and your tables are linked, then you can change the source of the tables by simply relinking the FE to a different BE. It is very simple. But we still don't know if your code is actually running in Access or in a web page or some Python program.

 

[dw85745]

----------------
The-Doc_Man
----------------
Thank YOU.

--------------
Ms. Hartman:
---------------
While I have a lot of respect for you, based on your posts, I respectfully disagree.

For example a SP actually supports programming logic and can work with multiple recordsets at one time whereas a querydef is a declarative object limited to the SQL language

An SQL can use IFs, ANDs. ORs, NOTs, and SubQueries, which IMHO is programming logic.

Early vs Late binding

An object does NOT have to reside in a library. One can create an object in code, and then use it.. Office is just one library. There are thousands.

still don't know if your code is actually running in Access or in a web page or some Python program.

I thought I made that perfectly clear in my last post. I use multiple languages. For example a DLL can be generated from Assembly, C++, or even VB Classic and then the functions within that DLL called from any other language of choice. I believe I also made it clear I'm just using ACCESS ACE (the database only) and not the RAD portion (I'm not sure whether you consider the ACCESS GUI as the RAD or Not, but I don't use the GUI)

A couple good reads if of interest are:
1) Windows98 Programming by Schildt (may be out of print)
2) Advanced Windows by Richter (multiple editions, not sure of the latest edition number).

For whatever reason programmers who focus on a single language get on a railroad track in regard to that language. For example, take the word procedure. There are approx 2500 difference languages. Whether each of them calls their code "procedures", I dont't know. Classic VB (3,4,5, and 6) and C++, calls their code procedures. A Classic VB programmer further subdivides those procedures into Subs and Functions. They would argue a Sub does Not return a value where a function does return a value. YET, one can write a function that does Not return a value. In C++ all code procedures are referred to as functions. They would argue a VOID function does not return a value,, where a function without VOID is just a function. Assembly makes no differention. So who is right?

 

[GPGeorge]

Personally we are beyond the point of this thread and in a debate over whether one calls a "toma'to" a "to'mato".
Since I am unable to post a link, hopefully putting it in quotes will work. ERRORED even as a quote.
I'm deleting the internet prefix in the hopes that works. It does.

This addresses stored and unstored queries (procedures).

Specifically



LASTLY, As Ive addressed above, a compiled object can be early or late bound depending on how it is declared.

As far as the front end, I believe I've addressed this. I use various languages to create objects (e.g. Pictureboxes, Textboxes, Treeviews, etc).
and Reports, mostly using API calls.

HTH

Arrggh. Substituting one term for another in a statement is not an argument about the meaning of those terms.

Here: "STORED (Permanent) QUERIES/PROCEDURES" is such an attempt to use substitution to avoid the plain meaning of database related terms.

Stored Procedure has a specific, defined meaning and usage. It's an object in a SQL Server database, not part of the Access development environment. Ask 100 developers what it means and you'll get some variation of that same understanding from 100 of them.

Query also has a specific, defined meaning and usage. It's an object in any database, SQL Server, ACE, Postgres, MySQL, et al. Ask 100 developers what it means and you'll get a variation of that same understanding from 100 of them.

Temporary and permanent querydefs objects in VBA ALSO have specific, defined meaning and usage.

A temporary querydef is created, and exists only in memory, when a VBA procedure runs; it is dropped when procedure ends. Such a querydef doesn't exist outside that procedure nor does it point to any permanent object outside that procedure.

A permanent querydef refers to one of the saved queries in the database. It's part of a collection of such querydefs, each one referirng to one saved query. A reference to that query can be created when a VBA procedure runs by invoking the appropriate permanent querydef from the collection.

Temporary and permanent refer only to this existential aspect of queries. Are they saved as part of the database? Or are they created in memory by code? No more and no less.

Implicit in understanding the problem here is your frequent misuse of the terms early and late binding.

Those are also specific, defined terms in the context of Access VBA. Early and late do not apply to the temporary or permanent properties of querydef objects.

Early and Late Binding refer to the point during runtime where an object reference is resolved. This can be done prior to runtime or only at runtime. It has nothing at all to do with "temporary" or "permanent" status of the object. If you specifically reference an object, it's early bound. If you let Access figure out what kind of object is expected when the code runs, that's late bound. Again, being repetitious in hopes it sinks in, this does not refer to whether an object is temporary or permanent. It refers to the decision to let Access figure out what object is required (late binding) or do it yourself (early binding).

In conclusion, you can't legitimately throw any of those terms together using parenthesis and forward slashes and pretend that you've made some sort of parallel between them by doing so. That's not how language works and it's not how databases work.

You cannot override definitions with loose syntax.

That's been the crux of this entire discussion.

It's the reason no progress can be made on your behalf. You prefer to argue the plain meaning of common terms should not be applied to your project. Rather than buckling down to the hard work of resolving the need for greater storage space, we're endlessly revisiting the distractions.

So, if the 2 GB limit of the Access accdb is inadequate for your needs, the solution is relatively obvious: move to a different database like Postgres, MySQL, SQL Server Express or one of the many other databases available to you.

Your interface, which is already not part of Access, can be refactored to use those other tables and that's the end of it.

I do wish you continued success with your project, though, wherever your journey leads you next.

 

[The_Doc_Man]

Stored Procedure has a specific, defined meaning and usage. It's an object in a SQL Server database, not part of the Access development environment.

Also found in ORACLE. My experience was specific to ORACLE Enterprise Server.

 

[GPGeorge]

Also found in ORACLE. My experience was specific to ORACLE Enterprise Server.

Yes, sorry. I lost that point in the overall context. However, being precise about definition requires that be clear as well.

 

[dw85745]

GPGeorge:
Since you were kind enough to go to all the trouble to put out those definitions, and since I'm posting on this forum, I'll do my best in any future posts to follow those definitions to keep that thread on track.

Not to cause another off-topic discussion, but:

But arguably the most-debated topic surrounding SQL is whether or not it is considered a programming language.

In that regard here is one persons take. I haven't registered on that site, so don't know all his arguements, or any conclusion reached,
but found the title

10 Reasons Why SQL Is and Is Not a Programming Language​

likely to present both sides of the discussion. Since I can't post a link, I include it as a quote without the https prefix., if of interest.

medium.com/learning-sql/10-reasons-why-sql-is-and-is-not-a-programming-language-d6ccbea2e484

 

[The_Doc_Man]

If you follow the implied link, it wants you to sign up to see the whole article.

The answer to dw85745's bold-faced question must start with a counter-question: "Whose definition of 'Programming Language' are you using?"

By most of the standards I have seen, SQL is a programming language but is not a general purpose programming language.

The languages used by programmable logic controllers count as languages (there are 5 commonly used variants). The language used by certain types of flat-bed plotters counts as a language (variants based on HPGL and its later generations). The language used by CNC routers is a language. ALL of the examples listed here are Domain-Specific Languages. Most if not all of them would fall in the same place when considering the Goedel Completeness Theorem - they are "incomplete" and therefore their correctness is theoretically provable.

In fact, there is a whole specialized branch of logic - called interrogatory logic - that analyzes the validity of answers given by such languages. That branch asks the question: Does this answer satisfy this question? The contrary form asks: Does this question condone this answer? Interrogatory logic is the companion to the more familiar "assertoric logic" that examines the validity of assertions.