Upgrading to SQL Server

[Hank Reed]

Hello,

I have convinced my boss that it is time to upgrade our Access 2000
database to SQL server or possibly some other back end. I have
researched many of the posts to get a feel for the effort required.
The application has about 120,000 lines of code but a relatively small
dataset of about 60MB. If you scold me for not doing this, 100,000
lines ago, I'll understand.

My particular programming style uses SQL statements embedded in the
code for most of data handling. I understand that many (most? all?)
will have to be converted to stored procedures. My main question that
I have not been able to pull out of my readings is this: Can the back
end be upgraded and run off the client side SQL statements, allowing
conversion as time allows, or does every query have to be converted to
a stored procedure first?

I realize that continuing to do client side processing does nothing
to enhance the speed but the task of converting every line at once
seems pretty daunting.

Thanks,
Hank Reed

[Lyle Fairfield]

ha********@aol. com (Hank Reed) wrote in news:f4b05677.0 407140310.387f7 870
@posting.google .com:

Hello,

I have convinced my boss that it is time to upgrade our Access 2000
database to SQL server or possibly some other back end. I have
researched many of the posts to get a feel for the effort required.
The application has about 120,000 lines of code but a relatively small
dataset of about 60MB. If you scold me for not doing this, 100,000
lines ago, I'll understand.

My particular programming style uses SQL statements embedded in the
code for most of data handling. I understand that many (most? all?)
will have to be converted to stored procedures. My main question that
I have not been able to pull out of my readings is this: Can the back
end be upgraded and run off the client side SQL statements, allowing
conversion as time allows, or does every query have to be converted to
a stored procedure first?

I realize that continuing to do client side processing does nothing
to enhance the speed but the task of converting every line at once
seems pretty daunting.

It's unlikely that the syntax of your JET "SQL statements embedded in the
code" will be compatible with the T-SQL syntax; many of these queries will
fail if you connect to the server via OLEDB.

In order to use query strings in code, the user must have Table Permissions
(SELECT, INSERT, DELETE, UPDATE) in the MS-SQL DB. Many DB administrators
will not want to give such permissions.

T-SQL is very powerful. If your 120000 lines of code is about Data
Manipulation you may be able to translate much of it to Stored Procedures
and User Defined Functions on the MS-SQL Server. You will need some time to
learn T-SQL in order to this.
But if the code is about something else, converting to MS-SQL will do
nothing to reduce it.

It's rumoured here in CDMA that MDBs, ODBC and Pass Through Queries will
let you treat MS-SQL just like JET. Implementing a solution like that will
get you, hmmmmmm, where?

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Hank]

Lyle,

Thanks for the quick response. Yes the SQL statements are all about
data manipulation and retrieval. I build a lot of view screens and
reports for my users. Also, obviously, they are always adding data. By
embedded statements, I mean something like:

SQL1 = “DELETE FROM [Table Name] WHERE [Customer ID] = “ &
CustomerID & “;�
DoCmd.RunSQL SQL1 or

SQL1 = “SELECT * FROM [Purchase Orders] WHERE [PO ID] = “ & POID &
“;�
Set rsPO = db.OpenRecordse t(SQL1)
If (rsPO.RecordCou nt > 0) Then (et cetera)

The reason I use this approach is that it makes single step debug
extremely easy as you can see your SQL statements verbatim.

Your statement here is a little cryptic:
It's rumored here in CDMA that MDBs, ODBC and Pass Through Queries will
let you treat MS-SQL just like JET. Implementing a solution like that
will get you, hmmmmmm, where?

If you are saying that I will gain nothing execution-wise I understand.
My only hope is that I can make the SQL to T-SQL transition one function
at a time.
Hank Reed
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tony Toews]

ha********@aol. com (Hank Reed) wrote:

I have convinced my boss that it is time to upgrade our Access 2000
database to SQL server or possibly some other back end. I have
researched many of the posts to get a feel for the effort required.
The application has about 120,000 lines of code but a relatively small
dataset of about 60MB. If you scold me for not doing this, 100,000
lines ago, I'll understand.

My particular programming style uses SQL statements embedded in the
code for most of data handling. I understand that many (most? all?)
will have to be converted to stored procedures. My main question that
I have not been able to pull out of my readings is this: Can the back
end be upgraded and run off the client side SQL statements, allowing
conversion as time allows, or does every query have to be converted to
a stored procedure first?

You can convert it one step at a time by using tables linked to SQL Server. This is
exactly the approach I started with for a clients very large app. Unfortunately,
due to the intervention of a PHB (Dilberts' Pointy Haired Boss) I didn't finish.
Thus the below document isn't complete.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

I wanted to be at the stage where, at cutover time, the same FE MDB could be used for
Access and SQL Server. Thus in the weeks preceding the cutover I could still make
changes for the client while connected to the Access BE and yet I could continue
working on the SQL Server conversion.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Brad\)ey]

"Hank" <ha************ ***@aol.com> wrote in message
news:40******** **************@ news.newsgroups .ws

Lyle,

Thanks for the quick response. Yes the SQL statements are all about
data manipulation and retrieval. I build a lot of view screens and
reports for my users. Also, obviously, they are always adding data.
By embedded statements, I mean something like:

SQL1 = â?oDELETE FROM [Table Name] WHERE [Customer ID] = â?o &
CustomerID & â?o;â?�
DoCmd.RunSQL SQL1 or

SQL1 = â?oSELECT * FROM [Purchase Orders] WHERE [PO ID] = â?o & POID &
â?o;â?�
Set rsPO = db.OpenRecordse t(SQL1)
If (rsPO.RecordCou nt > 0) Then (et cetera)

Fairly easily recreated in stored procedures....
--
regards,

Bradley

[Albert D. Kallal]

Actually, if you use linked tables to sql server, then a VERY large portion
of your code will work.

So, to:

Can the back
end be upgraded and run off the client side SQL statements, allowing
conversion as time allows, or does every query have to be converted to
a stored procedure first?

yes the above is right on the money as to how this works. The only kink is
that some stuff may not work, but you simply fix that.

Then, OVER TIME, you start converting sql queries and code to stored
processing. In fact, some stuff will run just fine and dandy, and you don't
have to convert it at all.

So, in my experience, 99% of your sql code, even dao reocrdset code will
work. There are some things that have to be changed, for example:

dim rstRec as DAO.RecordSet
dim lngNewID as long

set rstRec = currentdb.OpenR ecordSet("yourt able")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpda ted
lngNewID = rstRec!ID

So, some things like code that assumes the autonumber ID (identify in sql
server) will be available BEFORE the update occurs has to be changed. But,
most code will in fact run as is...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[Tony Toews]

"Albert D. Kallal" <Pl************ *******@msn.com > wrote:

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNe w
rstRec!City = "Edmonton"
rstRec.Updat e
rstRec.BookMar k = rstRec.LastUpda ted
lngNewID = rstRec!ID

Whereas I never knew you could even do the former. It never occurred to me to try.

So I've used the following code for as long as I could remember.

RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

No idea where I found it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm