Solved DAO Workspace Database Collection

[GPGeorge]

The_Doc_Man:

So now I have a decision to make, stay with ACCESS or go with a new Db.


One Hopes for that. I always try and get things to work first, then optimize for speed.

---------------------- FWIW-----------------------
Data files originally started out as flat files.
When "Revelation" Db came out I moved to that.
"Revelation" disappeared,
DB3, I think bought it, and PARADOX came on line around the same time.
So I moved everything to PARADOX, and then ported over to ACCESS after that.
One thing I've always disliked about ACCESS -- in my situation -- is I need to build a separate query for each Symbol.
ACCESS won't allow variables in "stored" queries for whatever reason. Just remembered this issue.
Going to a new Db may solve that issue..
--------------- FWIW End ----------------------------------

Actually, I'm not sure we have enough information to respond adequately to some of these statements. Specifically, the reference to variables in queries.

What you are probably missing are parameterized queries. Many queries can be parameterized with form references, for example, as well as with TempVars and even VBA functions.

The following statement reveals a lot about the shortcomings of the current approach, unfortunately.

"All forms, reports, and anything else is handled outside of Access by Creating/Updating ACCESS tables or by executing one of the queries."

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.

 

[dw85745]

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

Will have to play with it to see if can be accessed from Code other than Access VBA..
If it can be used in stored procedure, this would greatly reduce the number of stored procedures I use.
In compiled code - objects are either early or late bound. Early bound gives a performance boost.
How ACCESS deals with stored procedure I'm Not sure.
M$ link say TempVar(s) are objects.
From my read they appear to be a global variable collection (text and numbers only)

To use them several questions arise.
1) Are stored procedures considered an object? If so, are they early bound?
2) If a TempVar(s) is used in that stored procedure does it change to latebound?

 

[NauticalGent]

@dw85745 , give this a read: https://www.devhut.net/access-tempvars/

 

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

 

[Pat Hartman]

If it can be used in stored procedure, this would greatly reduce the number of stored procedures I use.
In compiled code

I thought you weren't using SQL Server. Now I'm really confused.

Access is a RAD tool. Do not confuse it with Jet and Ace the desktop database engines that are much maligned and the cause of most complaints about Access. Access makes a great front end to pretty much any RDBMS so whether your tables are Jet/ACE or SQL Server or Oracle or DB2 or Sybase or whatever floats your boat. Access the RAD tool will work for you.

If you use an Access FE to SQL Server, you may need SPs or you may not. My apps tend to use linked tables and bound forms and saved querydefs. Sometimes I make Views to speed up joins that are frequently used I don't have many apps that do batch processing so I usually only create SPs for very complex reports. Once you use saved querydefs, you get in the habit of using arguments that reference form controls so the same query can pull data for ANY date range. This means that you don't need to create dozens of specific forms, reports, and queries.

 

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

 

[Pat Hartman]

IMHO they are really the same.

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.

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.

 

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