Upsizing from MS Access? 3

[MichaelRed]

Having read many of the suggestion, some directions and a few horror stories, I am about to start the oft delayed process. To this end, I have a few(?) questions:

Do we really move ALL tables (even the local ones) to a seperate BE?

(Assuming Yes to the above), SQL server is advertised to be able to know which tables are temp and allocate these differently (so multiple users can run the same processes w/o interfeering with each other). However I can't seem to find / figure out how these temp tables are identified by / to SQL Server. Who can explain this (even if via reference to other documentation).

Is it preferable to move all queries to SQL Server? Even the litbox, combobox, ... rowsource queries?

If 'all' Tables and queries are sepweated, does the upsize whizzz do a beter job of conversion (esp issues like data typing? and syntax)?

If I can identify the procedures included in the Queries, can the upsizeing process utilize them to 'ease' the process? Will Mr. UpSize do anything with procedures?

What will Mr. Upsize do re Indexes? Are they just 'replicated'? Is there some part of the process which examines them and remove those which are not used?

If Mr. upsize doesn't 'review' and correct the indicies, are there any useful guidelines to doing such as review from within MS Access to help remove un-useful ones?

What (other?) perils and pitfalls are most likely to cause trouble.

PS this is cross posted in the MS Access Other Issues forum

Thanks,


MichaelRed

 

[RiverGuy]

This doesn't answer your specific questions as I haven't used Access in years and have never used the upsizing wizard, however I will tell you that if this was my project -- I would not rely on a wizard to build an efficient SQL Server database. I would probably start completely from scratch, creating the tables/indexes/views/store procedures/functions/schemas, etc, all the while using SQL Server best practices.

 

[gmmastros]

faq183-2935
faq183-7072

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

This doesn't answer your specific questions as I haven't used Access in years and have never used the upsizing wizard, however I will tell you that if this was my project -- I would not rely on a wizard to build an efficient SQL Server database. I would probably start completely from scratch, creating the tables/indexes/views/store procedures/functions/schemas, etc, all the while using SQL Server best practices.

If the Access database was poorly designed in the first place, I would agree here. But it's not very practical advice if the goal is to keep using the existing Access front-end (which is usually the case if someone is considering the upsizing wizard).

Assuming the designer of the Access database was skilled in relational database design and did a good job, then using the wizard in my opinion would be a good choice, both from a design and business view.

From a design view:
*******************
1. Access and SQL Server are both relational databases. If you set up the relations in the Access database correctly, it will be exactly the same structure in SQL Server.
2. The same generally goes for indexes, constraints, etc. - if they were the right decision in Access, then they are most likely the right decision for SQL Server
3. As for choosing data types, I have found the Upsizing Wizard to be very good at matching up the Access data type to the suitable SQL Server equivalent.

There is of course some tweaking that needs to be done afterwards. But I would say that the wizard usually does 95% of what I would have done anyways. And the 5% fixing up is much, much less time then it would take to do everything manually. I dare to suggest that during a manual conversion you will make more than 5% transcription errors - you will spend more time correcting your own mistakes than the wizard would have made.

From a Business View
********************
1. The main argument here of course is time. Let's say you are going to upsize a database with 100 tables. The wizard is going to do that in a few minutes. How many tables can you create manually in a few minutes? And yes, you will have to correct some mistakes made by the wizard, but I maintain that you will make just as many (and probably more) transcription mistakes.

2. Let's keep in mind the business goal here - we want to keep our Access front-end intact, just move the tables to SQL Server. So redesign is not really an option anyways, because changing the schema breaks the interface to the front-end.

In the end, wizards are not automatically evil, as long you don't rely on them to make all the right decisions. Let them do all the tedious work, and at that point apply your expertise.

 

[vb5prgrmr]

It's been a long time since I have used the upsizing wizard (Access 97 which I think had to be downloaded from MS at the time) but I can say this about it.

It exported a few million records from a single specific table along with nearly a million records from the other 30 or so tables and the only problem I had with it was it would not export a single index from the main large table, which was easy enough to fix via SQL (7.0 I believe at the time might have been 6.5).

Also, I agree with JoeAtWork, however, let me say this. When I had started this program in VB6.0 (maybe it started in 5.0), it was origionally intended to go from Access to SQL (Access for quick design and portability between site and office, SQL for testing through production and release). So with this in mind I had originally designed the data access with RDO (yes I know ado was out at the time but ado was a wrapper for both dao and rdo and so was slower than either or both including dao odbc direct) and ODBC and the only problem between the two databases was in the way each handled dates or should I say the vb code needed to be just a bit different between the two, but that was easy enough to work around.

One other note: The speed improvement between access and sql was night and day. There was no comparison between the two and using access as an middle step will only help keep things slow (at least the one time I tried it, it remained slow and may you have better luck).

Good Luck

 

[MichaelRed]

Thanks to all but particularly JoeAtWork and vb5prgrmr, as my interest is in specifics of the process.

I am more-or-less between the proveribal places. The current app is between 10 and 14 years old and has been throough at least five programmers. Almost NO removal of obsolete objects has been attempted, and it was general practice to develop on a copy of the working db, and simply replacing the FE with all changes when it was considered to be 'tested'. This has resulted in approximatly 2200 top level objects (Forms, Reports and a few queries).

I do not want to even consider a wholesale redesign.

Eventually, the entire app will be recast as a .Net FE contraption using SQL Server as the BE. In the meanwhile, it is slowing to an absoloute crawl and I want to switch the BE to SQL Server. This will (should) improve the response and thereby releve some of the angst about the performance.



MichaelRed

 

[vb5prgrmr]

>I do not want to even consider a wholesale redesign.

>Eventually, the entire app will be recast as a .Net FE contraption using SQL Server as the BE.

Military Intelligence? (i.e. meaning if you are porting from 6 to .net then it will basically be a redesign from the ground up)

>In the meanwhile, it is slowing to an absoloute crawl and I want to switch the BE to SQL Server. This will (should) improve the response and thereby releve some of the angst about the performance.

Does your app have a compact or repair database method that it uses regularly? A short term solution to solve your performance may be the CompactDatabase Method and the RepairDatabase Method.

Search your MSDN that came with vb6 for "CompaceDatabase Method" and select "Microsoft DAO 3.51" and see the example. Then under the See Also link select RepairDatabase and see its example.

Since this is the SQL forum...If you have any questions about these methods goto the vb5-6 forum222

Good Luck