Where do I go next? (Migrating to SQL)

[scotthutchings]

I am not sure where to go next...

Over the past year I have developed an Access FE/BE database that has exceeded the original design and scope. As a result, it has slowed dramatically. When it is run on a local machine, it works great. When it is run over a LAN, it works but several of the forms and reports run way too slowly. When is it run over a WAN, it essentially doesn't work anymore (unless you have 20-30 minutes for each step).

Here's what I have done: I have successfully installed MSSQL Express 2008R2 on the server and have connected successfully. I have tried to use the SSMA tool to migrate my BE Database to SQL. This seems to work but I don't know how to use the BE (how do I link my FE to it and how do I get my queries in the FE into SQL?) I have also tried to combine my FE/BE into a single database before I migrate it, which appears to work, but the front end doesn't run.

I am not sure even where to turn... Essentially I need some step by step help understanding how to migrate my database and how do I use the migrated database. I would love to find someone with experience in SQL/Access to help me.

Does anyone have any idea where I go next?

 

[Banana]

See if this overview helps?

 

[scotthutchings]

Thank you for this lead. Quite frankly I'm a little nervous to see how many of my thousands of lines of code don't run. But, before I get to that point, do you have any recommendations or suggestions on a how-to step by step - especially once I get my database moved to SQL (which I have done) how do I link my existing FE database to it? I know I am suppose to use the External Data -> ODBC connection but when the "Create Link To Data Services" wizard comes up, I'm not sure what to do with it. For example, the first questions is to specify the link name. What is this?

 

[gemma-the-husky]

before you do this - I don't think moving to SQL server is likely to fix the problem with the non-local users. you probably need a terminal server solution for this.

out of interest, how big is your dbs, and how many users do oyu have. you may find that upsizing costs you a lot, and doesn't real help - and that what IS needed as some application redesign.


Banana's link is very enlightening though.