SQL Server Performance Forum – Threads Archive

Downscaling SQL-server db to ms-access

Hello Gurus, I have a problem downscaling my SQL-server 2000 database to MS-Access Jet. To transfer the tables is no problem, I just import them in Access. The problem lies with the views (I do not care at this moment about the Stored procedures of triggers, just the views). The reason for downscaling is that on some of my clients computers installing a SQL-server is not allowed. But I developed the whole enchillada on SQL-server… So, I have to somehow reverse engineer the whole thing. I looked everywhere on the web for conversion tools, but did not find one that has this capability. Does any know of a script or tool that can help me out? ps. I have quite a lot of views in the database, many of them with CASE statements in them, which are not supported by MS-Access. Hope you can help me.
Emphyrio

Before thinking of converting to Access, would the use of the free MSDE be an option? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Hi Frank, Thanks for the suggestion, but unfortunately using the MSDE is not an option neither. The customer specifically wants MS-Access (how do you convince people that have no clue…?) Hope there is another way, except manually going through all the views. Regards,
Emphyrio

Yes, I know this kind of people. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] AFAIK there is no tool for such a conversion, but you might want to try and ask this in an Access community. Typically we deal here with the other way round, so chances are that some Access geek knows a tool we’re not aware of here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Classic case of ‘the customer is NOT always right’.

Ok guys, thanks for your help. Let’s see if I can find an Access freak who solved this problem…. L8er,
Emphyrio

Tell the customer that MSDE is freely available [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing.</center>

Does the client know you can create an ADP in Access that connects to an existing SQL Server database? Depending on the logon, you get to see all accessible objects in the database. You can bind forms and reports to tables, views and sprocs … and still be able to use VBA for a lot of things. – Please note that there is precious little known about the ADP, eventhough it’s been part of Access since Office 2000. Seems like it isn’t really being used out there. Other than that, you can actually run most DDL statements (SQL 92) in Access, using DoCmd.RunSQL (using OpenFile in VBA to access the script that you generate from SQL Server) or cutting-and-pasting into the SQL View of a query object in Access. You may need to remove some features from the script, especially with SQL 2005, so review any error messages you get for each statement. If the SQL database only contains tables and views, that should be fine. Once you get to sprocs and UDFs, that’s when you will need to create replacement code and objects in VBA. In the end, the simplest tool would be what-used-to-be-called DTS (SQL 2000) where you export all tables to an MDB. Create an empty MDB beforehand – as follows! Be sure you understand Access security – it is there, but there is a wide open backdoor that you need to close: (1) Make a copy of the "System.MDW" file from the Office installation folder, and give it a new name. Move it to your project folder. (2) Create a shortcut with this command line: "C

rogram FilesMicrosoft OfficeOfficemsaccess.exe" /WRKGRP "project_folder
ew_workgroup_file". (3) Launch Access with the new shortcut. (4) Go to Tools>Security>User and Group Accounts, and create a specific login that will be your "system administrator" or sysadmin. (5) Close Access, and add the /USER sysadmin switch at the end the command line of the shortcut. (6) Launch Access with the shortcut. Go to Tools>Security>User and Group Accounts. Click on the Change Password tab, where you will see the name of the sysadmin printed below. Set a password for your sysadmin login. Note that this password is not the same as the "database password" option elsewhere in the Security menu, which is a nuisance when it is set – so don’t go there. (7) Close Access again, and launch Access again with the shortcut. The system should now prompt you with the sysadmin login, asking for the password. If you messed up the password, you can use the "Admin" login (the wide open backdoor I mentioned) and go to Tools>Security>User and Group Accounts. From the list of user names, select the sysadmin login that you created, and press "Clear Password". Close Access again, and launch Access with the shortcut (it should open without asking for the password, because you’ve just cleared that). Repeat steps 6 and 7, and continue with step 8. (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Create a new MDB file. This will open the database window of the MDB in Access. Go to Tools>Security>User and Group Permissions. (a) For the "Database" object, grant all permissions to your sysadmin login. (b) For the "Database" object, remove all permissions for both the "admin" login and the "Users" group. (c) Select each of the other object types in turn, and grant all permissions to your sysadmin login for the "<New …>" object. (9) Go to Tools>Security>User and Group Accounts. Create "groups" corresponding to the database roles that your SQL Server database had. Go to Tools>Security>User and Group Permissions. (a) For the "Database" object, grant "Open/Run" permission to each new Group. Do not grant "Open Exclusive", otherwise you’re turning it into a single-user database. (b) Select each of the other object types, and grant the appropriate permission(s) to each new Group. Note that there is no equivalent to the DENY option in SQL Server. If you are running a non-English version of Office, the "admin" login and the "Users" group may be shown with a different name. The backdoor problem is that all workgroup files in existence have the "admin" login and the "Users" group. If you grant any permission to that login or that group, then anyone with Microsoft Office will be able to access the data in your MDB. When exporting to the new MDB file, you must now add a reference to the new workgroup file, probably referred to as "SystemDB" on the advanced settings, and provide the sysadmin user name and password. When distributing the MDB file, you also need to give users a shortcut that refers to the correct workgroup file. You may also want to add logins, and make them a member of the Groups that you’ve created. Have fun!

Adrian
Thanks for the extensive information on MS Access, I have known few gaps from your reply. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

This sounds like a future issue ofhttp://thedailywtf.com/ The fact that the customer should have any say in the RDBMS foundation of your application is true management BS. My suggestion to you is : Run away, or ask rediculous amounts of money to do the job. Access jet: Disadvantages:
– Does not support SP / Triggers
– Indexes are a nightmare to manage
– You cannot access the database in a client-server-fasion (In a multi-user scenario, users will access the physical data file over the network). Advantages:
– the GUI applications you can make in Access are fast and reliable
– Applications are easy to deploy By using Access 2000, you have the option of actually running MSDE in the background. Even without telling your customer. Good luck with the job at hand, regs, Mads "Follow the join tree" – Dan Tow

Everyone, Thank you for all the suggestions. I’m very aware of the pro’s and con’s of MSDE <=> Access Jet. Wish my customer was also… I’ll get it done, and the customer will be happy (I hope)… regards,
emphyrio

Mads, Have mercy on people trying to cut down on immediate costs! Access not supporting sprocs and triggers – strictly speaking it doesn’t. But it doesn’t mean that you can’t setup a system that is nailed shut, and that can mimick triggers with the form events. Stored procedures – well, that’s what VBA code is for. And if you understand set-based operations, there is nothing you can’t do in VBA (stay away from ADO, though – unnecessarily complex – use good old DAO). The index nightmare … not sure what kind of problems you’ve run into there? Nothing much to manage, except the occasional Compact operation. Client/server – correct, it doesn’t do that. However, it does do front-end/back-end pretty nicely. If the front-end crashes, you may end up with corrupted indexes in the back-end, but these can be repaired one way or the other. And did I mention the costs?

Adriaan I agree that "it can be done" in Access. Having spent a good part of my junior-developer years doing it. However, it worries me, that Access in the year 2006, is at all an option, for any kind of solution, anywhere (besides for personal database of LP Vinyl records). In this web-based, client-server/thin client/webservice world, it sounds like the customers of the unfortunate Mr. Emphyrio, are stuck in the mid 90’s. regs, Mads
"Follow the join tree" – Dan Tow

Mads, Even in large-scale enterprises, people do not always have the budget for an enterprise-level database. Or if they have, it might still be overkill to use it for a small database. If SQL Server is already up and running, and they can just add another database to that, then that should be the preferred option – but it will not always be an option. And finally, not all systems on an enterprise-level database platform are developed equal. The platform is there for security, safety and stability, but you can have good and bad design on any platform, be it SQL or Access.

]]>