Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jczarni
Starting Member
2 Posts |
 Posted - 2004-11-03 : 14:22:35
|
| Im consulting a financial company on their Access database. It has a back-end with about 100 tables (some close to 100,00 records) and a front-end with all the Forms, Queries, Modules, and Reports. The last few weeks they have had to compact and repair the front end twice a week where before it was a bi-weekly thing.Im trying to sell them the service of moving everything I can to SQL Server and link it back to the front-end Access GUI, but Im not sure of everything that goes into this process and how long it will take - of course my client needs the process on paper with an estimate timeline/cost.I know I can DTS the database to SQL Server and then change the column datatypes (because i know they dont always port correctly). But I'm worried about the Access Queries, Macros, and Modules....My questions are: 1) What do I do about the Access Queries? Move to Stored procedures? How do I use them?2) What, if anything, can I do about Macros, Modules, and Reports?3) What other concerns should I have?Also if any of you fine people have done this sort of thing in the past please share your stories :) |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-11-03 : 14:53:52
|
We did this where I work about 2.5 years ago. My biggest suggestion: Do NOT try to mimic each query with a stored procedure and then mimic each macro as a stored procedure that calls a series of stored procedures.We actually redesign most of the interface into a series of web apps. We initially contracted some guys to do the SQL translation, but that is all they did. The result was SQL that ran horribly slow as it was really designed for access (with all of its limitations). Best example we had: - a 'macro' that consisted of several steps, where each step was a series of queries (upto 28 deep), was translated to SQL by these guys. It ran for 7 to 10 hours at the time (in SQL). I redesigned it about 6 months ago, and it is now 1 stored procedure and runs in under 15 minutes while processing about twice as much.Basically, before you start each function of the app, resolve what the final result is, and design the SP from the SQL point of view.My 2¢!Corey |
 |
|
|
jczarni
Starting Member
2 Posts |
Posted - 2004-11-03 : 15:04:35
|
| Thanks for the input Seventhnight. Once I translate a Macro or Query into a stored procedure in SQL Server, how do I use them in Access? |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-11-03 : 18:16:26
|
You create a pass-through query (its an option when designing a query i think), and put your call to the stored procedure there... or you could open odbc sql connections in vb i guessSorry - Its been a while for me  Corey |
|
|
|
|
|
|
|
|
ACCESS --> SQL Server: Questions