We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one for
each of their clients.
There is now a requirement to run operations globally across all the
databases including a central database holding some global information. What
method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
--
regards,
Br@dley 10 1873
I am not aware of the details of how to accomplish it, but you can link
tables in different databases in SQL Server, just as you can in Jet. If you
are going to do something "global" with disparate databases, that would seem
to be the first approach to consider.
I might suggest that you consider redesigning the database so that only one
instance would have to be run, but the tables have "user" identification for
the data. However, as that may not be a common requirement (e.g, just this
one "outsourcer " or a few), that might just be unused, extra data, for most
of your clients.
Other than this, I am not sure what recommendation you are seeking.
Larry Linson
Microsoft Access MVP
"Br@dley" <do***********@ google.com> wrote in message
news:dv******** **@news-02.connect.com. au... We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
-- regards,
Br@dley
I've done something similar in Access; I coded a routine to dyamically
link the tables I needed and pull in their data, using a local table to
keep a list of the database locations. I don't think an ADP would be a
good choice, since you can only connect to one database at a time.
If you're comfortable with SQL data warehousing, that's probably the
best bet, though... that's what it sounds like you're doing.
What about finding a SQL Server hosting company?
If the SQL Server happened to be hosted in one place and the connection
strings all pointed there (IP over the web - or - VPN and connect) then your
in business. Multiple users anywhere could connect and operate from a
single datasource. Of course depending on the efficiency of the client
application and how much it utilizes the server itself for processing
(particularly on report generation), performance could be an issue. However
I would think it is certainly worth a shot before recoding or messing with
replication. Could be another profit center for you as well whether you
host the db yourself or outsource that. Especially if the client sites have
a good dedicated internet connection.
--
Jerry Boone
"Br@dley" <do***********@ google.com> wrote in message
news:dv******** **@news-02.connect.com. au... We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information.
What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
-- regards,
Br@dley
Larry Linson wrote: I am not aware of the details of how to accomplish it, but you can link tables in different databases in SQL Server, just as you can in Jet. If you are going to do something "global" with disparate databases, that would seem to be the first approach to consider.
Thought of that but we're using an ADP...
I might suggest that you consider redesigning the database so that only one instance would have to be run, but the tables have "user" identification for the data.
Yes, this is the way I'd like to have done it but I didn't design it and
there is not the time/resources to redesign it at the moment. There is also
some benefit to having the data seperate.
However, as that may not be a common requirement (e.g, just this one "outsourcer " or a few), that might just be unused, extra data, for most of your clients.
Other than this, I am not sure what recommendation you are seeking.
Larry Linson Microsoft Access MVP
"Br@dley" <do***********@ google.com> wrote in message news:dv******** **@news-02.connect.com. au... We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best? (Access2000 ADP, SQL Server 2000)
-- regards,
Br@dley
--
regards,
Br@dley
Graham Charles wrote: I've done something similar in Access; I coded a routine to dyamically link the tables I needed and pull in their data, using a local table to keep a list of the database locations. I don't think an ADP would be a good choice, since you can only connect to one database at a time.
Unfortinately it's not a choice... it's what the system has been written in.
I could maybe write an external interface MDB to do the processing...
If you're comfortable with SQL data warehousing, that's probably the best bet, though... that's what it sounds like you're doing.
--
regards,
Br@dley
Jerry Boone wrote: What about finding a SQL Server hosting company?
If the SQL Server happened to be hosted in one place and the connection strings all pointed there (IP over the web - or - VPN and connect) then your in business. Multiple users anywhere could connect and operate from a single datasource. Of course depending on the efficiency of the client application and how much it utilizes the server itself for processing (particularly on report generation), performance could be an issue. However I would think it is certainly worth a shot before recoding or messing with replication. Could be another profit center for you as well whether you host the db yourself or outsource that. Especially if the client sites have a good dedicated internet connection.
All the databases are on the same local server so none of this is required:)
"Br@dley" <do***********@ google.com> wrote in message news:dv******** **@news-02.connect.com. au... We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
-- regards,
Br@dley
--
regards,
Br@dley
"Br@dley" <do***********@ google.com> wrote in
news:dv******** **@news-02.connect.com. au: We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
Are the dbs on different servers? Do they have different names?
If they're not on different servers on can just access them using their
names? eg: SELECT * FROM FFDBA_ESO_LOCAL .dbo.tblSchools runs fine when my
ADP is linked to a db named Temp but which is on the same server as the db
FFDBA_ESO_LOCAL (assuming I have permissions of course).
And if they are on different servers the servers can be linked (BOL has
mucho info about linking servers; we can even link to an mdb if it resides
on the same machine as the SQL server), and the dbs can be accessed by
servername.dbna me.owner.object ?
eg SELECT * FROM Server2.FFDBA_E SO_Remote.dbo.t blSchools
But perhaps I am not understanding. Perhaps you need to access tblSchools
on several different dbs on several different servers. I don't know if a
Union will work in those circumstances or not, but I'd give it a shot.
--
Lyle Fairfield
Lyle Fairfield wrote: "Br@dley" <do***********@ google.com> wrote in news:dv******** **@news-02.connect.com. au:
We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000) Are the dbs on different servers? Do they have different names?
Same server.
If they're not on different servers on can just access them using their names? eg: SELECT * FROM FFDBA_ESO_LOCAL .dbo.tblSchools runs fine when my ADP is linked to a db named Temp but which is on the same server as the db FFDBA_ESO_LOCAL (assuming I have permissions of course).
Perhaps. Ideally I'd need to make it as dynamic as possible (ie. 'register'
the database into a table and insert the table names into the SQL
statement).
<>
--
regards,
Br@dley
Ahh...
Interesting, so you need to combine all the databases together for a single
source solution.
You could make a database called "globaldb" or something and make views
named for each table object you have in the other databases...
use globaldb
create view 'table1'
as
select * from db1.dbo.table1
union
select * from db2.dbo.table1
union
select * from db3.dbo.table1
create view 'table2'
as
select * from db1.dbo.table2
union
select * from db2.dbo.table2
union
select * from db3.dbo.table2
and so on...
This gives you a single db to connect to that combines data from the other
db's. In just about any sql server scenario view objects are completely
interchangeable with table objects. I always use views to "pull" in data
from other databases. Then if the source database changes you have a "shim"
layer where to can adjust such changes to keep from having to re-code your
application.
--
Jerry Boone
"Br@dley" <do***********@ google.com> wrote in message
news:dv******** **@news-02.connect.com. au... Jerry Boone wrote: What about finding a SQL Server hosting company?
If the SQL Server happened to be hosted in one place and the connection strings all pointed there (IP over the web - or - VPN and connect) then your in business. Multiple users anywhere could connect and operate from a single datasource. Of course depending on the efficiency of the client application and how much it utilizes the server itself for processing (particularly on report generation), performance could be an issue. However I would think it is certainly worth a shot before recoding or messing with replication. Could be another profit center for you as well whether you host the db yourself or outsource that. Especially if the client sites have a good dedicated internet connection. All the databases are on the same local server so none of this is
required:) "Br@dley" <do***********@ google.com> wrote in message news:dv******** **@news-02.connect.com. au... We have a product that is being used by a client to outsource salary packaging. They therefore run several instances of our database, one for each of their clients.
There is now a requirement to run operations globally across all the databases including a central database holding some global information. What method(s) would people recommend that would work best?
(Access2000 ADP, SQL Server 2000)
-- regards,
Br@dley
-- regards,
Br@dley
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jimstallings |
last post by:
Hi,
I am trying to locate documentation on using views across databases.
We have serveral databases broken down by application, some shared
databases and some location specific databases supporting multiple
facilities using the applications. The DBA for our client is the one
that set up the topology of these databases but I don't understand...
|
by: surfdog58 |
last post by:
Need to build complete deployment package for a multi-project enterprise soln
consisting of 3 web apps, 20+ web services, 2 windows services, 61 component
dll's and 4 databases.
Everything except the databases are in one Build Solution.
Project Output must be structured into unified Application Space with
folders for webs and winservices in...
|
by: chandrub78 |
last post by:
Hello Everyone
We have a third party tool which maintains its own custom
database,DB1 on SQL Server 2000 on Server A (Windows 2000). We built an
inhouse application which maintains a transactional database, DB2 also
on Server A derived from DB1. Since the DB1 is frequently updated the
synchronisation between the databases is currently...
|
by: RipTide |
last post by:
Background:
Using an unsupported/abandoned multi-user multi-database program that uses
Access 97 and Jet 3.5. Program itself appears to have been built with
PowerBuilder 6.5.
Databases reside on an NT 4 SP6 file server with opportunistic locking disabled.
Workstations are Win98 SE with Access 97, Jet 3.5 SP3, and Network
redirector file...
|
by: Damon Grieves |
last post by:
Hi
I just want to be sure I understand how the Access client works. If I have
an Access back end with a million records on a server and an Access client.
If the client is installed on the users pc and run by the user...does Access
drag the whole million records across the LAN when we call up one record ie
filters for that record locally? If...
| |
by: bobwansink |
last post by:
Hi, I'm relatively new to programming and I would like to create a C++
multi user program. It's for a project for school. This means I will
have to write a paper about the theory too. Does anyone know a good
place to start looking for some theory on the subject of multi user
applications?
I know only bits and pieces, like about...
|
by: Merrall, Graeme |
last post by:
I don't think there's an easy way to do this but I thought I better ask just in case. I'm trying to come up with a way to search across a number of databases without resorting to lots of horrible scripts. In one database I have a lot of news stories from our news provider while in another database I have a lot of user entered content. Ideally I'd...
|
by: Dmitri |
last post by:
Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned...
|
by: Philip Nelson |
last post by:
I'm a UNIX person who is having difficulty understanding Windows security.
I've got to install a DB2 server (initially Express-C) in a Windows domain
environment.
I fire up the installer and everything goes fine until I come to create the
user IDs for the instance. I read that the recommended way is to use a
Local Domain account (not a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |