MS. Access to SQL Server 2000
-
I am task to move access database to SQL Server. In the access database ther are queries that are generated by access.
What is the easy and best way to have these queries move to SQL Server inform of stored procesdures?
-
Maybe code out there, haven't followed that market. Updates have a slightly different format, I think Access allows Delete * (sorry been a while). It's a fair amount of work, you not only have to build the procs and get the permissions set, but then convert your code to call it. You might get better advice on an Access site, but I'd recommend you get the data out of Access and into SQL first, then slowly migrate to procs. Moving the data fairly easy. Also, not sure to what extent the migration wizard might help with the procs, if at all.
Andy
-
I too have the same issue, and have not been able to find an easy way to do it, short of changing the queries to SQL format in the design of queries and checking to see if they run in query analyzer then deciding how to best recode or execute them in SQL server. I agree, that SP's or even VIEWS, for select queries are the best.
HTH 🙂
------------------------------
Life is far too important to be taken seriously -
I have been doing this sort of thing professionally for the past 3 years and its a big job, there's no quick way to do it properly. If you opt for the quick and dirty solution of linking tables by odbc then renaming the dbo_XXX back to the original table names so your original queries still run in Access you will get a bad result. You must re-write queries on the server using a combination of stored procedures and views in the most approriate ways for efficient data handling, keeping large data-manipulation on the server. This will take you through parameter passing to stored procedures, temporary table manipulation, updateable views etc. A big application can take months to convert. My latest conversion resulted in 20000 lines of server code (TSQL - Stored Procedures and views) as well as the un-diminished Access VBA code and has taken nearly a year to complete. The upsizing literature makes it sound like a simple conversion process - but to do it properly, it is not, its a re-build.
-
Hi malb,
quote:
I have been doing this sort of thing professionally for the past 3 years and its a big job, there's no quick way to do it properly. If you opt for the quick and dirty solution of linking tables by odbc then renaming the dbo_XXX back to the original table names so your original queries still run in Access you will get a bad result. You must re-write queries on the server using a combination of stored procedures and views in the most approriate ways for efficient data handling, keeping large data-manipulation on the server. This will take you through parameter passing to stored procedures, temporary table manipulation, updateable views etc. A big application can take months to convert. My latest conversion resulted in 20000 lines of server code (TSQL - Stored Procedures and views) as well as the un-diminished Access VBA code and has taken nearly a year to complete. The upsizing literature makes it sound like a simple conversion process - but to do it properly, it is not, its a re-build.certainly you are right.
And hopefully this is an inhouse application, for an external application for client built in Access is, well, let's say, 'suboptimal'
Are you using any tools when moving to SQL Server?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
wouldn't taking the SQL from the queries and creating them in SP's and Views be a good start. It would depend on the complexity of the prog. If it is a client tool and not internal proggy then sure you are talking total conversion, if it's internal then converting select queries to Views and some basic update queries to SP's would be a good start ? Ive sorta already started doing this, and am about 2 months into seriously doing it...
------------------------------
Life is far too important to be taken seriously -
I would start by using the Upsizing wizard
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Transferring the data can be straightforward. I usually find that transferring it to working tables in exactly the same format as the access tables and then using t-sql to transfer into the final destination tables works best.
With regards queries/sp's, the access queries are often in need of a total rewrite anyway, and you will probably find that there is a lot of redundancy in the access queries.
Also many access developers use queries like sql developers would use views so that you will find queries that use other queries for their data. So you need to assess:
o sp candidates
o view candidates
o redundancy
All in all the data can go in batch the data access needs to be analysed and hand-cranked.
-
Having performed many of these conversions, I can provide these guidelines that follow the path of least resistance:
Before you do anything at all and before you promise anything to anyone [i.e. 'sure i can do it - piece of cake']:
Using vb from vs, write a small routine that will grab all of the queries [querydef info], order them by type, by name, and that will then write them to text files in quantities of 10 each (manageable). [or if you want to go one better put them in a table]... Have the app put different types in different directories - one for select, one for update, one for insert, one for delete....have the app add the code necessary for sql server to recognize them for what they are, when possible (for example for SELECT querydefs, have it insert the value [CREATE VIEW myAccessQueryName AS] ...yadayadayada...
have the app look for ms access keywords in the queries that don't work in sql server (there's a buttload), and mark those queries as suspect with a sql comment above each query in the file {you can create a naming convention for each type of problem you find}, cuz you'll need to change/fix them later...
Now, if you end up with 200 suspect queries out of 200 queries - you've got a big job ahead of you and you need to advise appropriately
If you decide to accept this mission, proceed in this manner:
1. Use the upsize wizard to create the tables
2. Move the data with DTS
3. Check the data with a fine-tooth comb after you move it (types, concats, strips)
4. open your auto-generated select query files in qa and start running them against the server - they're now in quantities of 10 so that it's manageable to track errors and inconsistencies between access and sql server
...
...
at this point in time, you have created the db, moved the data, automatically generated many/some queries, created views from those auto-gen queries, generated a list of queries that need to be fixed...
the advantage of this method is that you are auto-generating as many queries as possible, so that interrelated and correlated queries that get moved can use them.
your mission now, jim, will be to go fix those suspect queries - which can be a big job or a project-condemning nightmare, a career-changing mistake, or possibly a cakewalk if you're real lucky.
this msg will self-destruct in 10 seconds....good luck, jim.
-
Hi a5xo3z1,
I use no tools other than Query Analyser and a small stack of TSQL books from Amazon for the application programming. I agree that the Upsizing Wizard to get the initial data across is the first thing, that takes about a day, then the real work follows. You can copy some Jet Sql across to begin views in Sql but if the Application is at all complex you soon need the sort of computational ability you use in VBA building temporary tables and the like to be migrated to the server. You then find you need Stored procedures to do the work. To get updatable recordsets you need views. For datalists etc on Access forms you need ODBC Passthru queries, and you inevitably need to call Sps from VBA using ADO or equivalent. You will be faced with providing Access frontend access to a Production and Development servers and databases so you will need switchable connection strings and ODBC linkages. The list does go on a bit. But if you start small and work up to it wou will find it all comes together and, if you program it well, taking all efficiency stategies into account, you will be really rewarded. Your application will really go like a rocket supporting huge amounts of data, then you will wonder why anyone sticks with an Access backend. The Access frontend can work very well with an Sql backend if you get the techniques right. Best wishes. malb
-
quote:
Hi a5xo3z1,I'm seriously considering to change my username to something more meaningful !
My question was just out of curiosity. I'm gald to be beyond the stage where I used Access as front-end. Today I only use Access as quick and dirty data manipulation tool.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
malb - you sound like a seasoned pro that has dug thru the dungeons of microsoft hell and arrived, triumphantly, at the gates of the dark lord himself and shouted: "I will not go quitely!"....
anyhew....one more thing i forgot to mention and it is all-important in this day-and-age of budget slashing - if you are re-using the access app and you find a lot of sql embedded in the app - by all means leave it there, especially if it's odbc calls...much less work for you to do. for those purists out there: just cuz the sql's in the app doesn't mean you're killing the efficiency - especially in an access app - cuz chances are it was small in nature to begin with [famous last words]
which brings up yet another point - i would say that 50% of the conversions i've done have been for one reason: the use of the access app was becoming so great that it was slowing down. so, you might want to consider re-writing the whole schlamele from scratch in vb if that's the case.
lata.
-
"...so, you might want to consider re-writing the whole schlamele from scratch..."
Best advice I've heard in this whole thread!
I've only done a few of these conversions and only one was an Access app written bya "professional". On every single one I've monkeyed around for ages trying to kludge things together so that it works the same way. Then I grab myself by the neck, give myself a good shake and swear I'll never do this again.
After that I look at what the Access application is actually doing and what the client really *wants* it to do. Then I can create an application using SQL Server and ASP that does the same thing faster and provides greater flexibility. (With less headaches!)
Of course, for those really big Access apps... Bleah!
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
quote:
Of course, for those really big Access apps... Bleah!Back to the time when I was programming Access 97 'professionally' we had the GUI and source code db at ~30 MB. That causes really funny issues at the customer. And every time we said to them, we are about to rewrite the app.
What happened??
Well, I don't know the end. I left after some 2 years of those false promise.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi all,
After reading all the posts in this thread
I think i might have to restart the project i working on. I have about 6 Access apps all at about 20mb each, That i need to re write in SQL / vb.. I think i have taken the kludge approach, which is something i dont usually do, After viewing the responses i think i will re work how im going to convert these apps. Thanks heaps to everyone who had input into this thread.. Ive certainly learned something... looks like ive got a solid couple of YEARS ahead of me !!!
------------------------------
Life is far too important to be taken seriously
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply