Frontend database design

[soltin]

Hello,

I am in the process of developing some access databases. I have split
the database (The tables into a back end database and the code/forms
into a front end). I liked the idea of being able to modifying my
forms anytime and then when complete, being able to copy the front end
database in place when no users are online.

The problem I have is, the users want to be able to share queries and
create custom queries. What would be the best way to handle this
situation? Since the users can create queries I can't just copy my
updated form back because they may have created queries while I was
doing development and that would result in lose of their new queries.

I thought about using replication of the development front end database
to the production front end database, but I would rather not use
replication if I don't have to.

Thank you for your inputs.

 

[Tim Ferguson]

The problem I have is, the users want to be able to share queries and
create custom queries.

If they have the ability within Access, let them create a new mdb file and
link the tables from the back end, just as you probably did when creating
the original front end. There is no harm at all in having more than one
front end attached to the same tables. As long as they know enough not to
screw up the tables themselves - and if you are letting them write queries
now that should be true - then there are no new security or integrity
worries. The advantage is that they won't run the risk of messing up any
queries that are needed for your front-end forms.

Another option is using MSQuery.exe -- it's always installed with Office
although by default there is no shortcut to it.

Sharing queries: the easiest way is to open the SQL pane of the query
editor (view -> SQL), then select the whole text and paste it into an email
or something. The sharee can do the process in reverse: create a new query,
cancel the "Show a table" dialog, choose View->SQL, and paste in the text.
It doesn't matter if neither party can read the SQL itself (as long as it
doesn't get damaged) as the query will form up again in the query design
grid with view->design.

Hope that helps


Tim F