 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / May 2005Guide for switching to .ADP from .MDB
Is there a general guide anywhere that would help me to weed out all the issues I am having? I have converted a previous database before with some problems, but it is now working and in production. I am now in the process of converting a much larger database (130MB .mdb file) that is loaded with code. It would be nice if I could find some common steps to go through. Ryan >Is there a general guide anywhere that would help me to weed out all the >issues I am having? I have converted a previous database before with some >problems, but it is now working and in production. I am now in the process >of converting a much larger database (130MB .mdb file) that is loaded with >code. It would be nice if I could find some common steps to go through. Rule #1 - ask why this ought to be an ADP rather than an MDB with linked tables. Steve, Good question. After struggling with the .adp for 2 days I have determined it will be too much of an uphill battle for this application. I have decided to go with .mdb with linked tables. The main issue we are having is performance with the shared .mdb so I thought .adp would be the best solution. Now the problem I am having is that 2 (of about 60) of the tables are not uploading. Unfortunately the wizard errors don't give me much information. Any ideas what could cause this? Table: PERSON PERSON Microsoft Access SQL Server Skipping table 'PERSON'... ESCAPE Microsoft Access SQL Server Skipping table 'ESCAPE'... Server Error 156: Incorrect syntax near the keyword 'ESCAPE'. Ryan >>Is there a general guide anywhere that would help me to weed out all the >>issues I am having? I have converted a previous database before with some [quoted text clipped - 5 lines] > Rule #1 - ask why this ought to be an ADP rather than an MDB with linked > tables. >Steve, > [quoted text clipped - 3 lines] >performance with the shared .mdb so I thought .adp would be the best >solution. Performance problems with MDB front-ends to MS SQL Server are rarely a result of the fact that the front-end is an MDB. The most common cause of performance problems is a lack of good C/S design such that large result sets are repeatedly returned from the server. >Now the problem I am having is that 2 (of about 60) of the tables are not >uploading. Unfortunately the wizard errors don't give me much information. >Any ideas what could cause this? Using an upsizing wizard is a pretty unreliable way of migrating tables to SQL Server. You almost allways want to create the table definitions yourself, so you can decide what indexes to have, whether to add a timestamp column, etc. After that, you use append queries to upload the data. This is not a problem with ADP vs MDB. If the number of tables is large, you may want to write your own program to analyze the schema of your MDB back-end and generate a provisional script for generating the tables on the server, then hand-tweak the script before running it on the server. Note - first script building the tables, then populate them, then add indexes/keys, then create relations. When populating tables, you may want to use an Access program to create a temporary copy of each table on the server without an IDENTITY column, upload the data to the copy, then execute a pass-through query to append the data from the temporary copy to the destination table with identity insert turned on. I know that all sounds kind of daunting, but it's the most reliable way to do it. Hi , If I am correct then you are opting for this option in migrating the MDB application "Create a two-tier application. (front-end/back-end application)". Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application. The main advantage of this migration method is a low transition cost because no modifications are needed to the front-end database objects.Also this approach requires very little application modification since the code is still using the Jet database engine. Users continue to interact with a familiar interface, and the SQL Server provides centralized data storage, backup and recovery options, a multi-user environment, an integrated security model, and other advantages. But the main disadvantages and the biggest drawback of this method are that queries are still processed locally, and a large amount of network traffic is generated. So ADP is the best approach on ecan opt for when migrating an MDB application from Access to SQL server. Regarding teh upload problem related to the two tables will the import data feature of SQL do the task for migrating the data from the MDB to SQL. I had done that in the same manner when the Upsize functionality failed to upsize 3 of the tables in an application. thanks sd_ >Hi , >If I am correct then you are opting for this option in migrating the [quoted text clipped - 13 lines] >generated. So ADP is the best approach on ecan opt for when migrating >an MDB application from Access to SQL server. This is a common misconception, and it is completely false. Access/JET will do its best to have processing done at the server, not at the client, and it does not gratuitously read entire tables through the network. This is also true when joining tables - Access sees that the connection strings for the links are the same knows it can have the server process the join. Pretty much, the only times an MDB front-end will not have the query processed by the back-end are when you ask it to do things that can't be done that way such as a where clause that includes a call to a VBA function. [...] >>But the main >>disadvantages and the biggest drawback of this method are that queries [quoted text clipped - 3 lines] > > This is a common misconception, and it is completely false. Unfortunately it is mostly false but not completely. > Access/JET will > do its best to have processing done at the server, not at the client, and it > does not gratuitously read entire tables through the network. This is also > true when joining tables - Access sees that the connection strings for the > links are the same knows it can have the server process the join. There are certain querys, mostly involving complex outer joins, which will not be passed to the server as a whole but will be splitted in serveral smaller queries which may return a serious amount of (unwanted) data to be processed locally by Access. The more tables you join the more likely is such kind of behaviour. This is an example of such a query: SELECT * FROM (tabelle2 RIGHT JOIN tabelle1 ON tabelle2.feld1 = tabelle1.feld1) LEFT JOIN tabelle3 ON tabelle2.feld2 = tabelle3.feld2 WHERE (((tabelle1.feldXY) = 'Kriterium')); Usually the best solution to that problem is creating a view on the server which does already join the tables and then query that view with the criteria from within Access. Best wishes Phil  SignatureAccess FAQs: http://www.mvps.org/faq/mvp_faq_links.htm#Access >[...] >>>But the main [quoted text clipped - 6 lines] > >Unfortunately it is mostly false but not completely. I'm aware of that, but I like to make the point as strongly as possible because otherwise, it does not seem to get across. I've seen -way- too many people either convert projects to ADP or reimplement -all- their queries as stored procedures for no good reason. In many cases, forces them to damage the integrity of their programs and make their UIs less convenient to operate. Yes, some Access queries will stubbornly refuse to compile for server-side operation, and using views for the join-logic is often a good work-around for that. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.