Access or SQL-server

[galashkina]

Are there restrictions on the use of Access for very large projects?

Could it be more appropriate to use the SQL-server for large projects?

[dougmcalexander · Accepted solution]

I have it set up both ways on my teaching laptop.  The size of your project isn't the main consideration.  It's the amount of data in your Catalog Database, as in how much vendor data you chose to install.  I install all vendor data in the my teaching laptop because I never know which parts I will need as I travel from client to client, and as I develop for my clients. 

Honestly, I don't see much difference in speed between Access and SQL, but my laptop is pretty quick.  Sharing the database over the network is where you will see more of a speed decrease.  You need a router switch capable of at least a 1GB transfer rate.  The faster the better.  Other wise you will experience delays when you click Lookup.

I use the Config Catalog Database utility to toggle back and forth between the Access database and the SQL database when I am teaching.  Obviously you should choose one or the other in a production environment, since you will likely be adding part numbers to the database.

That said, once you go to SQL you are a bit more limited, compared to editing the database directly with MS Access.  But that might not be a bad thing.  I have repaired many databases that were damaged when someone edited theirs and was not careful, changing table names, changing field names, changing field formatting, deleting fields, etc.  Such tweaks can damage the database.

If you do switch to SQL you could download and install Microsoft SQL Server Manager, just in case you ever need to edit the SQL database directly.  I have to do that only because when I teach I sometimes create sample tables for class and need to delete them afterwards.