Procedural Question - Back End forms

[Guest]

I have 6 front-end databases that share the same back-end database (tables).
Each front end has several unique forms that cut the data in special/unique
ways. They all, however, share a couple common forms. Are there any
"gotchas" if I move the common forms to the back end and link them to each
front end databases? The logistics adding a new common form or of keeping
the current common forms synchronized can be a hassle (well at least time
consuming and prone to typos). using Access 2000, Windows NT.

For example, each database has a unique profile form, but all evetually
require an address. When the postal code is entered, a back end table is
searched and the City, State are:
1. automatically filled in if the postal code is in the table.
2. A form with all cities within that postal code is displayed and the user
can pick the city they want (or can add a new one)
3. If the postal code is not listed, a form is displayed for
city/state/remarks entry and the postal code table updated.
The postal code maintenance, add new and city select are forms that are
shared by all.

Thanks for taking the time to read & appreciate any insight.
Jael

 

[Guest]

Bummer! - can't link forms, just tables. I suspect I can do the forms on a
common db and import them as I update them. I was hpoing for something a bit
more automated.
Oh Well...
Jael

 

[Rick Brandt]

I have 6 front-end databases that share the same back-end database
(tables). Each front end has several unique forms that cut the data
in special/unique ways. They all, however, share a couple common
forms. Are there any "gotchas" if I move the common forms to the
back end and link them to each front end databases? The logistics
adding a new common form or of keeping the current common forms
synchronized can be a hassle (well at least time consuming and prone
to typos). using Access 2000, Windows NT.

There is no such thing as linking to a form, only tables.

I suppose you could have code in the startup code of each front end that
deleted its local copies of those forms and then re-imported them from the
back end. Sounds a little problematic to me though.

 

[Guest]

Jael,

Maybe you can create a replica of you database? Via this way you can synch
changes made in the master db to any replication db.

 

[Guest]

Thanks, Rick - realized that shortly after I posted. Deleting and importing
them was the first way i thought of but...Well I may try it just for the
experience.
Thanks, Jael

 

[Marshall Barton]

I have 6 front-end databases that share the same back-end database (tables).
Each front end has several unique forms that cut the data in special/unique
ways. They all, however, share a couple common forms. Are there any
"gotchas" if I move the common forms to the back end and link them to each
front end databases? The logistics adding a new common form or of keeping
the current common forms synchronized can be a hassle (well at least time
consuming and prone to typos). using Access 2000, Windows NT.

For example, each database has a unique profile form, but all evetually
require an address. When the postal code is entered, a back end table is
searched and the City, State are:
1. automatically filled in if the postal code is in the table.
2. A form with all cities within that postal code is displayed and the user
can pick the city they want (or can add a new one)
3. If the postal code is not listed, a form is displayed for
city/state/remarks entry and the postal code table updated.
The postal code maintenance, add new and city select are forms that are
shared by all.

Don't put those forms in the back end (for the same reason
you originally split the database).

You can create a library mdb/mde file with the common forms
and reference it from all the frontends. There are some
tricks to doing this, the first one you need to deal with is
opening the forms. This is done by adding a Public
procedure to the library database that takes care of it.

It's probably not this simple but the general idea is:

Public Sub MyOpenForm(frmname As String, . . .
DoCmd.OpenForm frmname, . . .
End Sub

The library mdb can link to the same tables as the frontends
if that's appropriate, but there may be other issues with
record/row source queries.

I don't think there is a way to use a library form as a
subform in a front end form.

 

[Guest]

Thank you, Marsh.
A library is an intriguing approach. I'm not well enough versed in Access
to completely follow ithis yet, but it's a direction I'll explore.

I assume that a call to a library function would have some construct for
passing information to and from the library.

Will post if I arrive at a workable solution.

Jael

 

[Guest]

if that's appropriate, but there may be other issues with

record/row source queries.

the library database is CodeDB instead of CurrentDB.

all queries and .Execute SQL in the library refer to
tables/queries/links in the library database.

DLookup and DoCmd are Application methods, and
refer to CurrentDB. You may need to use alternatives
to refer to CodeDB.