separating application from data tables

T

traveler

I have a new application that I am developing for home use that I want to use
on a number of different sets of data. Therefore I want to separate out the
application portion from the data tables. This would allow me to switch
between sets of data tables.

Is this possible with Access?
 
L

Larry Daugherty

Hi,

Unless your sets of data are identical in both the structure and the
relationships between entities, it wouldn't be worth while. You'd have such
a detuned, generic interface that you might as well not bother.

You can easily test the concept by designing your front end with one set of
data and doing a lot of data entry and management. Then copy the app,
emptying the tables in the copy and try to use it to enter and manage the
second set of data.

Then you decide which is the right way to go based on whether you had to
make changes to the front end to make it work well with the second set of
data.

HTH
 
G

George Nicholson

Yes.

If you break your app into FrontEnd (Queries, Reports, Forms, Code) and
BackEnd (data tables). There is even a wizard to help you do this (make a
backup first!).

http://www.mvps.org/access/tables/tbl0009.htm is an example of "relinking"
code that might serve as an idea springboard for a "change backend on
demand" routine of your own. I've seen (other people's) apps set up where
you had the option of pointing to different backends depending on whether
you were working with budget or actual data or this year vs last year data.
In these cases I assume that the data structure is absolutely identical from
one BE to another unless you pick-and-chose which tables you want to relink.

....and yes, I know that there are other - possibly better - ways to deal
with budget vs. actual and this year vs. last year data, but this is a
perfectly valid approach in some situations.
 
T

traveler

Larry,
Thanks. Yes, the data sets are identical. What I have is different
years' worth of data that I want to switch between.
 
T

traveler

George,
Thanks. That sounds like what i want to do. What I have is different
years' worth of data that I want to switch between. the other reason I want
to do this is so that I can share the application with my grown children.
When I make updates to the application I want to be able to simply ship it to
them to install and use with their existing data tables.
Does that make sense?
 
L

Larry Daugherty

Hi,

It usually isn't a good idea to separate tables by year. You can achieve
the same result with a simple date field and keep all of the data in a
single table. If you were tracking automobiles would you have a table for
the red ones, another table for the blue ones and other tables for other
colors? Wouldn't it be simpler to keep them all in a single table with a
field for "Color"?

HTH
 
T

traveler

Yes, Larry, that makes sense until the database gets relly large. The other
thing I want to be able to do is update the application and send the updated
app to my grown children to use without having to have them re-enter their
data every time there;s an update. Is there a simpler way to do this?

traveler
 
L

Larry Daugherty

Yes.

Split your database into a Back End, with just the tables, and a Front End
with all the rest.

I started answering when I realized I don't really know your specific
objection. Who is keeping what data up to date and where and how?

You are unlikely to exceed the maximum size of 2 Gig for an Access database
unless you are storing images in the database - a practice that is
discouraged.

Please post back telling us how you are actually using your application:
what it does, who does what with it?

HTH
 
T

traveler

Larry,
It's a home budget manager . I wrote the original application in dBase
over 16 years ago but wanted something in a windows based app that can easily
print out reports. Based on my testing it looks like it could generate about
50 megabytes per year of data so size isn't really the issue. In my original
app switching to new tables every year made it more convenient to annualize
information for tax purposes but I can see where that won't be an issue with
Access.
The other reason I am rewriting it is that dBase code is difficult to
maintain and I have several adult children who use the program as well as a
few friends. I need some way to make fixes to the code such that I can send
them an update that won't require them to re-enter their data.
Now before you, or anyone else, tells me, "Why don't you just use
Quicken?"... I have been told that ever since Quicken was first written so
I've looked at it 5 or 6 times over this period of time. What I want to know
from my budget program is how much I have left to spend in any particular
budget category. Quicken will only tell you how much you have spent at the
end of the month. With my app I can see with a single key entry how much is
CURRENTLY in every one of my bidget categories. Besides all that, if I used
someone else's program I wouldn't have the fun of rollin' my own.

Thanks for your help!

Traveler
 
L

Larry Daugherty

Hi Traveler,

I believe I understand; it's an application that you share with others in
which you each maintain your own data. Great!

Do the splitting into front end and back end that I started to suggest
before. That covers the business of changes in design except for the case
where you're making changes that affect the tables. Then there's no way to
avoid some level of data massaging to get the old data into the new design.
When you make changes to the front end you can just ship out the changed
version after thorough testing and people can replace their old copies with
the new one. FWIW I'd convert the front end MDB to an MDB before sending
it. That will prevent someone making changes to the MDB and then being
upset when your new Front End didn't preserve their changes.

HTH
 
L

Larry Daugherty

or maybe MDB to an MDE

HTH
--
-Larry-
--

Larry Daugherty said:
Hi Traveler,

I believe I understand; it's an application that you share with others in
which you each maintain your own data. Great!

Do the splitting into front end and back end that I started to suggest
before. That covers the business of changes in design except for the case
where you're making changes that affect the tables. Then there's no way to
avoid some level of data massaging to get the old data into the new design.
When you make changes to the front end you can just ship out the changed
version after thorough testing and people can replace their old copies with
the new one. FWIW I'd convert the front end MDB to an MDB before sending
it. That will prevent someone making changes to the MDB and then being
upset when your new Front End didn't preserve their changes.

HTH
Click to expand...
 
T

traveler

Thanks Larry,
I guess I'll have to read up on the difference between an MDB and an MDE.
There's still lots to learn!

Traveler
 
L

Larry Daugherty

I do encourage you to read about the MDB/MDE differences but I'll give you a
start.

You're already using an MDB as you do your designs and get things to
working. In that process you write commented code and probably have some
modules with special or application wide code. If you go to database
Utilities you'll find a choice to Make an MDE ... You then navigate to the
folder with your MDB and select it. When the conversion is complete you'll
have a new file with the same root name and the extension MDE. All of the
human readable code and comments have been removed from the MDE version and
it cannot be modified. By using an MDE, you won't be faced by accidental
changes made by others to your code. You leave the Back End in the form of
an MDB so that it CAN be changed.

One tricky item for someone who may not like putting in error handling code:
if an unhandled error occurs in an MDE, it just quits the application.

Yes, there is a lot to learn about Access. There is a pretty steep learning
curve at the beginning and then you can get very productive with it.

If you are converting a legacy application into Access, I encourage you to
start with your design first rather than just trying to make an Access
implementation of a dbase application.

HTH
 
T

traveler

Thanks Larry, that's very helpful.

Regarding the app, it's nearly done and, yes, I did start with the DB
design. That was fairly easy since I had most of the tables ID'd from the
old app. Most of the functionality is the same between the two. I did
nearly all the processing with macros although I can see that VBA might offer
some better ways of doing some things. I suppose that learning VBA will be
my next project.

Thanks again for your assistance.

Traveler
 
L

Larry Daugherty

Hi Traveler,

You're welcome.

Yes, VBA is the way to go. While there are lots of other good reasons to
use VBA, you can't use the debugger on macros. BASIC is the core of VBA.
Then, each application has its own object model.

HTH
 
Top