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 6 1499 ha********@aol.com (Hank Reed) wrote in news:f4b05677.0407140310.387f7870
@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.
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.OpenRecordset(SQL1)
If (rsPO.RecordCount > 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! 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
"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.OpenRecordset(SQL1) If (rsPO.RecordCount > 0) Then (et cetera)
Fairly easily recreated in stored procedures....
--
regards,
Bradley
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.OpenRecordSet("yourtable")
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.LastUpdated
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
"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.AddNew rstRec!City = "Edmonton" rstRec.Update rstRec.BookMark = rstRec.LastUpdated 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Nikola Skoric |
last post by:
Hi!
Few days ago I upgraded my MySQL 3.23.xx (can't remember those two
last, really) to 4.0.16. And then three problems came up (well, more
than two came up, but I solved all the others).
1....
|
by: Thomas |
last post by:
Hallo.
Habe auf einem SeSE 9.1 Server due neue MySQL 4.1.11 und den
dazugehrigen client installiert.
#: mysql -V
mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i686)
Soweit so gut der...
|
by: Tex |
last post by:
Hi to all,
I've a question about upgrading a MSCS'ed DB2 v7.2 to DB2 v8.1.
The scenario is simple:
Server A and Server B, DB2 running on A.
I'm ok about installing a fixpak on a clustered...
|
by: Shawn |
last post by:
After upgrading to IIS 6.0 /Windows Server 2003 Standard
Ed, I get the following error message for regular .asp
pages. How can I fix this?
HTTP Error 403
403.1 Forbidden: Execute Access...
|
by: Precious |
last post by:
Our existing application is developed using VB6/SQL Server 2000, which is
used by remote users located at different places through vpn/terminal
server. It is a typical accounting application. I...
| |
by: paulh |
last post by:
Hello, we are preparing for an upgrade to SQL 2005 and as a result of
this I became aware that the compatibility level of one of our
databases was set to level 65 (current SQL server is SQL 2000...
|
by: Ivan |
last post by:
Hi, I experienced a problem while upgrading from DB2 UDB LUW Workgroup
edition 7.2 FixPak 10a to Workgroup Server edition 8.2 FixPak 12. The
install wizard said the upgrade was successful but none...
|
by: JimLad |
last post by:
Hi,
We have a major ASP app that we are in the process of upgrading to
ASP.NET 1.1 and probably on to ASP.NET 2.0 in the middle of next year.
(We will also be upgrading to SQL2K5 at the same...
|
by: Steve |
last post by:
Hi guys,
I'm trying to upgrade TFS 2005 to TFS 2008 on a test server in
preparation for upgrading our live box. However, when I run the
install of 2008, I get the following error message:
...
|
by: oriol.ardevol |
last post by:
Hi,
We've recently migrated from a framework 1.1 asp.net solution to
framework 2.0 and we're facing a problem when upgrading a dll in our
production environment.
We have a dll with a pagle...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |