Re: Splitting and archiving a large database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John's analysis here is so teriffic, I just HAD to complement him on it.
The major difficulty in partitioning a logical database between several
physical databases is that you lose the enforcement of referential integrity
(RI). I have never found another major disadvantage of this, and I'd really
like to know if there is one.

MSDE will not only allow you to develop your database, but will probably
allow you to operate it for some time. When the time comes that you exceed
it's limitations, the migration will be painless, except for the cost of SQL
Server, and perhaps a server operating system, since MSDE could be run on a
non-server OS for now.

Tom Ellison


"John Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:q0mqs1llai2bbmbmrcjanmtperoa7e986k@xxxxxxxxxx
> On Tue, 17 Jan 2006 10:23:03 -0800, "Elizabeth- MDC"
> <ElizabethMDC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
>>The Access help file says that RI can still be set if the
>>files are all in Access. Does anyone have any experience with this?
>
> You can enforce RI between two tables ONLY if they are in the same
> database file. The reason makes sense: if there is a relational
> constraint defined in A.MDB affecting a table in B.MDB, there is no
> way that it can be enforced unless A.MDB is open. There's nothing to
> stop someone from opening B.MDB directly, or opening it from X.MDB,
> and making a change which violates the rule.
>
> You may want to look ahead to the future, and consider putting the
> data into SQL/Server, still using Access as a frontend. You can test
> this out for free using the MSDE version of SQL which comes with
> Access - you can install it from the SQL folder on the OfficePro CD.
>
> John W. Vinson[MVP]


.

Relevant Pages

  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: Prevent Viewing on worksheet
    ... and run MSDE as a server. ... been on the market for what.. ... MSDE changed the friggin database market... ... i really reccomend using sql authentication.. ...
    (microsoft.public.excel)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)