Access 2000 to SQL?

[Denise]

I want to know if you'd recommend that a novice convert
back end data files to SQL database tables. I want to
continue using Access 2000 as front-end. Our users are
placing high demand on data tables requiring frequent
compact and repairs.

Any other suggestions?

 

[Lynn Trapp]

I kind of doubt that a novice will be able to handle that. It will take an
experienced SQL Server programmer/DBA to do the job correctly.

 

[Albert D. Kallal]

I want to know if you'd recommend that a novice convert
back end data files to SQL database tables. I want to
continue using Access 2000 as front-end. Our users are
placing high demand on data tables requiring frequent
compact and repairs.

Any other suggestions?

Well, if you are a novice, the how do you know that you are placing too much
demand on your current system?

Without some numbers as to how many users..and how large the largest table
is, then I can't say if you need sql server yet.

However, I will say that if your application is not designed correctly, then
moving to sql server will NOT fix any performance problems you have. In
fact, in many cases you will actually see a slow down. If performance is
not good now..then switching to sql server may not help at all.

Assuming you only have like 10 users, and small tables of only 50,000
records..then you should NOT be experiencing any performance issues.

Further, you mention that you need frequent compact and repairs. Do you now
place a front end on each pc (preference a mde), and have the back end mdb
file on the server? If you don't have that setup now..then you need to do
this. In fact, even IF you go with sql server, you still need to install the
software that you write on EACH pc. If you don't do this..then even when you
use sql server, you likely will have to still do compact and repairs on the
shared front end.

It is without question that sql server is capable of handling more users,
and is generally more reliable then ms-access. However, if you don't take
some steps to determine why you are NOW having problems...moving to sql
server WILL NOT necessary fix those problems.

So, how many users do you have at the same time (max)?
How large is the largest table in your database?
How large is the back end database (right after a compact)?
Are we taking a standard office LAN, or some type of wan?

 

[Sharon]

I am having the same problem. I have many tables, a few are over 50,000 records. We have a maximum of 6 users at one time. When I compact the back end, it is only 80 KB, but within two days is over 1 gig. We are using Terminal Services, with one front end used by most users, two users have a different front end with more access to data. The time between compact is getting shorter, and so I have the same question as Denise - should I convert my back end to SQL to handle this problem, and continue to use Access for the front end?
Sharon