splitting database

[Nandini]

I have an access database based on Ms access 2003. Regarding the upsizing I
have some questions.
1. Is there any limitaion of storage space of a backend database containing
only data both in case of mdb database or sql server database, whereas
frontend is based on access 2003?
2. Is there any limitations in number of users for using backend database
(access or sql server) through ms access at the front end?
3. Is it possible that multiple backend databases (access) can be accessible
from one user interface at the fronend through linking tables? If so how is
it possible?
Early response will be highly appreciated.
With regards,

 

[Alex Dybenko]

Hi,

I have an access database based on Ms access 2003. Regarding the upsizing
I
have some questions.
1. Is there any limitaion of storage space of a backend database
containing
only data both in case of mdb database or sql server database, whereas
frontend is based on access 2003?

Access has 2GB limit, SQL Server - no

2. Is there any limitations in number of users for using backend database
(access or sql server) through ms access at the front end?

Access can handle up to 255 concurrent users, I think practical limit is 50
SQL Server - no limit

3. Is it possible that multiple backend databases (access) can be
accessible
from one user interface at the fronend through linking tables? If so how
is
it possible?

yes, possible, just link tables to different access mdbs or SQL server
databases.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Nandini]

Thank you very much.
For linking multiple databases what should I do? Link table manager of tool
menu is to be used ? Please help me much more.
With best regards,

 

[dymondjack]

You should also note that, when linking tables between databases, no
relationships may exist between the various databases you link. For example,
db1 can have relationships, and db2 can have relationships, but db1 and db2
cannot have crossed relationships.

For linking multiple databases what should I do?

In Access '03 (and before... I'm not sure about '07), right click in the
database window under Tables, and select 'Link...' You can specify what
table to link from. Repeat for each table you want to link to.

To do it programmatically, see this link:

http://www.mvps.org/access/tables/tbl0009.htm

This will relink tables from one db (making maintence a breeze), and can be
modified to link from multiple sources.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Nandini]

Thanks for your guidance.
I want to know something more. Same parameter queries present in the front
end will be able to retrieve data present in the tables of multiple
databases, if they contain same fields and also what to do about the existing
relationship between the tables (at present this database contains five
tables which are related)? Actually my objective is to cope with the 2GB
limitation and gradually increasing nature of data as this is made as
bibliographic database for a library. So I want to know whether its possible
to create same set of tables at the backend in continuation (record Id as
primary key) of previous set when the backend database would cross the limit
of 2GB. Whether its better to use sql server as backend datbase. Please help
me to decide the correct one.
With best regards,

 

[Alex Dybenko]

Hi,
as you have such a big tables - I think best option is to use SQL Server as
backend, you will have one set of tables and that is

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Nandini]

Hi Alex,
Thanks for your kind suggestions and all the limks. Please help me much more
giving the liking websites, where I can get all the steps and all the tips
regrding use of sql server as backend.
With best regards,