Access vs. SQL Server

Dear experts,

I am currently using Microsoft Access and the current size of my database is about 250Mb. I need to incorporate an additional set of data which could potentially double the size of the database or more. I am considering whether to switch to a more professional database manager like SQL. I have the following questions:

1. How much data can Access realistically handle before performance starts to degrade materially?

2. What is the next best option for database upgrade in Windows environment (SQL server, etc)?

3. I am currently accessing my database via VBA (ActiveX Data Objects) within Excel application. If I switch to SQL, would I need to rewrite my code?

4. How difficult is it to convert the database from Access to SQL server format?

Thanks for your help. Any additional insights on pros and cons of Access vs. converting to SQL server would be greatly appreciated.

CWF

For a quick MS response: http://www.microsoft.com/sql/solutions/ssm/access/compare-access.mspx

Other than that, i did a quick msn search for "access versus sql server" and found many articles. Look through them and post any other questions you have here.

|||Thanks. This is very helpful.

Access Upsizing Wizard Size problem

I am using the Upsizing Wizard to create a copy of an Access database as a SQL Server database. The original .mdb is 14 mb. The resulting MSSQL database is 72 MB. Why the huge increase in size?I would be willing to bet that the database you are working with has a lot of fields with unspecified length. The wizard probably over-compensated by making all of the character fields nvarchar(4000) or something silly like that. Peruse the datatypes in your tables, and think to yourself if you really need that much room in any of the fields.|||Good idea, but that was not it. The fields are all the size they should be.|||How are you measuring the size? Is this the size of the data portion, the log portion, or the sum of both?|||Right-clicking on the database in Enterprise Manager, and choosing Properties.

Access to Sql server : file Size

I have migrated an Access Database to SQL server.

The original Access MDB file was around 50Mo.

After migration, I get 2 files in SQL directories :
xxx_data.mdf of around 100 Mo
and xxx_log.ldf of around 130 Mo

So the needed space is more than 4 times more than used under Access.

Is this normal, or is there something Wrong somewhere ?

Thanks for any Infos,
Pierre.Firstly the "data" component for your database is typically only stored in the xxx_data.mdf file, so it looks like it is around twice the size of your access db. You should back up your database, truncate and shirnk your transaction log.. see BOL for more info on transaction logs. This will control the size of your xxx_log.ldf file.

Next you can check how much of the 100Mb your data is actually consuming by running a a command like sp_spaceused. I'm not sure if Access has the concept of "devices" but I don't believe that it does. Have a read of this in BOL and maybe that will give you a hand..

Hope this helps.

Originally posted by Plarde
I have migrated an Access Database to SQL server.

The original Access MDB file was around 50Mo.

After migration, I get 2 files in SQL directories :
xxx_data.mdf of around 100 Mo
and xxx_log.ldf of around 130 Mo

So the needed space is more than 4 times more than used under Access.

Is this normal, or is there something Wrong somewhere ?

Thanks for any Infos,
Pierre.|||As specified make sure you didn't hit any errors during this upsizing from Access. And also assess the size of the files and schedule correct intervals of Tlog and database backups to avoid any out of space errors.

Occassionally refer to SQL error log for any information.|||Thanks for your advise.
But I am completly new with SQL server, and I do not see any tools
in SQL Manager to shrink/compact the database.

Where are these tools ?

Even in the BOL, I do not see anything talking about reducing space.

Thanks for any help.|||What version of SQL Server are you running?

Originally posted by Plarde
Thanks for your advise.
But I am completly new with SQL server, and I do not see any tools
in SQL Manager to shrink/compact the database.

Where are these tools ?

Even in the BOL, I do not see anything talking about reducing space.

Thanks for any help.|||True, you didn't mentioned version of SQL server used and make sure your login has SYSADMIN privileges to carry on those tasks.

Refer to BOL for SP_ATTACH_DB & SP_DETACH_DB topics which involves attaching/detaching process.

And make sure you have client tools installed on your machine to talk to the SQL server which has been migrated.