sql server replication: Long description for short question:
I currently have a database application using Access 2003 as the front
end and as the back end. I will be changing the back end to SQL
Server 2000.

The front end links to 2 databases at any time. One of the databases
is the "collection" and the other database is the "shared" database
which contains information such as list of Artists, Periods,
Countries, Currency Codes, etc. which are used in the "collection"
databases. There are at least 5 different collections. The user logs
in to a collection and the front end links the tables to the 2 .MDB
files. Each collection is a separate MDB file and there is a
Shared.MDB file. The table names in each of the collections are
identical.

I would like to reproduce this with SQL Server however I have to start
thinking long-term. In Access I can link to tables from 2 different
SQL Server databases without any problems.

However one of the reasons I am moving to SQL Server is that we want
to start developing a web-based front-end, to get over some of Access'
limitations, using ASP. Normally I can only connect to one database
at a time with ASP and therefore can not connect to the Shared
database and a Collection database simultaneously.

I was therefore wondering if I shouldn't perhaps reproduce the Shared
database's tables in each of the Collection databases as subscribers
to the Shared database who would be a replication publisher. Every
example I have seen of replication has been from server to server
rather than from database to database within the same server.

The only other idea I can come up with is putting all of the
collections in to one database merged with the shared database.
However this would involve renaming each and every table and may break
a whole lot of my existing code so I would probably want to avoid
that.

Am I going in the right direction? Is there a better way of doing
this?

Any ideas and suggestions would be greatly appreciated.

Derek,
Personally I would restructure this completely, and have all collections in
one set of tables in one database, with an extra column to designate the
collection, along with all the shared tables. As a temporary fix you could
create separate databases and have views to segement the collections. The
views would be identical in name and sql apart from the where clause which
segemnts the collection. Currently when someone chooses a different
collection you link to a different access database. This would become
temporarily a case of opening a different database. Ultimately it would be
an argument sent into the stored procedures which return the records and the
databases containing views could be dropped.
HTH,
Paul Ibison

[quoted text, click to view]
Paul,
Thank you for your reply.
I can't put all of the collection articles in to one database as there
are copies of the individual collections which are sometimes
distributed on to different machines for independant work. That
machine can only have one collection on it as the individual is not
"aware" of the other collections.

[quoted text, click to view]

This looks more like what I need to do. However I have not seen a
view example yet which allows me to do a select from two different
databases.

If I have a tblArticles table in the dbCollection database which has a
field fldArtistNum, fldTitle and I have a tblArtists table in the
dbShared database which has the field fldArtistNum and fldArtistName
how could I do a view which would allow me to have the Title and
ArtistNames?

If they were all in one database I would do

SELECT tblArticles.fldTitle, tblArtists.fldArtistName FROM
tblArticles INNER JOIN tblArtists ON tblArticles.fldArtistNum =
tblArtists.fldArtistNum

How could I do this sort of thing if tblArticles was in a dbCollection
database and tblArtists was in a dbShared database???

Derek,

this type of query would work for you in a view:

SELECT a.fldTitle, a.fldArtistName
FROM dbCollection.dbo.tblArticles a INNER JOIN dbShared .dbo.tblArtists b
ON a.fldArtistNum = b.fldArtistNum

HTH,
Paul Ibison