New question: Split database - Linked tables

[Larry Kahm]

Only after I posted - and re-read what I had written - did the logic fall
into place.

I'm still reeling from the slow form load issue I posted earlier and didn't
have enough wits to think this one through.

Thanks!

Larry

 

[Larry Kahm]

Thanks!

The tables used by the new application are for reference (i.e., read-only)
so the issue of cascading deletes is not a concern.

I do appreciate the throrough explanations and hope that others benefit from
my confusion.

Larry

 

[David W. Fenton]

Hum perhaps sql server 2000? I thinking that older versions of sql
server did NOT have declarative enforced relationships (they used
triggers).

Not 2000 -- the biggest upsize I ever did was from A2K to SQL Server
2K and, thankfully, the A2K3 upsizing wizard created the
relationships. It may be that the A2K upsizing wizard did not,
because A2K predates the release of SQL 2000.

 

[David W. Fenton]

A properly designed Jet database can be reliable with multiple
users.

Well, only if you're a competent Access developer. That clearly
excludes Aaron.

 

[Gina Whipp]

Must have been my lucky day!

 

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

re-building relationships-- in a database that actually supports RI--
is a PLEASURE.
You might think of it as work-- but I think of it as _FREEDOM_.

Jet doesn't support RI in any way whatsoever. It's just not reliable
enough (mainly because it is was too limited for any real world use).

Prime Candidate = Linked Jet databases-- you can't enforce RI.

With SQL Server, you have many ways to do the same thing.

Maybe you guys should continue to learn more about SQL Server instead
of fighting it because you're scared because you don't know jack shit
about it.

-Aaron

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 hitchand
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?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

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.

 

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

SQL 7.0 supported DRI also.
Nice try

And triggers are actually _MUCH_ better than anythign you guys could
possibly write in VBA, because if you put your logic in VBA, you
always run the risk of _SOME_ application not enforcing this.

-Aaron

 

[Larry Linson]

Jet doesn't support RI in any way whatsoever.
It's just not reliable enough (mainly because
it is was too limited for any real world use).

I believe you know that what you stated so strongly is not true.
Relationships defined in Access for Jet or ACE databases include the option
for Referential Integrity.

Prime Candidate = Linked Jet databases-- you
can't enforce RI.

Jet or ACE enforces Relationships defined in the database where the
pertinent Tables reside. Do you intend to imply that the same is not true
for MS SQL Server and other server databases?

Maybe you guys should continue to learn
more about SQL Server instead of fighting
it because you're scared because you don't
know **** **** about it.

If you read here, you know that many of "we guys" use MS SQL Server, and
other server databases, have done so for years, know them very well, and are
not in the least "scared" by them. Don't try to transfer your fears of Jet,
which you have demonstrated that you obviously do not know well, onto us.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

[to Aaron:]

Don't try to transfer your fears of Jet,
which you have demonstrated that you obviously do not know well,
onto us.

He knows perfectly well that he's just flat out telling lies.

 

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

you might tink that Jet supports DRI.

It might, I don't buy it.

But the bottom line is that when you split the database-- you can no
longer use DRI.

In other words, you can't split a database into Orders (backend) and
OrderComments (frontend) and require DRI.
It just doesn't work in Jet.

You can take your baby-sized database, with hundreds of limits- -and
shove it!

-Aaron