Migrating Microsoft Access Databases to SQL Server 2. Database. Journal. Microsoft Access is a very good database solution, but it has limits. While the portability of mdb and accdb files is convenient, there are advantages to moving to the less portable SQL Server solution. Cost, however, is not one of those advantages. THIS TOPIC APPLIES TO SQL Server starting with 2008 Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. In SQL Server, each column, local. Unless you have a SQL Server available to you in your network, Access data stores will continue to be a better, more cost effective route. On the other hand, if you do have SQL Server, theres very little reason not to consider migrating your Access Databases. Not all custom made Access applications easily lend themselves to a SQL Server solution so youll need to do some analysis before choosing a migration path. Thats what well consider in this article. Why upsize from Access to SQL Server Capacity. Microsoft Access files, be they MDB or ACCDB, have a maximum capacity of 2 gigabytes, minus the space needed for system objects. The work around for exceeding this limit is to use multiple data files, and this is indeed an adequate solution when the required capacity is only a handful of GBs. TNBlogsFS/BlogFileStorage/blogs_msdn/petersad/WindowsLiveWriter/InstallSQLServer2008R2ExpressEdition_13353/image26_thumb.png' alt='Microsoft Sql Server 2008 Rename Database Server' title='Microsoft Sql Server 2008 Rename Database Server' />However, once your data reaches tens or hundreds of gigabytes, Access files are no longer a viable solution. There are, of course, other databases that would work. Oracle, if you can afford it. My. SQL, now owned by Oracle, has been a free, open source solution. Perhaps there are other database solutions out there, but for more than a decade Ive been impressed enough with SQL Server that I havent cared to look around for anything else. It can handle whatever capacity your application requires. Security. I am the worst developer when it comes to security. I hate thinking about it and I loathe implementing it. Using security in SQL Server can be as simple as you like, or as complicated as you need. You can GRANT access to objects and DENY to others whole tables or individual columns. In my opinion, this sort of Power Mad Micro Management leads only to pain and frustration. Ive seen implementations where a user is granted access to a stored procedure that references a view to which the user is denied. As I recall, DENY always trumps GRANT, which means a procedure with this security configuration would fail. This sort of thing is also very difficult to debugSo what does this mean for your Access application Implementing security on SQL Server is at least as simple as trying to manage User Level security in MDB files of the Access 9. As for Access 2. 00. User Level security paradigm was abandoned in ACCDB files. The easiest way to apply security for users of an established domain is to link the SQL Server tables to your Microsoft Access client using a Trusted Connection and implement a role based security model. Putting domain users in one or more roles gives them access to the tables that correspond to that role. The 1 SQL Server community and education site, with articles, news, forums, scripts and FAQs. What version of SQL Server do I have This unofficial build chart lists all of the known Service Packs SP, Cumulative Updates CU, patches, hotfixes and other. For example, if a user attempts to read salary information in the HR tables but they are not in the HR role, the SQL Server will deny access. This might generate an ungraceful error user interface, but that too could be managed through VBA code. Availability. By availability I have in mind two ways that SQL Server is more available than Access files downtime and visibility. In order to modify an Access data file, whether that be adding tables and columns or simply performing a compact and repair, the file must be opened exclusively by one user. That means you need to get all other users to close their client files that link to the data. This can be extremely difficult when users go home at night and leave the application open on their desktop. Sure, there are ways around this. I once built a module that would watch the data file for a signal to shut down the client. It works, but at the expense of having a hidden form open on each client instance, constantly pinging the server to see if it should shut down. I hated that approach. The other method I implemented was to restart the server. This approach is very drastic and one I wouldnt recommend, but it is effective. SQL Server eliminates the need for either of these offensive practices. Go ahead and add a table or compact the data files. You can even add a column to a table that clients are linked to and currently using. The Access application wont see the new column until their links are refreshed, but provided you dont remove or rename an existing column, the application will continue to function correctly. The other way SQL Server availability exceeds that of Access MDB or ACCDB files is the answer to this question How do I make my Access application available over the Internet With an MDB file, this is simply impossible. Can You Download Music From Ps3 Browser on this page. You and I cannot create table links to an MDB file on a remote server via the Internet. We can, however, link to a hosted SQL Server. For more information on how to do this, see my August 2. Migrating your AccessSQL Server App to the Cloud. How to upsize your Access database. There are a number of ways to upsize your Access database to SQL Server. I discuss three options below. The topography of your application will determine which one that is best for you. SQL Server Import Wizard. If you simply want to pull in the basic table structure and data, you can use the SQL Server Import and Export Wizard that is part of SQL Server Integration Services SSIS. This is a quick and dirty way to move your tables and data. It does not, however, reproduce primary and foreign keys, so this route will require that you tweak every table in design mode. Indexes too will have to be added manually, except for those that SQL Server generates when you add primary and foreign keys. As a rule, I dont consider this method to be that valuable when you are migrating a complex application. The other methods we will discuss perform more of the grunt work for you. As youll see, this is very desirable. Access Upsize Wizard. Most likely, youll use the Access Upsize Wizard that lives in the Microsoft Access user interface. The menu option is in different places for Access 2. Access 2. 00. 7, but the function is the same. Below is a sample of the dialog box used to define the migration. Indexes, rules, defaults and primaryforeign keys can be included in the upsize process. This will save you a ton of work and its virtually fool proof. Ive performed dozens of these upsize actions and have never had an issue. Notice in the bottom half of this dialog that you can choose to assign a TIMESTAMP field to all tables. Should you do this Unequivocally yes Access does this funky thing with SQL Server when a table has a memo field. To determine if a user has changed a record while you are editing it, the values in all columns are compared, including memo fields. But because of the nature of memo fields, Access throws up its hands and says, Im sorry, I just cant tell is another user has edited this record or not. Gonna have to drop your changes. The only way around this is to give the system a better way to see if a record has been updated. Thats what TIMESTAMP columns do. You dont need to expose it and in fact, you cannot edit it. The column only needs to be there and may be named how you like. Its mere existence solves the multi user error issue. Next, the wizard asks you how you want the application to interact with the newly created SQL Server database, if at all. I usually elect to make no local changes and create a new, empty, pristine MDB shell into which I import all the forms, reports, queries and modules I need. How can I clone an SQL Server database on the same server in SQL Server 2. Express It turns out that I had attempted to restore from a backup incorrectly. Initially I created a new database and then attempted to restore the backup here. What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field. So, in short, restoring from a backup did the trick. Thanks for all the feedback and suggestions guys.