Migrating Microsoft Access Databases to SQL Server 2. Database. Journal. KkrqHUY/T8Tbcg5-vOI/AAAAAAAAAFw/oUHa3LYyLew/s1600/DeepSouthReport.jpg' alt='Microsoft Access Front End Oracle' title='Microsoft Access Front End Oracle' />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. Microsoft Access Front End Oracle' title='Microsoft Access Front End Oracle' />Microsoft Mobile was a subsidiary of Microsoft involved in the development and manufacturing of mobile phones. Based in Espoo, Finland, it was established in 2014. Oracle acquired Sun Microsystems in 2010, and since that time Oracles hardware and software engineers have worked sidebyside to build fully integrated systems and. Technical articles, content and resources for IT Professionals working in Microsoft technologies. A front end developer whos frustrated at not having the skills to code the backend of your application, always reliant on others or 3rd party solutions that never. Cost, however, is not one of those advantages. 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. 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. Microsoft Access Front End Oracle' title='Microsoft Access Front End Oracle' />Whats the Maximum Number of Microsoft Access Users There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Azure offers many pricing options for Linux Virtual Machines. Choose from many different licensing categories to get started. 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. 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 InternetWith an MDB file, this is simply impossible. 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. Sap Controlling Month End Activities For Kids. 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.