Do I need SQL Server

[storer]

We have 2 registration databases in Access on a network, with 4-5 users. I think by most standards, they are not that big (the larger one is approx. 35,000KB) Within the next 6 months or so we plan to combine, or link the databases. Sometimes, it can run slow (or at least doesn't zip along) and I am working on improvements in the forms, etc...to improve performance.
It has been suggested that we put the back end on SQL Server and keep the front end as an Access application. I don't know anything about SQL server. Would this be a good idea? Is SQL hard or easy to learn? Could it possibly be as simple as importing the back end in SQL and linking with Access?
I am interested in improving speed, especially as the database grows, and in preserving the integrity of the data. One person I consulted is leary of Access, saying she has heard (this was in the past) that it is not a stable environment, especially with lots of data. We had a scare today when we discovered the larger of the 2 databases (and one with the most important info!) was corrupted. Evidently the hard drive on the server "died" while a user was in the database. Everything else was retreived ok except for this database. We were able to repair it but this scare made us wonder if we are on the right path.

Would SQL server be overkill? Any suggestions or thoughts would be greatly appreciated!

 

[foxdev]

I don't think SQL Server would be overkill. Based on the criticality of your data, it would be advisable (you can do point-in-time recovery, for example). Though your number of users is low, you will still enjoy performance improvements.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[storer]

Thanks for your input! I'm starting to work with Access 2000 and have read bits about its upsizing wizard. Can I "upsize" an access database so I have the back end in SQL and leave the front end in access? Is it really that simple? I've been told I would have to learn visual basic to work with SQL. I've learned some with Access but am still a piker. If just tables are in SQL, why would I need VB, except it is good for working with access. I'm afraid my knowledge of VB wouldn't be enough to handle SQL if it is required. Thanks!

 

[Guest_imported]

Yes you can easily upsize an access DB to sql server. No VB is not a requirement for SQL Server. What will be useful to know is Transact SQL which is a superset of ANSI-92 sql which you use to write stored procedures. Your front end tool does not matter then.

 

[harryrich]

I'm going through a conversion from MS-Access 97 forced on me by the database size limit. I have a table that's getting a little hot at 0.9 GB of data. I realize there are some alternatives but the table is growing at 0.6 GB per and the SQL server conversion should provide some lasting relief. I've learned a few things:

(1) The upsizing wizard, free from Microsoft, does a good job of converting databases, indices and all.

(2) The upsizing wizard will also transfer reasonable amounts of data into the new database for you.

(3) If you are transferring unreasonable amounts of data, you should probably have the upsizing wizard build only the table. It's too easy to run into a timeout which will abort the operation and try your patience with the huge rollback. Use the Access export wizard to export the large tables in CSV format, and use the SQL server import wizard to make a similar import. This is probably faster as well as more reliable.

(4) The hard spot in our conversion is the data we import on a regular basis. SQL server Data Tranformation Services will do all we need but there's a learning curve involved. BTW the Visual Basic scripting language used in DTS is that used in Internet Explorer and you can get the language specification on-line.

(5) I timed some sample queries using an MS Access front end and running the same queries through the SQL server query analyzer. The times were essentially the same, so Access seems to be an efficient front end.

Best of luck,

Harry

 

[Craftor]

Hi everybody

I need to do an update between 2 SQL Servers - take data from a set of tables on one and transfer it to another. I can't simply export the data because the tables don't link up, values are combinations of other values etc. The way I have done this is - create a script to create the necessay tables on the 1st server, exported those tables, created scripts to update the tables on the second server. This seems rather cumbersome - does anyone know of a better/easier way?