VB Programing support Access Database Microsoft Office VB.Net & SQL Server nev@NevVB.com.au Sydney, Australia (612) 9453-0456 Contact Details 23/02/2012
Microsoft's Access Database is extremely popular and is adequate for many applications. But it is meant for small projects with few users. As applications and volumes grow, the limitations of Microsoft Access soon become apparent. Slow performance, error messages, unstable applications, security issues and data corruption are common symptoms. When the application needs to support more features, more data, and more users, a robust database system such as SQL Server is required.
In Access, the MDB file is opened directly. If the network connection is unexpectedly broken or a client or server breaks down, there is a good chance data will be corrupted. SQL Server runs as a service – the user does not have direct access to the file. If the server shuts down unexpectedly or the network connection is broken, the integrity of the data will be maintained.
SQL Server has a distinct advantage over Access in that all transactions (database updates, insertions and deletions) are kept in a log file. The log records the changes to the data and enough information to undo the modifications made during each transaction. So in the event of system failure, the log file can be used to recover the data.
Access supports a maximum of 255 concurrent users. In the real world, it is common to experience major performance issues with as few as 10 users attempting to use the Access database simultaneously over a network. SQL Server supports a concurrent user base that is limited only by available system memory. Because of its optimised query-processing engine and ability to utilise simultaneously multiple computers, processors and hard drives, it can scale to meet any requirement.
Access supports a maximum database size of 2 gigabytes plus linked tables. Although use of linked tables theoretically enables more much data to be stored, it is common to experience performance issues due to the amount of data being processed. Consider upsizing an Access database if the size reaches 100 megabytes. SQL Server, on the other hand, has vastly improved storage capabilities, allowing for 1,048,516 terabytes of data to be stored efficiently across multiple devices. It also has self-repairing and self-compacting features, making it a very robust database solution.
Access has limited backup features and does not support point-in-time restores. Access does not have performance monitoring features. SQL Server 2000 provides wizards that allow the database administrator to monitor and tune performance. It also contains tools to automate data backup and secure data.
Upgrading a Microsoft Access Back-End database to SQL Server and using ADO and an OLEDB connection is reasonably straightforward.
Note that setting up SQL Server efficiently and securely, and maintaining it requires a new set of skills.