Splitting Access Database 1

[Valeriya]

Hi, Guys! I'm working on splitting an Access2000 DB. Have a few questions.

1. I've got several intermediate make table queries that I do not want to be a part of the Front End copies. Is it possible to include them in the Back End? Basically, when the users will run their Macros or Reports they will be pulling info from these intermediate queries and tables, but I don't want them to have physical access to these queries.(Most sources say that the BE is stricktly for Data tables). The reason I want it to be done that way is that in case I need to make any changes to the the Queries I will be able to make just one change instead of going into every FE copy).

2. Each Front End copy will be 1 of the 4 different regions(West, East, etc). Each region will have different Macros, Reports, etc. How would I go about creating individual copies. Can you suggest a website with a detailed explination. No of the ones that I looked go in the details of how splitting is actually implemented. just general info.

Thank you very much!!!

Valeriya

 

[alvechurchdata]

You can certainly put Queries in the back-end or you could put them into a third database.

What problem are you having in making four copies of the front-end? We keep a master front-end on the server and copy it to the users' PCs. Some departments need to have their own queries so these ones copy the master to the manager's PC, add the queries and then copy it around the department.

I've got a bit more on

http://www.alvechurchdata.co.uk/accsplit.htm

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[jsteph]

Valeriya,
You could store these queries on the BE, then for Action Queries you can .Execute them via code by opening the remote database object:
dim dbrem as database
set dbrem = dbengine.opendatabase("\\path to remotedb.mdb")
dbrem.querydefs("some remote action query").execute


For Select queries you can use the remote db's querydef.sql text and copy that to a local select query or us it as a recordsource, or whatever.
--Jim

 

[KornGeek]

Just to add my 2 cents,

I would do this by using a single front-end, with some sort of configuration option that tells it which regional interface to use. This is more work to set up initially, but I believe that you will more than save the effort later when you're doing updates.

Under this model, you could put the queries, macros, etc. in the front-end and only modify them once also.

One of the reasons I am against putting them in the back-end is that you might want these to change later, and changing a back-end that is already in use can be difficult. (You have to schedule time so that nobody else will be in it when you make the changes.)

I am also against putting these in several different front-ends, because (as you mentioned) you would then have to make the same changes in multiple places.

Finally, I am against distributing updated front-ends and having the users add their queries, forms, etc. because this places an undo burden on your users if you make several updates.

I believe a configurable front-end is the best way to go.

 

[Valeriya]

Thank you, Guys!
Especially to KornGeek!
But, I guess the concept is still not clear to me.
Since I've never use a VBA, the simpliest method that I'll choose,the better. However,

1.what if I have couple of tables that my users update everyday manually. Do I have to include them in the Back End?
2. How can I make these tables integrated? How would I go about making an update for them?

Sorry for stupid questions
Thanks a lot!
Valeriya