Frontend/Backend DB Madness - help!

  • Thread starter Thread starter Hafeez Esmail
  • Start date Start date
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?
 
"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).
Click to expand...

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?
Click to expand...

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?
Click to expand...
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
 
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).
Click to expand...

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,
Click to expand...

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.
Click to expand...

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)
Click to expand...

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.
Click to expand...
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
 
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
 
I was just wondering, why are you so objected to emailing
(internally) a database object?
Click to expand...

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
 
Back
Top