Link to home
Start Free TrialLog in
Avatar of ernest_barton
ernest_barton

asked on

Access 2000 to SQL Server 2005 Express help

I work for a small company that has used an Access 2000 database with about 50 tables and multiple forms for accessing this data. The volume of the company is getting larger and the clients want to have web access so I am being asked to investigate this.

They want to do this over the next year in various phases. They first want migrate all of their existing data into an SQL Server 2005 database. Then connect their Access 2000 forms to the SQL Server 2005 database. They eventually they want to start building web pages to connect to the database for clients to connect to. Can Access 2000 forms connect to a SQL Server database? I have the access database and forms on my desktop. I want to download express to try some things out. Is this all I need to install?

I am very much a newbie a this and very basic instructions or thoughts would be very much appreciated.
ASKER CERTIFIED SOLUTION
Avatar of adraughn
adraughn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ernest_barton
ernest_barton

ASKER

Thanks for the great tips Adria! I will definately be getting that book. We are going to be using the express version of SQL 2005 to do a "proof of concept" to make sure we can do this without major impacts on the business. It is a small company and cant afford big time consultants so we are going to try this ourselves and run in parallel for a while to make sure all is OK. A couple of quick qeustions...

- Is the migration tool you are talking about the SSMA Access tool? Since we are using express, I dont think we have the full tools.

- You mention linking the SQL tables to Access. I may misunderstand this answer. Once the data is migrated, we want to link our forms to the SQL 2005 tables. This si the part that is most confusing. How do I point my forms to the new database? Is this done automatcially as part of the migration?

Sorrry for the really dumb newbie questions...
There are two tools. The SQL Server Migration Assistant for Access and the SQL Server Upsizing Tool for Access. I couldn't get the Upsize Tool to work for my database. I tried the SSMA tool but it was easier to bring in the table designs mannually then figure out all of the errors from SSMA.

When you get a full version of SQL Mgmt Studio, right click on your db, go to Tasks - Import Data. Import your tables from Access.
Avatar of Jim Dettman (EE MVE)
adria,

 Nice answer!  I would just add that unless you willing to do #4, your not going to get much benefit. The whole point to client/server is to get the server to do as much as possible.  Also, I would add a #5: may require redesign of the app.

 Client/Server requires a different mind set when developing an application.  For example, you'd never bind a form directly to an entire table.  

  Often when moving to client/sever, you find that you end up basically reworking the entire app.

JimD
ernest,

jim is right. we highly recommend migrating your access queries to sql views and stored procedures. i'll explain why later on in this message.

as far as your question regarding forms, it can be handled in a number of ways. It really depends on the form. You'll have to post back when you make progress and tell us specifically what the form is used for and post your table design.

the thing with sql is, you don't want to use the same design you had in access. you see, if you have a form that normally opens with all records available so that the user can search - when you link to your sql data, if that form is still setup the same way, you have to pull all of those records over the network. it will kill your performance.

here are some tips in pulling data:

1. generally, when using sql for your data, you want to open your forms with data entry set to yes, so that they open empty. this eliminates the need to pull all records from the server. You want to pull as little as possible - only what you need. To do this, you will need to use views and stored procedures (or dynamic sql statements)

2. you can use a search on your main menu (please do not use the switchboard manager). post if you need help in creating a custom main menu. it's easy. you might even find prior posts with instructions. if you use the switchboard manager you will regret it later. it is limited in what it can accomplish.

3. you can also create a search on your form in the header section that will find records. when you get to that point, post back and we will help you. it's easy to do with a combination of option buttons, text box, command button and a little code.

4. all of your "drop down" menus should be changed. say the user selects from a list of employees. the list of available employees used to have a local table as a data source. you want to change it to a pass thru query. pass thru queries pull data straight from the server, so they are much faster. there is one drawback - they are read only. which makes them great for combo boxes. again, let us know when you get to this point and we will assist you. it would not make any sense for me to post instructions until you have data on the server to use.

5. as far as linking tables, in general, you do not want to pull in an entire table as source data. but if you were to link the tables to reference in your access queries until you migrate them to the server, follow these instructions:

a) From the tables tab in access, click on new - link table
b) Change 'Files of Type' to 'ODBC Databases'
c) Click on 'New' on the Select Data Source tab
d) Scroll down in the listbox and choose SQL Server as your driver
e) Type in a data source name and click Next
f) Click Finish
g) Type in a description for the database and select your server from the drop down or type it in then click Next
h) i would suggest using NT authentication, but it's your choice - make it and click next
i) click on 'change the default database to'. you don't want to store your data in master. you will need to create a database on the server specifically for your database. Once you have, select the database name from the drop down.
j) Click next - click finish - click Test Data Source
k) Click OK and then OK again
l) Click on the .dsn file that you just created and click OK
m) a list of tables will appear that are in that database on the server. select the tables that you would like to link from the list and click OK
n) you will want to open the table in design view and check the primary keys.

you would link a SQL View the same way, except it will prompt you for a primary key. Note that if you do not provide a primary key for a view, it will be read only.

oh, you only have to create the .dsn once. if you want to link more tables/views, you just use the .dsn that you already created.

once you have your tables linked, notice that they come in as dbo_tablename. You will need to rename the tables as tablename, removing the dbo_. Then you can delete your access table (hopefully a link to a table if you have a split db) and your queries will work with the sql table.

If your access tables are not links - please make sure that you copy your db before making these changes. It is probably a good idea to back it up anyway.

TEST, TEST, TEST before going live with SQL data. Like I said, just linking your data to SQL without changing any of your queries is going to DECREASE performance greatly. If you need help migrating your queries, post back.

I hope this helps. Just be patient and spend more time then you would like working on your table designs and data types in sql because this is the best time to do it. i would suggest posting your table design on ee for comments.

Once you get everything setup and running smoothly, I think you will really enjoy using SQL backends. It gives you more stability and better backups. (to mention a few). Good luck with your project!

adria