Solved DAO Workspace Database Collection

[Gasman]

Link: (for some reason forums will not accept a link).

You need a few more posts before you can post an actual link.
You can break it up with spaces.

 

[cheekybuddha]

You need a few lot more posts before you can post an actual link.

There, FTFY!

(Currently I think the post count required before being able to post links is 100)

 

[Gasman]

There, FTFY!

(Currently I think the post count required before being able to post links is 100)

It is, but I did not want to dishearten them.

 

[cheekybuddha]

 

[dw85745]

my post this morning did Not take for some reason.

-----------------------------------------
NauticalGent: Thanks for responding.
---------------------------------------------
Not familiar with TempVars, but looked at M$ docs about them.
Basically a collection of variables.

AFAIK: Stored Procedures, I believe are compiled so should be early bound objects. This may be the reason M$
distinquishes between non-Stored and Stored, since all code (Symbols in my case) are embedded in the stored procedure.
Whether using TempVar would change a Stored Procedure to late bound, I don't know. My guess is yes, since the
TempVar value needs to be added at time of use, (again in my Case the symbol).

------------------------------------------------------------------------------
Ms Hartman - Thanks for responding. Nice to see a lady on the board
--------------------------------------------------------------------------------
To clear up any confusion.
1) Right now I use ACCESS (JET) for a BE ONLY. This means only Tables and Queries.
2) As I believe I previously posted, I am nearing the upper limits of ACCESS (JET) because of the number of table and queries.
3) My FIRST plan was to split the databases into multiple databases.
HOWEVER, because all procedures are hardcoded with dbDAO{dot}{function}, trying to switch between one database
and another appeared to present a problem as I could NOT assign the dbDAO object varialbe to another database without
issues.
4) Because of number 3, several options were offered up. One of which, you mentioned, was to switch to SQL Server, because
it will handle more (10GB if I recall) versus JET (2 GB). Moving to SQL_Server is a viable option, which I am keeping on the table
as a possibility.
5) ANOTHER Option is to create multiple JET databases. Then at startup, copy over the tables and queries needed, to the
primary BE database (one currently being used). In this way I move a lot of data (tables and queries) out the the primary DB,
but am still able to use the primary. This way I do NOT have to rewrite a lot of code.
6) Regarding TempVars see previous post and response to NauticalGent. It has nothing to do with the DB upper limit discussion.
NauticalGent was kind enough to offer up advice for a comment I made.

Hope that clears things up.

 

[cheekybuddha]

AFAIK: Stored Procedures, I believe are compiled so should be early bound objects. This may be the reason M$
distinquishes between non-Stored and Stored, since all code (Symbols in my case) are embedded in the stored procedure.
Whether using TempVar would change a Stored Procedure to late bound, I don't know.

This all sounds very confused - mainly you are using terminology that most here would understand to be something different from what you are trying to describe.

Are you really talking about stored queries in Access or even a VBA procedure rather than Stored Procedures in SQLServer (or other RDBMS's)?

By late-bound, are you describing dynamic queries constructed using VBA?

 

[dw85745]

This is a test.

Twice previously I've unchecked "Stay logged In" and my posts have disappeared.
What I don't understand is Pat Hartman response after Nautical Gent is also missing.

 

[dw85745]

cheekybuddha and "The_Doc_Man"

In code (most OPP languages and Classice VB -- not sure about VBA but believe VBA does Not have a compiler) objects can be early bound or late bound.
An early bound object is known to the compiler when compilying (e.g. oPBox As Picturebox, or oText As Textbox).
A late bound object is NOT known to the compiler when complying (e.g oThis As Object).

Unless I'm WRONG, M$ forces STORED procedures in the SELECT clause to be hardcoded with table field names.
Other SQL clauses (e.g BETWEEN) can be parameratized.
If I recall correctly, -- M$ docs -- STORED Queries are compiled.
Being compiled they should be early bound, and HENCE M$ requirement that the SELECT clause be hardcoded with table field names.

IF STORED Queries are complied, if makes sense, UNSTORED queries would be late bound, AS the fields and the variables in the query are filled prior to the query being called (executed) -- those fields and values would NOT be known to the compiler at compile time.

I MAY BE WRONG ON ALL THE ABOVE, bur my current understanding.

---------------------
The_Doc_Man:

Given that forms and reports are Access objects, and given that those objects exist ONLY inside an Access database, this statement begs further explanation. What are you calling "forms and reports?" How do you produce them? How do you use them? Thank you.

All of that is done using other languages with mostly API calls.
Where I think a lot of the disconnect is coming from is my view of ACCESS.
I look at ACCESS as being a database (JET), with a RAD attached.
A better wording would have been to use M$ definition -- Access Database Engine (ACE)

All the RAD stuff (Forms, Reports, other objects) can be developed in many different languages (Classic VB (a RAD), VB.NET, C#, C++, Java, and so on) and then just the ACCESS (JET) database used as a database.
I use ACCESS --- just the (JET) database,
If --and/or When - I referred just to ACCESS, I owe an apol0gy for confusing those who look at ACCESS as being JET and RAD, rather than how I
intended this post (JET database ONLY - or in M$ terminology ACE).




:

 

[cheekybuddha]

Unless I'm WRONG, M$ forces STORED procedures in the SELECT clause to be hardcoded with table field names.
Other SQL clauses (e.g BETWEEN) can be parameratized.
If I recall correctly, -- M$ docs -- STORED Queries are compiled.
Being compiled they should be early bound, and HENCE M$ requirement that the SELECT clause be hardcoded with table field names.

IF STORED Queries are complied, if makes sense, UNSTORED queries would be late bound, AS the fields and the variables in the query are filled prior to the query being called (executed) -- those fields and values would NOT be known to the compiler at compile time.

So, you still use both terms here - Stored Procedures and Stored Queries - so I'm still not quite clear.

I *think* you are solely talking about Access Stored Queries. Is that correct?

SQL Server doesn't factor in to your setup, or does it?

 

[CJ_London]

Suspect the OP is confusing compiled with query plans

 

[cheekybuddha]

Suspect the OP is confusing compiled with query plans

Either that, or that you can't create the SELECT'ed fields dynamically in a stored query.

But, until we get further clarification about exactly what @dw85745 means, it's difficult to advise.

 

[CJ_London]

you can't create the SELECT'ed fields dynamically in a stored query.

Only by modifying the querydef

 

[The_Doc_Man]

believe VBA does Not have a compiler

Not quite correct. VBA does, indeed, have a compiler - to "virtual machine" pseudo-code that runs on (or has run on) machines from the days of the old Intel 8088 through the modern Intel 14th generation chips and AMD offerings. VBA compiled code is interpreted or perhaps a better word is "emulated", not executed, and so you have an inherent overhead for compiled VBA code. This is why some parts of code are incredibly sluggish when compared to the code produced by something like VB6 or other "true compilers." You can, indeed, get both compile-time and run-time errors out of VBA, and that is one way to recognize that there IS a compiler behind all that mess.

Where I think a lot of the disconnect is coming from is my view of ACCESS.
I look at ACCESS as being a database (JET), with a RAD attached.

Actually, that DOES clarify the confusion. You are looking at Access totally backwards from the way most of us look at it. "The wrong end of the telescope" so to speak. That also suggests that your forms and reports are essentially from a non-Access front-end. But if they are not, ... if you are actually using the Access GUI for forms and reports, you are like a salmon swimming upstream, fighting along the way.

If I recall correctly, -- M$ docs -- STORED Queries are compiled.
Being compiled they should be early bound, and HENCE M$ requirement that the SELECT clause be hardcoded with table field names.

If we are talking "standard" Access Front End, then queries that are named and pre-defined in the list of queries in the navigation pane are analyzed to develop a query plan, but that isn't compilation in the true sense of the word. Query processing is still done according to rules published about the order of execution of various clauses - e.g. FROM / JOIN comes first, then SELECT, then WHERE, etc. (Look up "Order of SQL Operations" for full details.) Vendors of SQL still have options on exactly how they implement each stage, though there are many standards for the RESULTS of query execution. I've seen the code required for cases of non-Access report and form generation. VERY tedious if you don't have that GUI-based RAD tool to help you build and align controls on forms and reports.

You HAVE to remember this important fact: Access was originally designed for small businesses with limited amounts of data. Because it allows for external data via ODBC methods, Access (the GUI) can exceed the sizing limits by working with an alternate back-end setup. But ACE and JET cannot exceed THEIR limits regardless of the front-end. Access CAN point to multiple back-ends, but doing so dynamically becomes increasingly difficult as more and more back-end data becomes relevant.

It is entirely possible, maybe even probable, that you are at a point where a different back-end will be your only solution. In which case your problem with the "switching back-end tables" would diminish to a single back-end table with a field that lets you do the specific selection via filters rather than relinking.

 

[dw85745]

cheekybudda and CJ London:

I *think* you are solely talking about Access Stored Queries. Is that correct?

SQL Server doesn't factor in to your setup, or does it?

When I stated stored / unstored procedure, I was referring to stored/unstored ACCESS queries. IMHO they are really the same. A query is a procedure. AND unless you know otherwise, ACCESS STORED queries (procedures) require table field names in the SELECT clause to be hardcoded (no variables allowed). .

At this point SQL Server is under consideration (but currently Not used) to allow me to get above the 2GB limits of JET/ACE (Access database ONLY). As stated I do Not use the RAD portion of Access. Anything you might consider ACCESS RAD, I do through other languages.

Actually, that DOES clarify the confusion. You are looking at Access totally backwards from the way most of us look at it

IF I recall correctly, going back to around 1984/5 timeframe, Access was ONLY a database. I can't recall if M$ bought PARADOX and then
dropped or merged it with their Access, which was in development at that time. The RAD, I beleve was added later -- maybe around 1990/.92. Wiki probably has all the exact details, as my memory is cloudy as to "Who's on First"..

It is entirely possible, maybe even probable, that you are at a point where a different back-end will be your only solution.

Per one of my posts -don't reecall which -- that was one of my two options I'm considering.
1) A different backend (SQL Server or some other DB),
2) Staying with ACCESS JET/ACE and copying Hist tables and Querties from my Historical DB to my Production DB, when needed.

HOPE all is clear now to everyone.
THANKS TO ALL FOR their responses and putting up with any of my description errors, sinc ethis is an ACCESS forum and as the saying goes:
"When in Rome do as the Romans do!.
Am going to consider this thread closed.

 

[The_Doc_Man]

I can't recall if M$ bought PARADOX

They did not. Access was unrelated to the Borland product other than being for the same general purpose. I actually had a copy of Borland Paradox for Windows after having upgraded it from Paradox for DOS. I cannot remember all the details but I can tell you that in the transition from DOS to Windows, Borland "screwed the pooch" and somehow made their product LESS useful. Which is why when Access 2.0 came out with an offer to give you a cheaper price to switch to Access if you had an installed copy of Paradox. I did that and never looked back. The RAD was in place with Access 2.0, it just didn't do quite as much back then - but it still did more than Paradox did.

 

[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.

 

[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.