March 5, 2008 at 1:17 pm
Hello everyone,
I'm new to this forum and was wondering if I could get some help with this question. I am not a SQL person at all, but I guess learning it can only be a plus. I am consulting somewhere and my supervisor has asked me to the following:
"Research a Microsoft wizard for migrating MS Access data to SQL 2005 Server. After migration, we need to continue to view SQL database thru a viewer or Access."
I tried using the Microsoft SQL Server Migration Assistant for Access (SSMA) on a test database that was created for me and it seemed to have copied over a whole bunch of stuff but I dont know what to do next.
Any help would be greatly appreciated.:D
March 6, 2008 at 10:23 am
I am not sure what you need at this point. If you are looking for a viewer, create an Access adp to connect to your SQL database.
March 7, 2008 at 7:23 am
I would start the process with the Access upsize wizard utility. Since they want to end up with an Access viewer, the upsize wizard wizard will give you two options.
1. Create a New Access ADP file
2. Use the current Access db to fron sql data via ODBC linked tables
April 24, 2008 at 6:28 pm
you are better off using the SSMA [for Access] wizard , rather than the Upsizing Wizard embedded in ACCESS. Search microsoft.com for SSMA.
The reason to use SSMA is that it is more reliable and when it fails it gives you enough information to figure out why and what to do. the UW is much more failureprone and provides little help. It's limitations seem to be why SSMA was created by the SQL Team.
Once you have the tables and queries moved, you have the option within Access of either creating an Access Project (.adp file) or simply linking to the SQL database container.
Guidelines: A project gives you access to meta data in the sql engine such as relations, index defs, etc. HOWEVER, it requires that ALL data objects must exist in the SQL engine. No tables can be created or used on the ACCESS side. It should be noted that 'Access Projects (.adp)' has been deprecated in ACCESS2007 which means that they are preparing to discontinue it as a feature. Word of mouth from knowledgeable Access Team folks has long advised that ADP is no longer recommended unless you have very specific need for it. It was intended to deal with latency issues over slow LAN and WAN connections. It is crippled relative to the alternative.
Using linked tables gives you the ability to build queries and reports on the Access side and you can augment the operational database with local tables (that you don't necessarily want to support in the SQL container) to augment your reporting. Just create an .mdb database and then establish a link to external data to the host sql instance and database. This will involve using ODBC middleware. Once linked, all tables can be viewed, queried, edited, etc. You can't change data structures from within this approach. However,...
All such advantages of the project model are much more than compensated by the features of Sql Server Management Studio [client app]. If you are licensed to connect to SQL, you are licensed to use that client.
April 30, 2008 at 11:04 am
Robert O'Byrne (3/7/2008)
I would start the process with the Access upsize wizard utility. Since they want to end up with an Access viewer, the upsize wizard wizard will give you two options.1. Create a New Access ADP file
2. Use the current Access db to fron sql data via ODBC linked tables
I'm about to do this, but need to understand the benefits of each one. (Going out on a limb here....I'm only interested in Tables, Queries, Reports and Forms to start....)
NEW ACCESS ADP FILE.
Benefits: All objects are stored on SQL Server in some manner (i.e. queries are stored as views.) Disadvantage: 1 needs to create a "front end" to access the stored Report and Form objects on SQL Server. As this is the case, what the tools to building the front end, .NET?
USE THE CURRENT ACCCESS DB TO FONT SQL DATA VIA ODBC LINKED TABLES: (I assume by using the Upzing Wizard)
Benefits: Tables and Queries are stored on SQL Server, however Reports and Forms are still contingent on Access. Disadvantage: Reliance upon Access for Reports and Forms.
As Access ( the JET Engine) will no longer be supported, is SSMA the way to go in the long run? I appreciate any help, thanks
April 30, 2008 at 3:43 pm
I should preface this with the admission that I am biased toward using linked tables via MDBs over using ADPs.
BE AWARE: this is a religious issue among some and my opinion is based more on the advise than experience. (My experience aligns with the advise). The source of that advise has been in the business of Access (&SQL) databases for years with enough business to support over a dozen programmers and has, IIRC, over 100 Access solutions in their portfolio. They've tried it all and this is where they've landed. Where needed, they use ASP/AJAX and therefore ADO. However, when Access is feasible, they go with MDB linked tables (which includes links to 'virtual' tables ie views) and stored procedures. And, for real performance gains, they use pass-through queries to complex reporting.
Some folks would argue that using ACCESS for forms and reports is the opposite of a disadvantage/liability. They provide a much richer product per the time involved in development (twice the coder productivity, at least, per function point). They might not provide all the features one is looking for, but that is a marginal and situation specific issue if at all. And, there are third party libraries available from several sources that can augment such limitations.
It can also reduce the complexity (and cost?) of the installation, compared to a web approach since you don't have to add a web server to the implementation.
The skill level needed to create a report or modify a form is lower which allows end users (of a certain capability) to support themselves to an extent that is much less plausible using ADP or ASP.
Also, using an MDB with DAO (vs. ADP/ADO) connections allows local tables to be utilized (in both forms and queries/reports) that you might not want included in the central database, allowing power users to augment production data with stuff (possibly sensitive) that you don't want to expose or devise special protections for on the SQL Server data store. It also allows lookups from relatively static lists to be hosted locally (updated automatically when needed) which enhances performance and reduces network traffic.
It is possible/recommended that the DSN-based ODBC connections be replaced by 'DSN-less' connections. Google that (or 'dsnless') for more information.
While we often think of the Access-based Upsize Wizard (UW) as the primary quick migrator, SQL has an import wizard, also. However, just yesterday, attempting to use it on a very simple ACCESS mdb, it failed to move all or even any records in several tables with no explanation.
Using SSMA in no way locks you into and ADP/ADO architecture. All it is doing is helping you SUCCESSFULLY migrate table and/or query objects from MDB files to SQL databases. It shows you what needs fixing to make them work in SQL as well as making the actual move.
How you connect to/use them is not determined by using SSMA. When you run it, experiment with different project configurations to see what works best for you.
May 1, 2008 at 9:45 am
My only recommendation as was stated above that depending on the data make use of pass-through queries for your data retrieval from an access front end. The problem with Linked tables is that if you need to join 2 linked tables together access will pull all of the records for each of those tables to the client before it does the join, potentially causing slow response times, clogging the network etc depending of course on the size of the tables you're querying and such...
-Luke.
May 1, 2008 at 12:30 pm
I have both ADP’s and MDB’s. The ADP’s have so many benefits that I have nearly finished converting all of my MDB’s to ADP’s. The benefits include a full integration with SQL Server including the use of stored procedures. Parameterized store procedures will make Access forms fly.
May 1, 2008 at 3:06 pm
If the query that is joining the tables is read-only (not to update either table), I think you would find that a pass-through query would allow the join on the server before returning results. This doesn't require ADO.
I've seen this demonstrated with an extremely contorted query (grouping, ordering, and filtering with correlated subqueries. The join running locally took over two minutes (pretty small record count as well). The pass-through took under two SECONDS!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply