link access files

[J-Sherm-Dwyer]

I am linking two mdb files, aaa.mdb and local.mdb. I have a common field to
link the Access files. I will be working in file aaa.mdb with a link to
local.mdb. I wish to be able to update a field in the linked file local.mdb
when it is changed.

Jack Dwyer

 

[Tim Ferguson]

I am linking two mdb files, aaa.mdb and local.mdb. I have a common
field to link the Access files. I will be working in file aaa.mdb
with a link to local.mdb.

I think you are trying to create a relationship between tables in
different mdb files -- but it's not possible. Clue: you could have one
file open and delete a record that is still referenced by a record in the
other file.

Related tables have to be in the same database.

I wish to be able to update a field in the
linked file local.mdb when it is changed.

Well, this does not seem to make sense: you only ever make one change in
one table, and there is never any reason to make the same change
somewhere else. What about updating aaa.mdb when you edit local.mdb?

If there is something else going on, it's quite easy to run a little bit
of code that opens another database, executes a SQL command, and then
closes it again. But is that what you really want^W need?

HTH


Tim F

 

[J-Sherm-Dwyer]

Tim,

I could be accused of holding out on information. I have an application
designed for a single user and now the need is to have multiple people use
it. I setup a table that holds the order Id, so different forms or reports
can be processed without re-identifying the Order Id each time.

I am changing the application to move the aaa.mdb application from a client
PC to a server. However with multiple people going against different Order
Id's on the server, my method will not work. I am moving the Order Id
field/table from the aaa.mdb to the Local.mdb that will be on each client PC.

When the user identifies the Order Id they want to use, I will move it to
the Local.mdb table. When a form or report is accessed, it will link to the
Local.mdb table to identify the order to process.

It appears I can set a SQL to export the Order Id to the Local.mdb
application. I can then process the aaa.mdb with a link to the Local.mdb. I
can also split the database and keep the Order Id field/table on the user PC.
Can splitting the database cause upgrade problems when 8 to 10 users are
upgraded and none of them can function as an administrator?

It appears I have more than one option to accomplish the task.

Please keep in mind that "There is no one more dangerous than some one with
a little bit of knowledge" and you really have no idea how dangerous I am.
Our only advantage is that I have some idea of how dangerous I am.

Jack

 

[Tim Ferguson]

"=?Utf-8?B?Si1TaGVybS1Ed3llcg==?="

I could be accused of holding out on information. I have an
application designed for a single user and now the need is to have
multiple people use it. I setup a table that holds the order Id, so
different forms or reports can be processed without re-identifying the
Order Id each time.

I too am in the dangerous position of knowing very little about what you
are trying to do -- but this looks like an extraordinarily complicated
way of doing things. If you want to retain an OrderID number you could:

1 - use a global variable, or at least one with enough scope to
remain valid for as long as the report takes to process;
2 - keep a form open with it in a control;
3 - use a database property;
4 - put it in a table of "OrdersThatUsersAreProcessing" along with
the user id, so it doesn't get confused with the other users;
5 - maintain a single-record table in the user's local front end
mdb, so that it doesn't get confused with the other users;

The advantage of 1 and 2 is that you don't have to forget it at the end
of the session; with all the others you will have to tear down the
structure before the user closes the application. My personal favourite
would probably be (2), because it puts the user in control of the
process, whatever that is.

If you want to do 5, then it's no problem because you can simply access a
table in the local mdb without doing anything else.

If you want to do 4, then it's no problem because you can link to a back
end table just the same as all the other base tables.

Hope that helps


Tim F