Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Sun Feb 14 03:32:31 CST 2010
Hi Max --
Thank you for your note.
In fact you can do that new db creation without intermediate usage of text
files - just use .TranferDatabase, e.g.
- having db to be "packed" opened within MS Access IDE close it, and create
new db then use .TransferDatabase to import all objects one by one using
.Containers and .Documents collections to select source objects, or a query
over MSysObjects...
The issue with that approach (and the one you propose below) is that in
general case in the new db you'll have also to recreate:
- references;
- database properties;
- user defined properties;
- relationships;
- ? something else I missed ?
So, I'd currently keep the "EatBloat" simple as it is:
- export all objects into text files;
- import all objects back from text files (handle tables' import as a
special case discussed earlier here);
- compact db;
This "lightweight" approach assumes that there is no relationships in the db
to be "packed" as well as this is a developer's/user's duty to make db
backup before running export/import to "eatbloat" (yes, it's easy to make
backup of db by code but then current db should be closed before making
backup, and when reopened within MS Access IDE it could run autoexec macro
or start-up form, yes we can temporarily block them but then we will be
changing source db anyway - let's not do that for now...)
As I noted in one of the posting in this thread, quite some time ago in 1999
I have been involved in a project called "Application Manager", which did
many "tricks" with "shuffling" MS Access objects between a set of databases,
setting references, (re)creating user defined properties, injecting
"plumbing code", packing all stuff as InstallShield/SageKey setups etc.etc.
All that can be done but it takes time ("Application Manager" took almost
three months fulltime) therefore let's (for now) follow KISS principle to
not "sink in the implementation details" - I suppose "lightweight" approach
should work rather well for most of the real life cases (as William noted),
there should be just one option added probably to make tables'
backup/restore option selectable before export/import - then "lightweight"
approach will also work with the databases with relationships.
The next step could be to make it working for db with relationships - and
there could be at least two ways to do that:
- 1. from a set of existing relationships get (a) data dropping and (b) data
importing sequence and use (a) to drop all tables' data after they are
exported into text files, and use (b) to import all data from the tables;
- 2. Save tables' data as text, save Relationships definition, drop all
relations, import data from text, recreate relations.
When that is working saving/recreation of references can be added etc...
Thank you.
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Sunday, February 14, 2010 5:26 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET - Create a
NEW DB and populate with objects.
Hi Shamil,
Just done some more coding to create a NEW DB and populate with objects from
OLD DB without having to open either of them.
Assuming you are sitting in DB1 (code below) and you want to take all the
objects from Existing DB2 and create a Brand New DB3, here is one way (code
below)
To keep the idea simply, I have opened DB1 and run
sExportRemoteObjectsAsText() which exported all the Forms from DB2. (The
actual export code would be replaced by the code you are creating/posting
now to export ALL objects. This is just a demo bit to keep it short.)
Then once the objects have been exported as textfiles, I run the code in
sImportRemoteObectsFromTextIntoNewDB() which imports the text objects into
DB3. (Again, to keep the example simple, I have just imported one object
but this would be replaced by your code to bring all objects back)
If you now look at the NEW DB3 you will see ALL the objects that are sitting
in DB2 copied into a brand new database DB3 with no bloat etc. DB2 remains
unharmed or altered in anyway.
With this code you have done all this automatically without opening either
DB2 or DB3.
Would this be a useful wrapper to your project code?
I think this would work with ADP projects too but not sure.
Max
(Watch for wrap)
CODE for DB1.
Option Compare Database
Option Explicit
Private dbsNew As DAO.Database
Private wrkDefault As Workspace
Private proj As CurrentProject
Private obj As Object
Private appAccess As New Access.Application
Sub sExportRemoteObjectsAsText()
appAccess.OpenCurrentDatabase "C:\_MCM\DB2.mdb", False
Set proj = appAccess.CurrentProject
For Each obj In proj.AllForms ' export form objects just as an example
appAccess.SaveAsText acForm, obj.Name, "C:\_MCM\Objects\" & obj.Name
& ".txt"
Next obj
appAccess.CloseCurrentDatabase ' shut down temp application
End Sub
Sub sImportRemoteObectsFromTextIntoNewDB()
Set wrkDefault = DBEngine.Workspaces(0) ' Get default Workspace.
If Dir("C:\_MCM\DB3.mdb") <> "" Then Kill "C:\_MCM\DB3.mdb" ' Make
sure there isn't already a file with the name of the new database.
Set dbsNew = wrkDefault.CreateDatabase("C:\_MCM\DB3.mdb", dbLangGeneral)
' Create a new un-encrypted database with the specified collating order.
dbsNew.Close
Set dbsNew = Nothing
appAccess.OpenCurrentDatabase "C:\_MCM\DB3.mdb", False ' set a
reference to it
appAccess.LoadFromText acForm, "Form1", "C:\_MCM\Objects\Form1.txt" '
load a sample object into it.
appAccess.CloseCurrentDatabase ' shut down temp application
End Sub
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4864 (20100213) __________
The message was checked by ESET NOD32 Antivirus.
http://www.esetnod32.ru
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4864 (20100213) __________
The message was checked by ESET NOD32 Antivirus.
http://www.esetnod32.ru
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4864 (20100213) __________
The message was checked by ESET NOD32 Antivirus.
http://www.esetnod32.ru