 | |
#71
 
| |||
| |||
|
Re: Library Database -
03-09-2011
, 03:28 PM
|
Marshall Barton <marshbarton (AT) wowway (DOT) com> wrote in news:qtoan69hvumitau5fdhrk0eltr3ubq2c6d (AT) 4ax (DOT) com: I've been trying to follow this thread and was pondering why David's and your results were different. This summary explains it all in a clear and concise way and I appreciate having it all in one place. But all the instructions were there in my posts. I didn't expect the restriction to MDE, but did mention that I could only get it to work with an MDE, so the problem was caused by the fact that somehow that bit of information got lost somewhere along the line. |
Yes, David, they were. Because I was also trying to figure
out Steve's difficulty, I was also speculating about why
there was a disconnect and that left me in a confused state.
If I had had the inclination and time to recreate the
situation, I probably could have put it together instead of
just lurking and waiting for things to be sorted out.
Steve's write up of the details was what I was hoping to
see.
--
Marsh
#72
| ||||||||||||||
| ||||||||||||||
|
|
On 3/9/2011 12:40 PM, David-W-Fenton wrote: The library database does not need to be compiled. (You can rename or copy an uncompiled .Mdb to .Mde without compiling, and the Run command still works fine.) The only requirement is that the library database has a .Mde or .Accde extension. You do not specify the library name if you specify the path. Er, what? How can you call a function in a library database without specifying which library database it's in? As documented in the Access help, you can prefix the function call with a library NAME, for example Run "VBA.MsgBox()", where VBA is the library name. If you put a path in the Run command, you cannot use the library name. |
reference is present, but has the advantage of NOT breaking other
code if it's not. But with Application.Run, you get that advantage
already, since there's no reference, so there would be no benefit in
prefixing it with the library name (since there's no compilation
problem to avoid in the first place).
Quote:
|
In this case, the library name is irrelevant and works whether it matches or does not match the default root file name of the library. I cannot get this to work based on my understanding of what you're saying, so I must be completely misinterpreting it. See the above or Access help on library names, but I think you do know how a library name works to disambiguate a function call. You probably do it all the time when you declare Dim rst as DAO.RecordSet. |
specifying the library name is to do the disambiguating in your code
so that VBA doesn't have to construct the namespace/call tree on its
own (which will fail if there's a broken reference).
Quote:
|
The Run command might be most useful for infrequent calls to a separate tool (as David uses it), or for automation. However, the Run command is not verified until run time, just like Eval, so an incorrect call does not show up until after deployment. I have repeatedly said that you can get compile-time checking by wrapping the Application.Run call by wrapping it in a sub/function within your application. A wrapper function does not give you compile time checking with the Run command. |
calling via Application.Run.
Quote:
|
If you change an argument type in your library function, or give it a better function name, there will be no compile error even if the Run call is embedded in a wrapper. |
ease of distribution, and that's clearly what you're arguing for
here.
Quote:
|
The advantage of using a reference with direct calls to the library is that you get compile-time checks and auto-completion, so it is much easier for development. Adding a reference is more useful when you have frequent library calls, or you have more complex interactions between the front end forms/VBA and library forms/VBA. That is my situation, since I have significant code reuse for different applications. (I build scheduling and simulation models with database inputs and outputs). We have a philosophical difference here. To me, distribution reliability is an order of magnitude more important to me than ease of development. I'd rather spend my time writing the wrapper subs/functions than on any of the potential problems that come with worrying about references in the production environment(s). I agree that bug-free software reliability is most important. In fact, that is why I prefer to build a library of code that has been tested and improved over many projects, rather than cutting and pasting code the way you do it. |
Who said I'm cutting and pasting code and never re-using libraries?
This is a discussion about the methods I use for accessing
functionality stored in libraries!
Quote:
|
If you improve an algorithm or fix a bug, it is very hard to fix it in all your prior applications that have embedded VBA code in various front-end databases, |
incompatible with how it's used in some of your apps. Again, this is
the old DLL Hell argument, and I'd rather have multiple distinct
versions than try to make a single version that works for everything
and requires regression testing in all my apps before I could be
sure it's OK.
Quote:
|
or to use a compare tool to merge changes (you can export to text and then compare, but that is painful also). Having a library with generic code in one place improves consistency and reliability for me. |
apps. Either that, or your testing has to be substantially more
complex, since any time you change your central library, you then
have to run a full test on EVERY SINGLE APPLICATION that depends on
that library.
Quote:
|
As far as distribution, I have never had a reference problem if I install correctly. |
all be done correctly.
Quote:
|
In either case, using Run or using references, the easiest practice is to deploy the library database in the same path or a fixed relative subfolder of the front-end database. Despite David's concerns, I have never had a reference problem when the library is deployed in the same folder as the front end, starting with Access 95 over many customers. I encountered problems with it and so have avoided it ever since. It may have been that I misidentified the source of the problem. Even so, if you're depending on a reference, if the referenced library is not there, your app will break. With my approach, you can trap for that circumstance and avoid breaking anything else in the application. This is, in fact, how Access itself is working when you get a "can't find wizard" message -- it has checked to see if the wizard database is in its expected location, and if not found, it gives you an error message about it. If it's found, it uses Application.Run as I do to open the wizard database (though some of the Access wizards are run from a DLL, not from an MDE/MDA). I never install the front-end without the library. They go together, as well as the back-end databases and other files. I put all of the related application files into an InnoSetup installer. Why would the library be missing, versus any other part of your application? |
deleting things they don't think are important. Or the installation
fails in some way and the user doesn't report it to you, or tries to
use the app, anyway. Or a file gets corrupted. Or the file system
gets damaged.
There are lots of things that can happen that you can't control.
Quote:
|
If the user start randomly deleting application files (front end or library or data or whatever), then yes, the application no longer works, and he needs to reinstall. |
notification that it's not installed and they can't use the
requested feature. They can continue working on other things. With
your setup, they're dead in the water and can't do anything at all.
Quote:
|
Perhaps the wizards need this kind of thing, because Office might be installed incompletely, or some of the files are skipped. Office is notorious for its install/uninstall problems. I always install the library with the front-end database. If I forget to include it in the install package, well yes, that would be a major bug on my part, just like forgetting the front-end. |
wonder how broad your experience is with different apps installed in
different environments, because I don't consider mine very broad,
but I've seen all sorts of issues over the years that make me very
cautious in regard to avoiding any references other than the default
three for Access (Access, VBA, DAO).
Quote:
|
There is one subtle issue with using a reference. If a library form has the focus, its code and ribbons and menus do not "see" the front end and you cannot call a front-end function directly, since a circular reference would be needed. You can still use Eval or Run from a library function or ribbon and it sees the front-end code. Perhaps that is the cause of the bad reputation of library references. For me, the big advantage of Application.Run as I use it is that whatever you call runs in the context of the calling database, so everything within the calling database is available. Perhaps that's also the case with forms opened from a referenced database when not using automation? Whether or not you are using the Run command, inside your library function you cannot call a front-end function directly. |
be an issue.
Quote:
|
That's what I am talking about. You must use Eval or Run or something like that. This is true whether or not you have a reference. |
libraries is not nearly as complex nor extensive as yours. I use
them for add-in functionality, not for maintaining a common code
base.
Quote:
|
Here is another interesting example. Open a library form. While the library form has the focus, create a new query from the Access ribbon. When you add a table, the list comes from the library database. |
something I'd likely do, so my users won't have that problem. Again,
to me, you are putting far, far too much in a library and
introducing interdependencies between all your different
applications. To me, that's a far worse set of problems than the one
you're attempting to solve.
But if you only have one or two applications to support, and they
are all variations on the same application, it would make a lot of
sense. On the other hand, if like me, you have dozens of distinct
applications of different types that are wholly unrelated, this is a
different story entirely. I'd rather keep these apps as independent
as possible, and will update code that occurs in more than one place
only as needed ("if it ain't broke, don't fix it").
In short, your approach seems like a premature optimization if there
are is more than one distinct application using the library. It
introduces way more uncertainty in regard to the effects of changes
in the shared codebase than I'm willing to risk.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#73
| ||||
| ||||
|
|
On 3/9/2011 12:43 PM, David-W-Fenton wrote: If the omission of the extension bothers you, you should check into using an external database in the FROM clause of a SQL statement, where you can refer to a table in that database the same way I call a function/sub in a library, i.e., omitting the extension. |
Quote:
|
Omitting the extension fails in some cases. So when I refer to an external database in SQL, I always include the file extension. For example, with this query-wide syntax, the extension works: Select * From MyTable in 'D:\MyPath\MyDatabase.Mdb' Omitting the extension fails with "File not found": Select * From MyTable in 'D:\MyPath\MyDatabase' |
syntax:
SELECT *
FROM [c:\Data\MyDatabase.mdb].MyTable
....where the extension is required, so far as I know.
Quote:
|
For the table-specific syntax, Access SQL is more forgiving: Both of the following work in Access 2007: Select * From [D:\MyPath\MyDatabase.Mdb].MyTable Select * From [D:\MyPath\MyDatabase].MyTable |
were using this syntax, but I hardly ever use it, myself.
Quote:
|
I prefer to always include the extension, because (1) it works in all cases; (2) I am sure to get the file I want (there might be an mdb or mde with the same root name); (3) the extension is unambiguous when reading the SQL; and (4) adding the extension is more efficient because Access does not need to search the disk for a match. |
analogous to the syntax for Application.Run was a mistake on my part
-- it's clearly not the same at all.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#74
| |||
| |||
|
|
Marshall Barton <marshbarton (AT) wowway (DOT) com> wrote in news:qtoan69hvumitau5fdhrk0eltr3ubq2c6d (AT) 4ax (DOT) com: I've been trying to follow this thread and was pondering why David's and your results were different. This summary explains it all in a clear and concise way and I appreciate having it all in one place. But all the instructions were there in my posts. I didn't expect the restriction to MDE, but did mention that I could only get it to work with an MDE, so the problem was caused by the fact that somehow that bit of information got lost somewhere along the line. |
..Mde aspect but that was well into the thread and it expressed
uncertainty. It was not in your earlier discussion about the Run
command. In fact, in response to my questioning the lack of an
extension, you wrote:
"I don't know what would happen if you had both an MDB and
an MDE at the specified location, but that wasn't the case when I
tested -- I only had either MDB or MDE."
That would indicate that you had it working with an MDB at some point,
but now we know better.
Only later, you said:
"It may have to be an MDE -- I'm not certain on that. The only
cases in which I use this are where it's an MDE, but I thought
that didn't matter until I last wrote about this a few weeks
ago, and couldn't make it work except if the library was an
MDE."
That statement still expresses uncertainty, the way I read it.
The part about being a .MDE is actually the CRITICAL part of the Run
command with path that makes it work. That was not clear to me.
It is still very surprising to me that Microsoft would hard-code a valid
list of extensions and not include .Mdb. Moreover, the .Mde extension
works fine even if the library is not compiled (just rename the
extension from .Mdb to .Mde).
This is the first time I can remember that something could be done with
..Mde files that cannot be done with .Mdb files. Usually it's the other
way around.
Steve
#75
| |||
| |||
|
|
It is still very surprising to me that Microsoft would hard-code a valid list of extensions and not include .Mdb. Moreover, the .Mde extension works fine even if the library is not compiled (just rename the extension from .Mdb to .Mde). |
It is even more bizarre.
Suppose I have a MyDatabase.mdb with a reference to General.mdb.
It is possible to call a Sub in General.mdb that has an
Application.Run to MyDatabase.
This works provided that – as you indicated – MyDatabase.mdb is
renamed to MyDatabase.mde.
Is here a possibility towards circular references?
This renamed MyDatabase.mde has only a different icon, but all the
other properties seem just as if it was a .mdb.
Still wondering why such a “powerfull” feature is still undocumented.
Imb.
#76
| |||
| |||
|
|
It is still very surprising to me that Microsoft would hard-code a valid list of extensions and not include .Mdb. Moreover, the .Mde extension works fine even if the library is not compiled (just rename the extension from .Mdb to .Mde). Hi Steve, It is even more bizarre. Suppose I have a MyDatabase.mdb with a reference to General.mdb. It is possible to call a Sub in General.mdb that has an Application.Run to MyDatabase. This works provided that – as you indicated – MyDatabase.mdb is renamed to MyDatabase.mde. Is here a possibility towards circular references? This renamed MyDatabase.mde has only a different icon, but all the other properties seem just as if it was a .mdb. Still wondering why such a “powerfull” feature is still undocumented. Imb. |
true with a normal referenced library, in which case you do not need to
add any path.
For example, if MyDatabase.Mdb has a reference to General.Mdb, then code
in MyDatabase.Mdb can call a function in General.Mdb, which then uses
Eval or Run to call another function in MyDatabase.Mdb. No path is
needed, and all functions in both the front-end and library are
available for Access expression evaluation using Eval or Run.
I agree that it is surprising the path format is undocumented for the
Run command, and that the path cannot use .Mdb. Perhaps the path format
was a bit of a kluge used only for calling the Access wizards.
Steve
#77
| |||
| |||
|
|
Sky <S... (AT) NoSpam (DOT) com> wrote innews:il8hk4$j0m$1 (AT) news (DOT) eternal-september.org: On 3/9/2011 12:43 PM, David-W-Fenton wrote: If the omission of the extension bothers you, you should check into using an external database in the FROM clause of a SQL statement, where you can refer to a table in that database the same way I call a function/sub in a library, i.e., omitting the extension. I was wrong when I said this. Omitting the extension fails in some cases. So when I refer to an external database in SQL, I always include the file extension. For example, with this query-wide syntax, the extension works: * *Select * From MyTable in 'D:\MyPath\MyDatabase.Mdb' Omitting the extension fails with "File not found": * *Select * From MyTable in 'D:\MyPath\MyDatabase' I was wrong when I asserted this. I was actually thinking of this syntax: * SELECT * * FROM [c:\Data\MyDatabase.mdb].MyTable ...where the extension is required, so far as I know. For the table-specific syntax, Access SQL is more forgiving: Both of the following work in Access 2007: * *Select * From [D:\MyPath\MyDatabase.Mdb].MyTable * *Select * From [D:\MyPath\MyDatabase].MyTable OK, so I wasn't entirely off-track. I would never leave it out, if I were using this syntax, but I hardly ever use it, myself. I prefer to always include the extension, because (1) it works in all cases; (2) I am sure to get the file I want (there might be an mdb or mde with the same root name); (3) the extension is unambiguous when reading the SQL; and (4) adding the extension is more efficient because Access does not need to search the disk for a match. I would use the extension, too. But, again, my citation of this as analogous to the syntax for Application.Run was a mistake on my part -- it's clearly not the same at all. -- David W. Fenton * * * * * * * * *http://www.dfenton.com/ contact via website only * *http://www.dfenton.com/DFA/ |
When Application.Run is used to an external database, a ldb-file is
created (or maintained) of this database. So, in one way or the other,
some kind of reference is made. This reference stays intact until the
calling database is closed.
I can imagine that the external database from then on acts the same as
any front end. How do you handle the multi-user aspects? Does every
user has its own external database, as he has his own front end of the
calling database?
Imb.
#78
| |||
| |||
|
|
When Application.Run is used to an external database, a ldb-file is created (or maintained) of this database. So, in one way or the other, some kind of reference is made. This reference stays intact until the calling database is closed. |
1. doesn't break if things are not in the right place (because the
reference is not created until the external app is called with
Application.Run).
2. doesn't have to be fixed up/altered when things move around.
3. doesn't have any effect on the front end's compilation state
(i.e., the library can be MDB/MDE/ACCDB/ACCDE and it doesn't matter
what the front end is, as opposed to them needing to all be the
same, i.e., compiled or uncompiled, if you use a reference).
Quote:
|
I can imagine that the external database from then on acts the same as any front end. How do you handle the multi-user aspects? Does every user has its own external database, as he has his own front end of the calling database? |
application, so it goes along with the front end. I see no problem
here. The first time I ever used an external code library (c. 1998,
A97), I tried using a shared library on the server, but that didn't
work, so I moved it to the front end folder (and that didn't always
work, either, though it was set up according to all the
documentation I had at the time).
So, basically, there are no multi-user aspects to my libraries, any
more than there are multi-user aspects to the use of the front ends.
I thought that was self-evident (I kept saying I stored the library
in the same folder as the front end so that finding the path to it
was possible in all cases).
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.