Can this be done with VBA?

[rocco]

Hello,
I have built an application that has been deployed to customers via the
Package Wizard of MS Access Developer Extension. The database is an .mde
format at it uses a customized .mdw.
From time to time customers ask for some new features and I need to delivery
a total new database which will be filled with the data back upped from the
older one.
For the future I would use a different approach, but before to jump in doing
work that will end up in wasted time, I would like to ask you if all what I
post below can be done in VBA:
1) build an .mde file (A) with just the new features/changes and capable to
log in the already delivered .mde file (B) through my account (which grants
all possible permission);
2) from (A), create a new blank database (C), log into it and close (A)
[have to figured out how…]
3) from C import all from A and the new features from B
4) kill (A) and rename (B) for backup [if possible also archiving it in a
different folder]
5) create an .mde file(D) from C
6) log in to D close and kill C

Can all I listed above be done via VBA?
Is there a better approach? (I’m sure there is…)

Thanks!
Rocco

 

[Douglas J. Steele]

It sounds as though you've got a monolithic database: that your application
and data are both contained in the same MDE. That's not really how it should
be: you should split the application into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relationships). Only the back-end should be on
the server: each user should have his/her own copy of the front-end,
preferably on his/her hard drive (and only the front-end need be an MDE, as
creating an MDE does nothing for tables and relationships).

Given that, all you'd have to do is distribute a new front-end MDE when
you've made changes.

In answer to your specific question, be aware that for steps 1 through 3, A
MUST be an MDB: it's not possible to do what you're describing in step 3 if
A is an MDE.

Take a look at MichKa's TSI SOON (Shut One, Open New) database add-in at
http://www.trigeminal.com/utility.asp?ItemID=8#8: it should help you with
steps 2 and 6.

There's an undocumented feature that allows you to make an MDE
programmatically (although be aware that since it's undocumented, it could
always disappear at any time):

Dim a As Access.Application
Set a = New Access.Application
a.SysCmd 603, "db2.mdb", "db2.mde"
Set a = Nothing

To copy objects from one database to another, see help on the
TransferDatabase method.

There are built-in file manipulation capabilities in VBA, provided you're
not trying to use them on the currently-running MDB or MDE file: look at
Kill, Name and FileCopy for help.

As you've hopefully gathered by now, splitting the application properly is
definitely the simpler approach!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,
I have built an application that has been deployed to customers via the
Package Wizard of MS Access Developer Extension. The database is an .mde
format at it uses a customized .mdw.
From time to time customers ask for some new features and I need to
delivery
a total new database which will be filled with the data back upped from
the
older one.
For the future I would use a different approach, but before to jump in
doing
work that will end up in wasted time, I would like to ask you if all what
I
post below can be done in VBA:
1) build an .mde file (A) with just the new features/changes and capable
to
log in the already delivered .mde file (B) through my account (which
grants
all possible permission);
2) from (A), create a new blank database (C), log into it and close (A)
[have to figured out how.]
3) from C import all from A and the new features from B
4) kill (A) and rename (B) for backup [if possible also archiving it in a
different folder]
5) create an .mde file(D) from C
6) log in to D close and kill C

Can all I listed above be done via VBA?
Is there a better approach? (I'm sure there is.)

Thanks!
Rocco

 

[tina]

3) from C import all from A and the new features from B

the only objects you can import *from* an MDE file are tables and queries.
so if your enhancements include changes to forms, reports, macros, and/or
VBA modules, this approach won't work for you.

i'm guessing that your deployed database is *not* split into
frontend/backend files. now you're seeing one of the reasons that it should
be - when all the tables reside in a BE database, and all other objects in a
FE database, it's easy to make changes to the FE and simply send a
replacement to the user.

when the tables are in the same file as the other objects, the alternative
is to send a new database file with empty tables, then link the tables from
the "live" database into the new one, and append all the data from the
linked tables into the native tables in the new database. you can do this
with VBA code, but you need to a) know the filepath of the user's current
database, or b) include code in the "transfer" process to allow the user to
provide the correct filepath to their current database. you also need to
take care to append data in the correct "hierarchy"; that is, parent tables
first, then child tables.

hth

 

[rocco]

THANK YOU !!!!

It sounds as though you've got a monolithic database: that your application
and data are both contained in the same MDE. That's not really how it should
be: you should split the application into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relationships). Only the back-end should be on
the server: each user should have his/her own copy of the front-end,
preferably on his/her hard drive (and only the front-end need be an MDE, as
creating an MDE does nothing for tables and relationships).

Given that, all you'd have to do is distribute a new front-end MDE when
you've made changes.

In answer to your specific question, be aware that for steps 1 through 3, A
MUST be an MDB: it's not possible to do what you're describing in step 3 if
A is an MDE.

Take a look at MichKa's TSI SOON (Shut One, Open New) database add-in at
http://www.trigeminal.com/utility.asp?ItemID=8#8: it should help you with
steps 2 and 6.

There's an undocumented feature that allows you to make an MDE
programmatically (although be aware that since it's undocumented, it could
always disappear at any time):

Dim a As Access.Application
Set a = New Access.Application
a.SysCmd 603, "db2.mdb", "db2.mde"
Set a = Nothing

To copy objects from one database to another, see help on the
TransferDatabase method.

There are built-in file manipulation capabilities in VBA, provided you're
not trying to use them on the currently-running MDB or MDE file: look at
Kill, Name and FileCopy for help.

As you've hopefully gathered by now, splitting the application properly is
definitely the simpler approach!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,
I have built an application that has been deployed to customers via the
Package Wizard of MS Access Developer Extension. The database is an .mde
format at it uses a customized .mdw.
From time to time customers ask for some new features and I need to
delivery
a total new database which will be filled with the data back upped from
the
older one.
For the future I would use a different approach, but before to jump in
doing
work that will end up in wasted time, I would like to ask you if all what
I
post below can be done in VBA:
1) build an .mde file (A) with just the new features/changes and capable
to
log in the already delivered .mde file (B) through my account (which
grants
all possible permission);
2) from (A), create a new blank database (C), log into it and close (A)
[have to figured out how.]
3) from C import all from A and the new features from B
4) kill (A) and rename (B) for backup [if possible also archiving it in a
different folder]
5) create an .mde file(D) from C
6) log in to D close and kill C

Can all I listed above be done via VBA?
Is there a better approach? (I'm sure there is.)

Thanks!
Rocco