Access to SQL Server - Scalability Issues

[paulocc]

Hi,

I am pretty new to VB/Access/SQL Server but have used other development frameworks before.

Basically, I am a bit confused as to the capabilities of Access and SQL Server. I know that both are tightly coupled with VB. I have been told by some that Access is sufficient for single-user applications only whereas SQL Server is for multi-user. Others have commented that Access can be used for multi-user applications.

I need to prototype and application and want to use VB and Access but am concerned as to the scalability of such an approach. Does a VB app with Access DB convert easily to SQL Server?

Any help would be really appreciated.

Regards,
Paul.

 

[SQLSister]

Access is often used in multi user environments. The real issue between the two is speed (and cost of course!).

As to how hard it is scale up, that depends on the situation.

If you used Access only for the back end and wrote all your code in VB, it probably is a matter of simply converting the tables, making some minor adjustments to data types is you used the upsizing wizard in Access and then your application will probably run after some tweaking due to the different flavor of SQL Access uses and the need to change the connection string. How difficult this is depends a lot on how complicated the database calls are and whethter you used good object programming practices and put the connectioin string in one function rather than writing it over and over again throughout the program. To improve performance, you may want to adjust your code to call stored procedures rather than writing the SQL directly in VB.

If you used Access as the front end and wrote the code in VBA and used Access queries, you will definitely need to convert the queries to stored procedures to really gain performance benefits. This is the often the more complicated upgrade since people who program this way are often not familiar with writing SQL statements at all.

In either event, SQL server needs database administration and Access does not even have the tools do do real db admin. So someone will be needed to perform this job on a recurring basis.

Personally, in your case when you are starting from scratch, I would use SQL server to do the prototype because the time to set up the tables, etc between Access and SQL Server would not be appreciably different and since you weren't planning to use Access as the front end, there seems no need to start in Access if you have SQL Server already available. Now if you already knew Access and not SQL, I might consider protoyping with it to speed up the prototype if you have a tight deadline. But since you have learning curve anyway, I'd go ahead and use the program I intended the final result to be in.