Frontend/Backend DB Madness - help!

H

Hafeez Esmail

Old Status:
I have one database containing two tables ("tblCurrent"
and "tblPrevious"), some queries (including one Make-table
query) an AutoExec macro and some code.
The AutoExec macro is setup to run one query, some code,
and then close the database.
The first thing that's done is "tblCurrent" is updated
using the Make-Table query.
Then, some code (that makes comparions between the two
tables) is executed but before the database is
closed, "tblPrevious" is deleted and a copy
of "tblCurrent" is made as saved as "tblPrevious" (so the
next time around the 'current' will be updated and
the 'previous' will be the previous).

New Status
I kept this database as a 'backend'(BE) and have a
few 'frontend'(FE) databases (with linked tables)
accessing it.


Question:
Right now, from the FE DB, I'm deleting the BE tblCurrent
and then running the BE Make-Table Query. This seems to
be working for tblCurrent but is there a flaw in this way
or a better way?

How do I update my tblPrevious?

A separate issue:
From a FE DB, how do I delete all the records in a table
in a BE DB?
 
T

Tim Ferguson

"tblPrevious" is deleted and a copy
of "tblCurrent" is made as saved as "tblPrevious" (so the
next time around the 'current' will be updated and
the 'previous' will be the previous).

I have to ask why? This is at best a really hacky way of backing up a table
(and it is better just to do a proper file copy of the mdb) and at worst
some terrible misunderstanding of how relational databases work.
This seems to
be working for tblCurrent but is there a flaw in this way
or a better way?

I cannot guess what you are trying to achieve, but whatever it is I would
guess there is a better way. Deleting and renaming tables just is not part
of the relational model anywhere.
A separate issue:
From a FE DB, how do I delete all the records in a table
in a BE DB?
If you have a Tabledef link to it,

"DELETE FROM MyTable"

otherwise, you should be able to do it like

"DELETE FROM MyTable IN ""N:\otherfolder\backend.mdb"";"


Hope that helps


Tim F
 
T

Tim Ferguson

My database serves two purposes: One, to email employees
when any updates have been made to their system, and two,
to allow any manager to create a query (using a form).

I need to say that I have a religious objection to using email and database
in the same sentence (never mind in the same app!) but we'll carry on :)
The first purpose is done automatically. The database
is opened (via a link in the startup of the secretary's
computer), the autoexec macro updates the table,

I have no idea what this procedure involves, and I suppose I don't really
need to -- but in there presumably I would guess there are much better ways
of doing things that all this deleting and creating of tables. At a pinch,
how about putting in a field for when each record was last updated, or
having a separate record for each update? Still, I don't know what you are
doing and if your current solution works, then you needn't take any
criticism from me!
The second purpose (which uses the tblCurrent), allows
any of the managers to create a query (which is exported
as an excel file on their desktop) containing all of the
current data of any employee they choose.

If you really mean they are creating a query, then it might be just as
simple to teach them how to use MS Query. If you mean that they are putting
some parameters into an existing query, then it is easy enough to design a
form and a little bit of VBA to do that and export the XLS.
Alot of the managers are uncomfortable (and forgetful) of
using the shift button to open up a file (to disable the
autoexec macro)

Quite right: you should be providing a UI (user interface) to take care of
all of that.
It was suggested that an FE/BE approach would be
suitable. I'm always open to suggestions.
Always true. Actually, it should really be a FEs /BE approach, since there
is no reason why you should be limited to one Front End. You can have one
to manage the unattended update, one for the data entry clerks, one for the
managers (this one has no updatable forms, only a rich selection of queries
and reports) and so on. Come to think of it, why bother with Access at all
for the Excel stuff?-- the managers can open Excel, press a toolbar button
that says "Get Personnel Data" and let VBA/ Excel do the rest. While we are
about it, the unattended update could probably be done in VBS without
opening the whole Access application in order to run a little bit of DAO
code.

One of the best things about MS Office is the ubiquity of VBA/ Jet in all
the applications, and the fact that you can make anything truly database
driven. I have a Word template that happily looks up addresses from a Jet
database. You are paying for this flexibility of VBA in terms of the huge
security holes it creates, so you may as well take advantage of it too.

Best wishes


Tim F
 
H

Hafeez Esmail

Thanks for the insight Tim

I was oblivious to the VBA world (until about 3 months
whenI first got a project with Access). I wasn't aware of
how easily things can be done using some code.
I already have solutions to the two purposes I've
stated. The only problem was in updating the tables.
I took your advice about not deleting any tables (things
get complicated and messy) so I decided to update my
tables using the appropriate action queries.
Everything works wonderfully now. Thanks for your help!

I was just wondering, why are you so objected to emailing
(internally) a database object?

Hafeez Esmail
 
T

Tim Ferguson

I was just wondering, why are you so objected to emailing
(internally) a database object?

It's actually the other way round: databasing email addresses. Well-meaning
spam is still spam in my book... That is why I just do not even read any of
the questions here around, "how do I get Access to send an email to
everyone in my table..?" Just because Outlook is programmable, it doesn't
mean it's a good idea to do so -- and there are a lot of broken mail
servers out there to prove the point.

Sorry: end of rant :)


B Wishes


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top