SQL Server and Access 2000

[sunnywink]

Dear all,

I am tasked to convert Access 2000 database to SQL Server. ( Access 2000 database in front end application and SQL Server database in Back end application) SQL Server is new to me and would appreciate some advice.

I have been reading up some information on the web and is confused.

- What is the difference between SQL Server database and Microsoft Access project .adp.
- What is MSDE? Do we require it in the designing of SQL database?
- Do we require SQL Server software to convert? If needed, which version is better?
- Can we directly upsize using upsizing tools in Access 2000? Are there any issue to take note of?

Are there any good websites where I can obtain more information.

Thanks all in advance.

 

[mkelly11]

Quick question to clarify...
Are you looking into which version of SQL Server to purchase or have you already purchased one?

1)Microsoft Access Project is similar to SQL Server in that end users can access and create databases on the Server and manipulate them as if they were using Enterprise Manager. Basically Project can be used to develop front end's for novice users or allow access for proficient SQL developers/users without installing Client Tools on their PC. Project allows the user to connect directly to one SQL database (per .adp as is my understanding), where the user can create stored procedures, views, and diagrams. While also allowing for limited access functions (i.e. forms and reports). Access Project is ideal for developing front end applications for SQL as it does not use the Microsoft Jet Database Engine, instead it directly passes commands to the SQL server where they can then be processed.

2) Access this link to find out if MSDE is the right product for you

http://www.microsoft.com/sql/msde/default.asp

I could be wrong on this so do your research, but I believe MSDE is used primarily for developers to create a back-end data storage (which can be installed on a local machine or server) area for software products they intend to sell. If you are looking for a Server to use in a production environment within your office look into Standard or Enterprise Edition.

3) Yes, you do require a SQL Server Edition in order to convert or upsize your currect Access databases.

4) You can use the Access Upsizing Wizard to create your SQL database. Do note that some datatypes do not convert as clean as one would hope. As a SQL beginner this wizard is very helpful though.

If you plan on converting your Access databases/applications to SQL (with the intention of using Access as the front end) note that the SQL (Stuctured Query Language) dialects in Access and SQL Server are slightly different. If you are used to running reports or forms that call queries based on user input parameters, you will notice a dramatic decrease in performance. This method can be used in .mdb's, however this defeats the purpose of storing you data on SQL Server. With this method all data is pulled across the network from SQL to the local machine, then processed using Microsoft Jet Database Engine. The work around to this is creating variables within a Stored Procedure, then using VB to pass the variable to SQL. I am in no means a VB guru, so unfortunately I can't help you there. Just keep in mind that the standard Access tricks no longer apply, your best bet is to design a database application that performs the majority of your processes on SQL. A good book to pick up would be "Microsoft Access Developer's Guide to SQL Server" from Sams Publishing.

I hope you find this information useful, if I misunderstood any of you questions please feel free to clarify. I don't consider my self to be a guru in SQL, but I'd be happy to share my trials and tribulations to help you make the best decision. Overall I've been very happy with our conversion to SQL. I went through your scenario about a year ago and would stongly recommend either attending some SQL programming classes or receiving training of some sort before you take on this endeavor!

Sorry for the long winded message, but I hope it helps!!
~Megan

 

[sunnywink]

Thanks Megan for the advice, you have been very helpful.