Best database configuration ?

R

Rob Hofkens

Hi,

I have been reading a lot lately about possible database configurations and
I am not sure whats best for my situation.
I want to make a database for my office.
About 10 people going to work with the database and I made an estimade of
about 300.000 rows / year that will be added to the database.

The network Server is running on SBS 2003 Server software with and SQL 2000
is installed.
I have office 2000 on every Windows XP client but could upgrade to Office
2003.

I know now that there are several options:
1) A shared MDB file on a shared folder on the Server.
2) A Front End ADP in combination with the SQL Server with bound forms.
3) A Front End ADP with SQL Server and the use of unbound forms.

Can someone advice me what to do best ?

Best regards,

Rob Hofkens.
 
B

Brian Wilson

Rob Hofkens said:
Hi,

I have been reading a lot lately about possible database configurations
and I am not sure whats best for my situation.
I want to make a database for my office.
About 10 people going to work with the database and I made an estimade of
about 300.000 rows / year that will be added to the database.

The network Server is running on SBS 2003 Server software with and SQL
2000 is installed.
I have office 2000 on every Windows XP client but could upgrade to Office
2003.

I know now that there are several options:
1) A shared MDB file on a shared folder on the Server.
2) A Front End ADP in combination with the SQL Server with bound forms.
3) A Front End ADP with SQL Server and the use of unbound forms.

Can someone advice me what to do best ?

Best regards,

Rob Hofkens.
Click to expand...



It depends on your skill set. How well do you know SQL Server? Are you
happy to do the database design, and the admin (manage logins, backups,
maintenance, etc)? Are you happy writing stored procedures in Transact SQL
or are you more familiar with Access queries?
What about Access? Have you ever built an application using the adp format?
If not, how long have you got to learn this technology and then build.
With my skills and experience, I would choose an mdb file on each pc, SQL
Server database as the datasource. The mdb format allows me to use a number
of different ways to access the data: linked odbc tables and also DAO and
ADO coding. Perhaps someone will suggest adp with bound forms but I doubt
whether they would suggest adp and unbound forms - what is the point? I
would avoid adps all together, but this topic has been thrashed out so many
times, I'm not sure if there is much to say which is not publicly available
on the web.
 
J

Jeff Boyce

Brian's reply covers most of what I would have offered. I would ask,
though, how large your 300.000 rows are? Are you adding 300.000 bytes per
year or 300.000.000?

Were it me, with SQL Server available, I'd probably put the data in SQL
Server and use Access (DAO/ODBC) as a front-end on each PC.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
K

Klatuu

I'm with Jeff on this one.

Jeff Boyce said:
Brian's reply covers most of what I would have offered. I would ask,
though, how large your 300.000 rows are? Are you adding 300.000 bytes per
year or 300.000.000?

Were it me, with SQL Server available, I'd probably put the data in SQL
Server and use Access (DAO/ODBC) as a front-end on each PC.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Click to expand...
 
B

Brian Wilson

And in case I wasn't clear enough, that was what I opted for too - mdb front
end SQL back end.

Although I would not restrict myself to DAO necessarily.
 
K

Klatuu

I agree

Brian Wilson said:
And in case I wasn't clear enough, that was what I opted for too - mdb front
end SQL back end.

Although I would not restrict myself to DAO necessarily.
Click to expand...
 
R

Rob Hofkens

Brian Wilson said:
It depends on your skill set. How well do you know SQL Server? Are you
happy to do the database design, and the admin (manage logins, backups,
maintenance, etc)? Are you happy writing stored procedures in Transact
SQL or are you more familiar with Access queries?
What about Access? Have you ever built an application using the adp
format? If not, how long have you got to learn this technology and then
build.
With my skills and experience, I would choose an mdb file on each pc, SQL
Server database as the datasource. The mdb format allows me to use a
number of different ways to access the data: linked odbc tables and also
DAO and ADO coding. Perhaps someone will suggest adp with bound forms but
I doubt whether they would suggest adp and unbound forms - what is the
point? I would avoid adps all together, but this topic has been thrashed
out so many times, I'm not sure if there is much to say which is not
publicly available on the web.
Click to expand...

Thank you for answering Brian !

I have skills in programming and I created some applications in the past but
to be honnest that was many years ago.
I am a quick learner though so I realy like to give it a go.
I would like to start out the right way so thats why I asked for advice.

I have read several comments on ADP and many people avoid them.
I wonder why ?

Cheers.

Rob Hofkens.
 
R

Rob Hofkens

Good question Jeff :)
I think it would be something like 30.000.000 bytes.
Now that I think about it...is this big ?

Thanks Jeff for your advice.

Rob Hofkens.
 
J

John Vinson

Good question Jeff :)
I think it would be something like 30.000.000 bytes.
Now that I think about it...is this big ?
Click to expand...

Only moderately big. An Access database can handle two gigabytes; with
good indexing and good query design, neither SQL/Server nor JET is
going to break a sweat searching or sorting this table.

John W. Vinson[MVP]
 
A

Albert D.Kallal

I know now that there are several options:
1) A shared MDB file on a shared folder on the Server.
2) A Front End ADP in combination with the SQL Server with bound forms.
3) A Front End ADP with SQL Server and the use of unbound forms.
Click to expand...

4) a standard MDB front end, and linked tables to sql server.

4 is a good choice if you have an existing application, and want to migrate
it to sql server. A ADP is a good choice if you are starting from day 1 with
sql server. Your existing mdb file with code will not convert to a ADP, and
this is especially so if you used lots of dao code....you will have to
re-write it.

So, often, option #4 is preferred for *existing* applications. You simply
use odbc tables, and thus can salvage about 90% of your code...
 
R

Rob Hofkens

Thank you Albert !

I realy need to start from scratch so thats why I started the topic.
But after reading the comments here in my topic I have a feeling that
working with an ADP is less flexible then with a MDB using linked tables and
DAO / ADO.
Dunno if that conclusion is true though.

Rob Hofkens.
 
A

Albert D.Kallal

Rob Hofkens said:
Thank you Albert !

I realy need to start from scratch so thats why I started the topic.
But after reading the comments here in my topic I have a feeling that
working with an ADP is less flexible then with a MDB using linked tables
and DAO / ADO.
Dunno if that conclusion is true though.
Click to expand...

quite reasonable conclusion....
 
Top