how-one main menu to access two databases

[Guest]

i want the fact that i'm using two databases to be transparent to the user
(if possible).

i would like to have two buttons on a main menu.

the 1st would access database 1 which contains data on "abc".

the 2nd would access database 2 which contains data on "xyz".

i can't just import the tables, queries, reports & macros because database 2
is a copy of database 1 (so all the tables, queries, etc. are the same
content and name). the only difference is on database 2 i manually changed
the label names on the forms and reports (on everything that's visable to a
user)

for example the 'daily activity' form in datebase 1 shows "product abc" at
the top with field labels 'a' & 'b' & 'c' while database 2 shows "product
xyz" at the top with fields labels 'x' & 'y' & 'z'.

thanks

 

[strive4peace]

Hi Betty,

separating your data like this is not a good idea -- you should combine
everything into one place and have a "company" field in one of your
higher tables in the heirarchy to determine which company the data
belongs to

as for labels on reports, the changes can be handled through code

I will be happy to send you a 30 page Word document on "Access Basics"
-- send me an email and request it in the subject line

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

well let me explain whay i was trying to do.

i created the database for one product. then i copied and pasted with an
identifer each table, query, form, report and macro. i.e. table 'p1 abc'
copied and pasted as 'p2 abc' (p1=product 1, p2=product2). them i manually
changed the source for each query, form, report, and macro. a lot of work.
which i would perfer not to repeat. (i made a copy of the database and tried
renaming tables and queries but the changes did not carry through to queries,
forms, etc.) the database looks and works great. but i need to expand it to
two more products. i don't want the user to select or input the product
he/she is entering data for. when he/she clicks the p1 button on the menu
the p1 form opens so he/she can only see or enter p1 data.

i there a way to have queries, forms, and etc. update to the renaming of the
tables, queries, etc. so that i don't have to manually update the source for
the effected queries, form, etc.? is there some trick to get around the
program's limitations??

thanks

 

[strive4peace]

Hi Betty,

it is not a limitation of Access at all ... it is lack of your
knowledge to do it... but not to worry, we are here to help you

create a default table with the userID and the ProductID you want to
limit them to. Put all like structures in the same table along with
another field (if necessary) to identify which data set it is -- you can
set your queries, forms, and reports up to limit the data for their
default product.

To help put things into perspective for you, email me and I will send
you my 30 page Word document on Access Basics for Programming (it
doesn't really cover code, just foundation knowledge you need to start
coding) -- be sure to put what you want in the subject line

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal

i sent you an email requesting the "access basics" doc.

"create a default table with the userID and the ProductID you want to
limit them to" -- i don't need to restrict users to a particular product(s).

"you can set your queries, forms, and reports up to limit the data for their
default product." --- well maybe after i read your document i'll know
how. ---

i guess i can have form "a" that can be used to enter new product "a"
records. and i can have an id control on the form with a default value ("a")
so a user would not have to enter or select which product the data is for.
and i could have another form, form "b", to do the same for product "b".

BUT --- HOW are each of those forms restricted to display only their
respective product while maintaining the ability to enter new records only
for their respective product and while their source is the same query/table??
filter in the form?? criteria in the query??

if you wouldn't mind, could you briefly explain??

thank you for your help.

 

[strive4peace]

Hi Betty,

Just sent you Access Basics for Programming -- also sent you the first 3
chapters of a book I am writing on Programming with VBA.

I would be happy to explain in more detail. First, I need to see the
data structure and table relationships you are using.

In the Access Basics document, there is a section on using the
relationship diagram to document your database. It explains how the
parent tables should be to the left of the children tables so data flows
from left to right as well as other information to make the relationship
diagram truly useful while you are developing your database.

Once you read that, make a JPG file of your relationship diagram laid
out according to the guidelines and email it to me.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*