Best way to set up SQL DB Backend?

[travismp]

I have a very large DB. 250MB with 500,000+ records all in Access. I want to convert the backend to SQL. I have around 20 users that access the DB. I want to keep the front end in access because of the amount of queries, forms, reports, macros ect… what is the best way to attack this with SQL? Thanks.

Which is the better way to go:

Making a ADP and converting all queries and tables to SQL

~or~

Creating ODBC and just converting table. Leaving all queires alone and in the front end?


T~

 

[llkhoutx]

Creating ODBC and just converting table. Leaving all queires alone and in the front end?

should be

Creating ODBC and linking the SQL SERVER table(s). Leaving all queires alone and in the front end?

You'll have to rename the linked SQL SERVER table(s) to their original MS Access name. A linked SQL SERVER table has a different "Foreign Name."

Take a look at the MSysObjects table and the entries for the SQL SERVER table(s). Note particularily the "Forgeign Name" and the "Name" columns. This table is read only. To change to name of a linked table, select in in the Database Window and press F2, then change it to be consistent with yuour exisiting queries.

To display the MSysObject table, click Tools > Objects > System Objects (on the View tab).

 

[travismp]

OK, I can do that. Thank you for the help so far. Which way is "Better"

 

[llkhoutx]

I have no experience with ADP, but heavy experience with SQL SERVER and Oracle backends.

Put all tables that will be queried or joined in queries in your backend so that they will be executed in the backend. Access local tables joined with back-end tables often take a very long time to run. Some just won't run in days.

 

[travismp]

I was thinking about making my talbes (5) in SQL and leaving all of my queries, forms, and reports in Access. Is this a bad idea?

One guy told me to do ADP, but I just do not have a good feeling about it.

If I go ODBC backend do I need to create the ODBC connection on each and every computer?

 

[pbaldy]

I know of at least 2 Access MVP's that recommend against ADP's. IMHO they're not worth the bother. I'd just stay with an MDB with the SQL back end.

You can create a File DSN on the server and link with that. Then you don't need anything set up on each PC.

 

[travismp]

Paul,

thanks that sounds great. How do I make a "File DSN on the server and link with that"

 

[pbaldy]

Instead of creating one on your PC in the User or System tabs, I create one on the server and store it in a shared folder. Then when you link tables in your MDB, navigate to that instead of the User or System choices.