I am developing a system which will be delivered as a run-time product where the early months of live operation are likely to see the need to revise and add to the repertoire of text generation functions. Wherever possible I've tried to use table data ( queries ) but there are many cases where program code is required. (If Access had stored procedures then this would probably solve my problem.)

My plan was to move all these functions into a library db just containing code modules. I planned to re-issue just the library database if function logic needed to change or additional functions where needed. I realised that problems would arise if any functions used by the the main db were removed but I didn't ( perhaps foolishly !) think there would be any issues if the statements inside a function were amended.

It now appears that any changes made to the library db cause a run-time error. The runtime application certainly uses the library db - if it isn't there then it gives and error. My experiments suggest that any change to the library db will cause an error - it looks as though there is something in the compiled main db which is able to detect if the libary db isn't exactly the same as the db which was used in the compilation/assembly.

Am I right that my original assumption was wrong - you can't change a library db in a run-time application ? And if I am, can anyone suggest a solution to my problem which will allow data manipulation changes to be made without requiring the re-installation of the application ?

Thanks

Peter

If you change the library database, the main database looses it compiled state. Thus, it has to be compiled again.

You wouldn't need to reinstall, only update both the main database and the library database. They form a pair.

/gustav

Hi Gustav

Thanks for explaining what's going on. I hadn't realised that changing the library file in any way requires the db to be recompiled before it will run. With a full version of Access this just happens - with a run time .accde, re-compilation isn't possible - so I need to supply a newly compiled db.

If I understand your last point correctly, I think you suggest that replacing both the .accdr and the the library (.accde) would work - i.e all the shortcuts and registry entries ( e.g trust settings ) remain unaffected. I suppose this is reasonable as the "installation" process of a run time solution ( where runtime is already installed ) isn't much more than recreating the database with the .accdr suffix.

But to me this seems to reduce the value of library db's in run-time solutions - it seems better to include the library db module with the main db, as an additional module.

Another point that has come out of what you say and that is the approach of simply delivering a new .accdr file when program changes are made - rather than getting the user to uninstall and re-install the application.

Please come back if you disagree with any of these points.

Thanks for your help here.

Peter

Peter,

I find to bet to only run your installer package for the initial set up. I use a different method to for update. Updates are just a matter of replacing files.

I also have found the add-ins or libraries database don't seam to be useful production/end user needs. I have tried but they don[t seem to help much with making deploying updates any easier. I do use use them during my development process only. I have some developer tools as add-ins. I do not deploy them with my apps.

Hi HitechCoach

Thanks for clarifying my thinking here. In future I won't go down the route of re-packaging new releases - simply send the .accdr.

Thanks also for the link to your site - a quick look shows all sorts of interesting and useful things.

Peter

Yes. We use an install package and an update package. The last checks for the existing of the files to be updated.
We don't just pass the database files "as is".

/gustav

Gustav,

How do you build your install package - do you use an InstallShield type script ? I've never done anything like this - I've always used the Access Packaging facility. But I've noticed some limitations here - e.g if I want other files placed in certain directories. Can you point me in the right direction here ?

Another point occurred to me - would the problem you explained earlier of decompilation occuring if a library db changes apply if the library was a .dll ? i.e if I compiled the text generation functions into an ActiveX .dll using VB6 and then included a reference to this dll in my Access db. Could I then add new functions or change the code ( but not the interface - i.e the parameters / return value ) and then simply re-issue the .dll ?

Thanks for your time on this.

Peter

We use Inno Setup

If the dll doesn't need installation, I guess it would work. I haven't tried though.

/gustav

Thanks - will look up the url you give.

I tried the .dll and it fails with the same problem as the library db. My test was as follows.

I built a .dll in VB6 - it self registers on the pc where it is created. I then created an .accde and included this dll as a reference. I then altered one of the functions in the dll and re-compiled it. The new version of the .dll is implemented without problem on the .accdb ( presumably automatically re-compiled ) but the .accde fails. Presumably the Access db holds some id related to the reference so it knows if it has changed. Clever stuff.

Interestingly a run-time application built using a version of an office product as a reference will run without any issues if installed on a pc with a higher version - e.g an application built using Word 2003 runs without hitch if insalled on a pc with Word 2007 or 2010. So maybe there is some intelligence in the code which decides whether or not to flag an error. Maybe it knows that later office versions only differ from earlier versions by the addition of properties and methods - past objects will remain unchanged. But in a custom .dll it has no means of knowing.

Thanks

Peter

Peter,

Have you tried using Late Binding with your DLL?

It may be something to do with the way you created your DLL. I am able to replace DLLs with newer versions with a .mde without any issues. II do not have to recompile the MDE. I have not tried it with a .accde.

Sounds right. accde/mde files are not adoptive.

/gustav

No I've not tried late binding. How would I do this, at the moment I simply include the .accde as a reference in the main db ?
I'll try it as an .mde. The way the .dll was built may well have something to do with this issue - consistent with my point that some references ( e.g MS Word ) don't cause errors where the client pc has a later version than was used in compilation

Thanks for your further thoughts here.

Peter

Peter,

Good luck with your project.

Please keep us updated on your progress.

Here is an update...

The problems I've been having with including Access DB's as references relate to whether the database using the reference contains source code. So you can include .mdb , .mde , .accdb , .accde databases as references in an .accdb but not in an .accde or .accdr.

This is perhaps logical because what I now realise is the inclusion of a Access db as a reference "imports" the code into the project. I use this term loosely - the code appears in the project tree and you can make changes to the reference db code from within the project db ( but you cannot save these - you need to return to the reference db to make permanent code changes). As long as the code stays it seems that Access will re-compile the project if it detects that the reference db has changed. but with no source the database appears helpless - all it can do is to report a problem.

Now for the good news.

I was careless in my ActiveX DLL test. On repeating this I now find that changes to existing public function code which does not affect the interface causes no problems to an Access database that includes a reference to this .dll. This means that for existing functions no changes must be made to parameters passed to the function or values returned. But what happens inside the function can be changed. I believe you can also add new functions.

I found this reference very helpful http://www.vb-helper.com/howto_compatible_dll.html.

The dll itself can include references ( e.g DAO object module ) but not to Access controls e.g Form. This causes me some problems because if the reference is an Access db you can include functions with control parameters ( i.e frm as form ). But I guess this may be resolved by changing the function.

The key thing seems to be that you can shunt some Access program code into a compiled external component and use this as a way of making changes to the processing of a source-removed Access database.

Peter