ADO & Switching from one Database to another

[Jacinda]

I'm sure this is not a complicated thing to do, but I need to ask...

How do I set up a function (VBA or macro) to open another database, based on
information on a form.

For example from my accounting database, I would like to open my inventory
database to the order form corresponding to the invoice showing on the
current form.

Or am I reaching for the impossible?

 

[Ken Sheridan]

Jacinda:

1. In your inventory database create a Public function which accepts the
order number as an argument, and opens the orders form filtered to that order.

2. In the accounting database create a reference (Tools | References on the
VBA menu bar) to the inventory database. This will expose the function in
the inventory database.

3. In the accounting database call the function, passing the order number
from your form into it as the argument.

On the other hand, why not combine the two into one database? Its then very
simple and you can enforce referential integrity, preventing an invoice being
raised in relation to a non-existent order. Assuming the application is
split into front and back ends you could even have separate front ends for
inventory and accounting purposes if you wish, each linked to the same back
end.

Ken Sheridan
Stafford, England

 

[Jacinda]

Thank you for the info...

to answer your question- I gave a small example of what I am trying to do.
We really have 3 databases... two are very large on their own, the invoice
database is really tiny by comparison.

The major thing I will need is for the two large databases to talk to one
another... My orders database to talk to my material database based on the
product purchased.

The orders database tracks everything from customers to product history. The
material as you may have guess tracks my incoming material stock which is
vast.

So really what I will need all of this to do is: when I'm looking at a new
order, and the customer calls back with an expedite request, I would like to
see or link to my material to see if I have materials in stock to make the
part. Right now we have to go through several steps to get this info. (look
up order, look up part, open material database and look up material info) I'm
looking to automate this into one step.


-Jacinda

 

[Ken Sheridan]

Nothing you've said convinces me that this would not be better as a single
back end accessed from front ends designed for the required functionality of
each component set of tasks appropriate to different user groups. But if you
do maintain the data in separate databases, then don't think in terms of
'opening one from the other', but rather in terms of linking to and querying
the data behind each via a common interface.

By creating links in the orders database front end to the back end of the
materials database you can query its data via queries in the orders front
end. Each order record will presumably have a set of order lines in a
related table in the usual way (Northwind is a simple example), which will
detail the parts per order. This presumably provides an avenue for mapping
the order to the relevant rows in table(s) in your materials database via
joins in a query. Consequently you should be able to see the inventory
position in relation to each order by a query which can be the basis of a
form and/or report which can be opened either from a bound orders form or an
unbound dialogue form.

However, by holding the data in separate back ends you lose all means of
enforcing referential integrity, with the consequent risk of inconsistent
data, against which you can safeguard if the data is all held in one back
end. What may seem 'vast' to you might not be so to Access, but if you are
really dealing with data on a scale inappropriate to Access you might be
better considering moving to something like SQL Server with Access as the
interface.

Ken Sheridan
Stafford, England

Thank you for the info...

to answer your question- I gave a small example of what I am trying to do.
We really have 3 databases... two are very large on their own, the invoice
database is really tiny by comparison.

The major thing I will need is for the two large databases to talk to one
another... My orders database to talk to my material database based on the
product purchased.

The orders database tracks everything from customers to product history. The
material as you may have guess tracks my incoming material stock which is
vast.

So really what I will need all of this to do is: when I'm looking at a new
order, and the customer calls back with an expedite request, I would like to
see or link to my material to see if I have materials in stock to make the
part. Right now we have to go through several steps to get this info. (look
up order, look up part, open material database and look up material info) I'm
looking to automate this into one step.


-Jacinda

 

[Jacinda]

You are probably right. I have come in after the two main databases have been
set up, so I guess my thinking is as though I need to keep those two in tact
instead of pooling all of the data into one master database.

We ran into performance problems with linked tables in the past (that could
be our inexperience) and also I had to archive some data in order to improve
calculation performance on the forms. Perhaps setting this up as a front
end/back end enviornment will improve our accessabilitiy and performance.


Thank you.
-Jacinda