HELP MS Access moving to SQL and don't know how

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP MS Access moving to SQL and don't know how

Author

Topic

russler01
Starting Member

5 Posts

Posted - 2006-10-06 : 11:36:05

I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the error

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()

Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day.

So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me.

My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.

1. A good book to follow and easy to follow for SQL (connections / how tos) or website

2. Good examples or book on "ASP.Net Enterprise Manager"

3. How to convert access databases into SQL easy (some have over 50,000 listings)

4. how to convert Tab files into SQL easy.

Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites. I am afraid if I hurry to much I will build it wrong and really pay for it.. I am paying enough from using MS Access.. LOL

Thanks again,

Rusty

ACushen
Starting Member

29 Posts

Posted - 2006-10-06 : 15:31:21

As far as books-

I found the following one very helpful when starting my own trip from Access to SQL Server:

"Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron, SAMS Publishing, 2001, ISBN 0-672-31944-6.

Although some of it is a little dated if you are using SQL Server 2005, and it's not specifically aimed at Internet developers, still the basics are there. They cover upsizing an Access database to SQL Server particularly well.

One thing you MUST learn: using Access, you have probably been using SQL statements you concatenate in your ASP.NET code, including data that the user entered or chose from a dropdownlist. This is called Dynamic SQL, and YOU MUST NOT DO THIS in SQL Server; it leaves the server wide open to an extremely common Hacking technique called SQL Injection. Hackers tack on T-SQL commands to the end of the data, and can easily take control of your database, dropping tables, deleting/mangling data, stealing customer data, etc. There are ways of mitigating the risk, but unless you're an expert in SQL and the methods of attacking it, DON'T USE DYNAMIC SQL!
Instead, you write Stored Procedures in SQL server, and you pass in the data from the users using Parameters. There are many other advantages to using Stored Procedures, including speed- they are pre-compiled, so they run much faster. They are safer; they concentrate all the "business rules" in one place (it's debatable whether the best place for them is in a database rather than a separate layer, but for anything less than an Enterprise-level app, I don't see much benefit to separating them out), etc.

HTH,

-Andrew

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-10-07 : 07:56:19

use import option from enterprise manager.(sqlserver2000)

suji

Kristen
Test

22859 Posts

Posted - 2006-10-08 : 07:40:38

Beware that a public-facing web-hosted license for SQL Server is far from cheap - if you are using some shared hosting that provides SQL Server this won't be a problem for you, but thought I would raise it in case relevant.

There is an Upsize Wizard for Access which should help move the database to SQL Server.

"This is called Dynamic SQL, and YOU MUST NOT DO THIS in SQL Server"

Afraid I don't agree. Whilst we use 100% Stored Procedures for our application, starting from an Access solution that would take ages to develop , re-engineer and test.

You need to be careful to make sure that you have isolated ALL parameters and pass them through a function, but that's it.

So


strSQL = "SELECT MyCol1, MyCol2 FROM MyTable WHERE MyPKCol = '" & form("MyLabel").value & "'"

needs to be changed to


strSQL = "SELECT MyCol1, MyCol2 FROM MyTable WHERE MyPKCol = " & fnSQLParam(form("MyLabel").value)

where fnSQLParam does something like:


Function fnSQLParam(strParam)
    fnSQLParam = "'" & replace(CStr(strParam), "'", "''") & "'"
End Function

I do, however, recommend that you have a look at Stored Procedures, or perhaps parameterized queries (e.g. using sp_ExecuteSQL) as these will perform much more quickly, but if you are in a hurry to leave Access behind you don;t really want to be changing you application very much fr Version 1 - unless it is very small.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-08 : 10:46:04

A couple of things:

1) Access is fully capable of using parameters, just like Sql server, so they might already be in use that way, and if so -- leave them that way!

2) If params are not being used in Access, rewrite them to use params. Always. This will take no more time than converting them to any other syntax to work with Sql, or to incorporate a function like Kristen suggested. Never, ever concatenate sql like that, even if you use "escaping" functions. And sql injection is not the only reason to use parameters, see:
http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

3) Lots of the sql you are using in Access may not work that in SQL Server anyway ... IIF() becomes CASE, MID() becomes SUBSTRING(), casting and coverting works differently, dates have different delimiters, and so on .... Be sure that before you even *think* about converting things to SQL that you start from scratch and play around with SQL Server and really take the time to understand it, writing some SQL, create some tables, find out how things work different, how to view and test results, and so on.

4) No one ever wants to hear this, but the best way to go is to check your design, convert your data over, and then rewrite things from scratch. I know, I know, there's tons of stuff to rewrite, you don't have time, and so on -- but I promise you that it will be quicker and easier and cleaner and you might be surprised that if you examine your system and carefully rewrite it that out of the 10,000 queries you thought you needed you end up only needing 300 of them ....

- Jeff

Kristen
Test

22859 Posts

Posted - 2006-10-08 : 11:05:25

Absolutely agree Jeff, that would be a far better solution, but it is a lot more editing, and what I would consider "risky editing" in the sense that there is more chance of introducing a logic error, and thus proper testing will be required required. So I think Stage 1 depends on some sort of cost-benefit equation. If time available then parameterized queries definitely better. If no budget/stomach for a Phase II then its better too if it is done for Phase I. But if it is critical to get Access out the way as quickly as possible I think changing


strSQL = "SELECT MyCol1, MyCol2 FROM MyTable WHERE MyPKCol = '" & strMyString & "'"


strSQL = "SELECT MyCol1, MyCol2 FROM MyTable WHERE MyPKCol = " & fnSQLParam(strMyString)

is "less risky editing" than


strSQL = "SELECT MyCol1, MyCol2 FROM MyTable WHERE MyPKCol = @MyParam
...
MyCmdObject.Parameters.Add("@MyParam", SqlDbType.VarChar).Value = strMyString

on the assumption that the programmer is unfamiliar with this type of approach - but as you say, its possible that it has been done that way in Access in which case Yippee!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources