Linked Tables how do they work

[vandy]

Hi All,

I have a general question and want to get the groups inputs.

I have a Inventory database which calculates stock values and once the
project is completed transfers the item with remaining stock value to a
database called Rawstock_Inventory.
1) I need to be able to use this database for assigning locations to the
items and issuing stuff out etc.

2)Do I run a different database and create forms and give selective access
to users using both the database since one database is for Items in process
and the other keeps tracks of items after a project is completed.

3) Can i link the rawmaterial database and open a new database . And
everytime the transfer takes place since the table is linked should i assume
that the table is always updated.

Splitting table question:
4) I have tried splitting the tables and stored the inventory.be.mdb in my
network drive. when i copy the main file with all forms and queries on the
users desktop it does not open saying it is unable to locate the
inventory.be.mdb link file. what am i doing wrong.

I would really appreciate your inputs since i really dont know how to
proceed further.

 

[Klatuu]

Databases should always be split.

If your user's application cannot find the backend database, it is probably
because they do not have the same drive mapping you do. For example, you may
have the network folder mapped as M: and the user may have it as P:. When
you linked it, it stored the path as M:\SomeFolder\MyBackEnd.mdb, so when the
user's app tries to open the tables, they are looking form M:, so it will not
be found.

In a multi user environment, all linking should be done using UNC paths.
That is
\\ServerName\SomeFolder. That way, regardless of a user's drive mapping, it
will find it.

It is possible to link to multiple back ends. But since links are specific
to tables, that means you can select only the tables from a database you need
to use for the application.

Another method, it you don't want to have a static link to a database is to
use the OpenDatabase method. Then you can open any table in that database as
a recordset.

 

[vandy]

Hi Klatuu,

Thanks for you reply. I have a very basic question on splitting and i hope
it is worth answering.

If i have my MyBackEnd.mdb file containing only my database tables on the
network in a common path and copy the database file
containing,queries,forms,and links to the table but not the table since it is
split.

So were is the secruity. A user is able to access forms queries and go to
the network and access all the tables too. So what is being acheived here.
Also if i need to update the forms and tables should i have a working copy of
my database on my system.

thanks for your patience in reading the post I need some enlightment here!!!

 

[Klatuu]

Put a password on the backend database. Only those who know the password can
open the backend mdb directly. Now, doing so means the connect string for
the tables has to include the password.

The connect property of a table tells whether it is a local table or a
linked table and if linked it has the path and password. If it is a local
table, the connect property will be = vbNullString.

Here is an example of a connect property with a password:

1 2 3
MS Access;PWD=downwind;DATABASE=O:\Contract\ContData.mdb

1 = Type of database
2 = Password
3 = Path to the table