Upsizing To SQL Server

[Grumm]

I have an access database that I want to upsize to SQL Server. Through out the application I have expressions in the queries (e.g A command button opens a report and the query has an expression with Me!Date to prompt the user to enter a date parameter).

When I try and upgrade with the Client/Server option I get a great deal of problems. However if I chose Link SQL Server to current application it works fine. What are the draw backs of this option. I seems to work fine - can I not distrubute over a network.

 

[jbehrne]

Hi,

I have recently upsized an access db to SQL Server 2000 (I imported all the tables from the Access db). I am using the Access db now as a frontend to the tables in SQL w/no problems. My tables are linked and the connection is provided by a system ODBC. If you are worried about queries you really shouldn't be (I am using all my native Access queries with no problems). I have over 50 users in different buildings around town using the db (on a private intranet) so if you are concerned about sharing it via a network, you shouldn't be. You should mainly be worried about the data consistency. Make sure that you set the data correctly in SQL...

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Grumm]

Cheers for that

 

[markphsd]

Jbherne,

I'm in the same situation as both of you, sort of. I believe that there are some drawbacks that weren't mentioned. Maybe more that I don't know of. Also, I think people in general should stop saying how easy it is to upsize, it's not that easy if you have a pretty heavy access mdb that uses dao recordsets and some action and a few parameter queries. I hope my information here is accurate and not made up.

1. Main drawback: you are still using the Jet Database engine. It requires a lot more bandwidth and creates a lot more traffic then the adp for sp's and views. This is my main concern and the only way around it is to consider the second part of the next point, which is a real pain. Jet Databases aren't known for their data integrity.

2. A. You'll have to learn lots of work-arounds that the ADP files can do.
B. Conversely you'll need to learn all the ways to do things the adp with a SQL Server can't, like dlookup and paramenter queries that are a piece of cake; unlike sending parameters through SP just to get some simple info, which is a pain if you aren't really familiar with it.

3. Not learning SQL Sever in itself may be a drawback. You learned Access. You can learn SQL. There's a big fuss about SQL Server for some reason, it might be good money to know it. If you need to use it, well suck it up. (Well, I say this to give me hope of learning it.) Then again, only use it where you need it I suppose.

4. Jet 4. is the last Jet version microsoft will build, after that it's MSDE. Eventually you'll need to switch over.



Mark P.

Bleh

 

[LonnieJohnson]

How do you do a DLookup in SQL Server?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[psemianonymous]

answered thread181-569063

 

[Hap007]

Dear markphsd and Jbherne,

Yes, if you are using DAO and ODBC, then you are using Jet, but there is nothing wrong with that. We all have been using Jet for years.

In order to improve performance in this particular setup,
1) Identify what queries are bad performers.
2) Replace those queries with stored procedures or views.
3) Now call the stored procedures using pass thru query.

Net result, still using DAO and your performance is now great.

Good Luck,
Hap...




Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer