New question: Split database - Linked tables

[Larry Kahm]

The existing application has BEData1.mdb on server and FEClient1.mde on each
workstation.

The new application has links to 4 tables from BEData1.mdb.

Questions:

1. Do these links go in the new back-end of this database - where the
relationship diagram exists?
2. Do the links only in the new front-end?
3. Do they go in both locations of the new application?

Thanks - I'm having an exasperating afternoon and evening. Realized that
despite several books on Access these topics aren't covered.

Larry

 

[Albert D. Kallal]

The existing application has BEData1.mdb on server and FEClient1.mde on
each workstation.

The new application has links to 4 tables from BEData1.mdb.

Questions:

1. Do these links go in the new back-end of this database - where the
relationship diagram exists?

We might be confusing some terms here. There is the term of what we call a
linked table from the front end to a back end table. Then there's also what
is called a relationship between tables that you can design and build in the
relationship diagramming system.

So as for the part about the relationship diagramming, and setting up of
relationships, this is always occurs in the back end, and you do this by
opening up the back end file direclity (so this process always occurs and is
done in the back end file).

2. Do the links only in the new front-end?

If you're speaking about the relationship between tables, then it goes in
the back end as per above. However, you front end will have what's called
linked tables to the back end. Hence that issue of semantics and the use of
"links" here.

So when you relate tables, or draw lines between the tables to define
Relationships we really don't call that idea linked tables, but we call that
related tables.

3. Do they go in both locations of the new application?

As a general rule if you open up the relationship diagramming tools in the
front and part of the application and drop and a few tables you will see the
relationships appear that were created in the back end database. However,
this setup and setting up the diagrams in the front is not required at all,
and would only amount to eye candy for your purposes. So any the
relationship setting is to be done in the back end (you'll actually find
that some relationships things cannot even be set in the front end when
working with back end tables in that front end...it is the back end that is
the boss here). if you think about it, this has to work this way because
what would happen if you had two different font end with two different
relationship settings, which one would win out? So the relationships are
always set in the actual data file itself, and not some other file that
might have a linked table to it...

If you want to read up on splitting you can read my articl here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

In the above I also bring up some issues such as how do you add a new field
to a table? (where do you do this).

 

[Richard]

Hi Albert

I have a related question. If the relationships have been setup in the
diagram window prior to exporting the tables to SQL server do the
relationships need to re established?

 

[Larry Linson]

Hi Albert

I have a related question. If the relationships have been setup in the
diagram window prior to exporting the tables to SQL server do the
relationships need to re established?

Although the UI and application's Queries, Forms, Reports, Macros, and
Modules are separate from the data, when the data is exported to a server
database (SQL Server or other), the term we use is Client-Server.

In the Access realm, splitting into a front and back end, and placing the
tables with data, and relationships in a shared folder is commonly referred
to as a "split database" or "multiuser database".

I cannot answer your question. Although I have worked on many client-server
databases over the years, they all started out as client-server and none
were done first in Access and then "split".

Larry Linson
Microsoft Office Access MVP

 

[John W. Vinson]

Hi Albert

I have a related question. If the relationships have been setup in the
diagram window prior to exporting the tables to SQL server do the
relationships need to re established?

Depends on how the export is done. If you use the Upsizing Wizard the
relationships will be read from the Access database and recreated in the SQL
database. If you just export the tables, I'm not certain, but I believe they
will not, and will need to be manually reestablished.

 

[Richard]

Thank you both for your response.

Richard

Depends on how the export is done. If you use the Upsizing Wizard the
relationships will be read from the Access database and recreated in the SQL
database. If you just export the tables, I'm not certain, but I believe they
will not, and will need to be manually reestablished.

 

[a a r o n . k e m p f]

Jet can't be multi-user

move to SQL Server now, I'd reccomend 'Access Data Projects'

 

[Larry Kahm]

Albert,

Thank your for correcting me and the thorough explanation, but I'm not sure
my question got answered. I'll try with a specific example.

The original applicaiton, BEData1.mdb has tables Staff, Agreements, and
Clients - among others. The new application requires the use of these
tables.

I will use File, Get External Data, Link Tables to "link" to these tables to
BEData2.mdb.

Now, FEClient2.mdb has to see and use these tables. Do I get them from
BEData2.mdb, where they are linked, or from BEData1.mdb, where they exist?

I appreciate your help!

Larry

 

[BruceM]

Wrong again, as you know.

message
Jet can't be multi-user

move to SQL Server now, I'd reccomend 'Access Data Projects'

 

[John Spencer (MVP)]

As far as I know, you can't set up a link to a link. You have to link to
original table (source).

And it makes no sense to me that you would link to a link. It would just add
a level of complexity to any solution. For instance, if BEData2 became
unavailable for any reason then the chain to BEData1 (where the information
is) would be broken.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Gina Whipp]

As far as I remember the relationships will have to be manually created.
Now if you follow the 'wizard' just to the point of dropping in the tables,
it created the relationships, it will 'remember' most of what you did in
Access and you will just have to do the 'obscure' ones. At least it worked
for me, saved me hours of work... actually, saved the Client hours of me
working!

 

[a a r o n . k e m p f]

wow dude-- Access Data Projects 'just works' for multiple users.
And it's not possible to have a reliable Jet multi-user system

Most people blame it on the network.
I blame it on _JET_BEING_STUPID_AND_OBSOLETE_


ADP would be much simpler for you-- no linking-- no horsing around.
Just plug and play client-server architecture

 

[BruceM]

A properly designed Jet database can be reliable with multiple users. It is
not for every situation, but it has its place. You have had this explained.
Denying it does not change the fact that many people have been successful
with multi-user Jet databases. If you cannot make it work the problem is
with your design, not with Jet.

message
wow dude-- Access Data Projects 'just works' for multiple users.
And it's not possible to have a reliable Jet multi-user system

Most people blame it on the network.
I blame it on _JET_BEING_STUPID_AND_OBSOLETE_


ADP would be much simpler for you-- no linking-- no horsing around.
Just plug and play client-server architecture

 

[a a r o n . k e m p f]

you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion

 

[BruceM]

Incorrect (again)

message
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion
you're the one that is insisting that it _IS_ for every occassion

 

[Albert D. Kallal]

Hi Albert

I have a related question. If the relationships have been setup in the
diagram window prior to exporting the tables to SQL server do the
relationships need to re established?

This is actually a user option if you use the upsizing wizard. In other
words when you upsize the database table to SQL server, there's an option
in the wizard that asks if you wnat to preserve the relationships and define
them when the tables get up loaded to the server.

If you don't select the above option when you transfer the files up to SQL
server, then you'll have to redefine and re-do the relationships with the
tools that come with SQL server. (there is a graphical diagramming window
just like an MS access that comes with SQL server, and you would
use that).

So the answer is it really depends, but during the upload process when using
the wizards in access, there is a check box box option that allows you to
preserve the relationships during a upload. If you you don't select this
option, then you simply have to setup the relationships on the other side
after the data been transferred.

 

[Albert D. Kallal]

Albert,

Thank your for correcting me and the thorough explanation, but I'm not
sure my question got answered. I'll try with a specific example.

Now, FEClient2.mdb has to see and use these tables. Do I get them from
BEData2.mdb, where they are linked, or from BEData1.mdb, where they exist?

The wonderful John Spencer has given you a good answer. So, you don't
(can't) link to a link. If you have two different back ends, then you'll
simply have links to two different back end files, and this is perfectly
legal, and often quite common.

The only real restriction or knowledge that you need to know that when you
link to more than one back end file is that you can not enforce
relationships between the two different back ends. You can most certainly
design and build relationships in the query builder Between those tables in
different back ends , but you can't enforce the relationships between
separate back ends.

So if you had customer data in one back end, and custoer invoices in another
back end, you could not choose the option to have cascade deletes to
automatically occur that when you delete a single customer then all the
invoices would automatically deleted. So for this type of automatic deletion
to occur for you, they have to be in the same actual database file and then
you can then enforce the cascade relationships and delete options in the
relationships diagramming window between those two tables.

So, if those two tables are in different back ends, then you can't enforce
the relationships, but you can most certainly use relationships between
those tables in your queries, forms, and reports etc.

 

[Gina Whipp]

Albert...

I used the wizard at one of my Clients and it wouldn't let me upload with
that option checked. Once unchecked the tables went up without a hitch and
I had to redo the relationships on the server using the 'wizard'. It wasn't
that bad as it dropped it in the diagram with the relationships mostly set,
I just had to the un-obvious ones. Can you think of a reason that would
happen?

 

[Albert D. Kallal]

Albert...

I used the wizard at one of my Clients and it wouldn't let me upload with
that option checked. Once unchecked the tables went up without a hitch
and I had to redo the relationships on the server using the 'wizard'. It
wasn't that bad as it dropped it in the diagram with the relationships
mostly set, I just had to the un-obvious ones. Can you think of a reason
that would happen?

Hum perhaps sql server 2000? I thinking that older versions of sql server
did NOT have declarative enforced relationships (they used triggers). There
is two options, one to use DRI, and one to setup triggers. Perhaps that
version of sql did not support DRI (declarative) relationships.

Unfortunately, I don't really don't have a good answer here as to why it did
not work...

 

[Gina Whipp]

Albert,

Yes, it was SQL Server 2000 but it did have all 3 options. Of course,
doesn't mean they all worked. In any event only happened that once so I
chalked it up to 5 me - 1 SQL Server! But I did make a note of that little
anomaly for future reference!