Upgrading a Database Application

[smil3y]

I am upgrading a database application that I created a couple of years ago, so that it can operate on an SQL server. It used an Access Database previously and all my database operations used commands similar to:

sSQL = "SELECT etc", "INSERT etc", "UPDATE etc" or "DELETE etc"

Set rsado = Con.Execute(sSQL)

In my updrade (Once my connection (Con) has been set up, I have been using:

If Con.State = adStateOpen Then
With Rec
.CursorLocation = adUseClient
.Open SQL, Con, adOpenStatic, adLockBatchOptimistic
<Do what I need to do etc>
then continue.

By using comDetails as New ADODB.Command I have been able to translate some of my transactions as shown below:

With comDetails
.ActiveConnection = Con
.CommandType = adCmdText
.CommandText = "SELECT * FROM Employee etc" OR
.CommandText = "UPDATE Employee SET Employee etc" OR
.CommandText = "INSERT INTO etc"
End With

Con.Execute

HOWEVER using the command text
.CommandText = "DELETE Employee.* FROM Employee WHERE etc"

fails. The only way I can get DELETE to work is using a recordset to find a record and then using something like

Rec.Delete
Rec.UpdateBatch

I have read numerous articles from Microsoft and this forum and would like to know what is the best way to perorm these transactions (ie SELECT, UPDATE, INSERT and DELETE).

Is there a standard (??) way I can perform these transactions.

 

[gmmastros]

With SQL Server, you can only delete records from 1 table at a time, so, the syntax is different.

With Access, you specify the table name in the delete clause and in the where clause.

.CommandText = "DELETE FROM Employee WHERE etc"

You can also delete records from a table based on information in another table, using this syntax.

Delete Employee
From   Employee
       Inner Join EmployeeLocation
         On employee.LocationId = EmployeeLocation.LocationId
Where  EmployeeLocation.Name = 'Some Location'

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelRed]

but the "update" seems to not take advantage of the SQL server capabilities very well. Stored procedures would SEEM to be a better approach. It would only need to send the procedure info over the network and SQL Server could 'pre-compile' them to remove the delay inherent in parsing the query strings. While a reasonably usefull aproach to getting started down the path, it begs the developer to continue ...




MichaelRed

 

[BobRodes]

Yup. Stored procs are the way to go, for the reasons you mention and also to avoid SQL injection attacks.

 

[smil3y]

Thanks for the responses.
As explained, I am trying to upgrade an application. For this application all the SQL statements are within the application (not stored procedures).

I have read numerous articles on the security aspects of SQL etc and agree that stored procedures are the way to go BUT this would take me even more time. So for now I am just upgrading and once it is working I will look at migration to Stored Procedures.

 

[gmmastros]

Yep. I agree.

I did a similar thing. For my app, Version 4 only supported Access. Version 5 supported both Access and SQL Server. Version 6 had about 30% of the queries as stored procedures, and version 7 has 100% stored procedures. It's a long a painful road. The cool part is that SQL Server is faster than Access, and stored procedures are faster then in-line SQL, so each version of my app had more functionality (like a good version upgrade should) AND had better performance.

My advice to you is... as you move forward with the conversion... First, make sure the functionality works, then attack the slow performing processes, then do the rest.

I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom