SQL-Server2000 and Delphi?

I am using Delphi ADO components to connect to local access DB. I also use TDBGrids and other standart Data Controls...
How can modify my application to connect to SQL-server? My SQL-Server DB is allready up.

Anyone ever made this with delphi in a SQL multi-user env?
I use D5. Any special insites with this issue?

Any help will be great.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

My first advice would be to simply change the connectionstring and run the application from within the delphi environment. Put plenty of breakpoints at suitable places, such as before opening a query, and spot if/when thing start to go wrong. This will give you some idea.

The SQL accepted by SQL server is rather simular to MS Access, but some statement may need to be rewritten.

Apart from that, most things should run. Try correcting these errors if any, just to make things running again.

When they do, it is time to look over the design. Even though most things done using access also works using SQL-Server in the same way, there are many situations when it can be done much more effectively.

If you have complex sql queries, try moving as many of these as possible to stored procedures on the server. These always execute faster. If you make massive use of TADOTable components, try replacing these with TADOQuery or stored procedures instead.

Especially important if using filters. Filtered tables can always be rewritten to running much more effectively using Queries or stored procedures.

If your application need to support having multible users, you need to also look into using transactions and transaction handling. But this is not needed just for changing the database. You can still make use of the better preformance of SQL-server even with a single user application. I think the best feature is the possibillity of doing regualar backups in SQL-Server and things being less likely to crash if handling great amounts of data.

Good luck!
I think you better use '.udl' file (connection file).

1. I usually set CursorLocation property of ADO components to clUseServer especially for large datasets.

2. As Peter_ already said some Access SQL statements may not work on MSSQL because of different SQLs used.

3. If your app using Access DB wasn't multiuser then you have to redesign your DB tables, algorithms and GUI (perhaps) in an appropriate way (this is not that simple).

4. And I will repeat Peter_ again but use ADOqueries instead of ADOtables.

Regards, Geo
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

morgantopAuthor Commented:
thanks Peter_  and geobul,

I think you see my problem clearlly.
geobul, regarding your point 1, if i'll use clUseServer will i be able to use TDBGrids? what CANT i do normally using clUseServer?
(it supports only ForwardOnly cursor)

Im not worried about points 2+4 becouse i know i will need to change queries a bit, and i almoast always use only TASOQueries, and TADODataSet.

What i really need to know is how/what to redesign my DB tables or GUI. this is my major problem, becouse of lack of expirience... I need to get the major pointers about how my GUI and DB should look or NOT look (can i use delphi's Grids, DBControls, how to load and edit the dataset etc...)

I want to note that i use transactions in every insert,edit,delete blocks.

I will inc points larglly for a good explenation. please help me, as i never wrote delphi db app with SQL server.
thanks a milion.
You need to consider some things if your application is to support multiple users:

How many simultanious users of your application would you expect having at any one time? What rights do they need?

Will all these users need to have what seams to be full access to all data at the same time? If not, then what limitations can you apply? This is what is usually called a "locking strategy" as to prevent "lost of updates" and "dirty reads". Greater limitations means easier and faster design of your application, but it also means less freedom for the users working with it. A user might have to wait while other users finnish up their work.

If your application is to serve 100:s of users and they all need access to just about everything, most of the time (like in a worldwide airline booking system), then this is an extremely complicated task. You would likely have to design some kind of "3-tire" application with a client program and a server program which handles sending and recieving data from the different clients to the SQL-Server while making sure there are no collitions.

Most of the time thankfully, things are not that complicated. In most applications, only one client at a time needs to have write access to all or a rather large part of the data at any one time. This can be controlled in a number of ways:

You can design your application in such a way that on logon (startup) the user must choose if to enter the application with "update rights" or "read only". When one user has been granted update rights, no other user can get it until this user logs off. Your application will adapt to this mode such that for a "read only" user, all update functions are disabled. This is an easy solution, much like single user environment, but it is usually not accepted by the users.

In most cases, instead of having "read only" access to the entire application you will have to listen to the users, which is the largest amount of data they can accept for "read only access" at any one time? The answer will matter to you database design as well as your GUI.

The delphi db components themselves implement some degree of locking under the hood in the sense that only one user at the time can edit one record of data. One record of data in a dbgrid for example can be put together from many different database tables though if using queries. This is one thing one has to consider if using the components. Sometimes a rather large amount of data can be locked for other users when one record is being edited. A different db table design might then work better.

I personally prefer not to use the delphi db components very often when an application can have multiple users, but rather I create my own object model. If the database model is complex (with many linked tables) or there are more then just a few users, then the components dont offer enough controll to work properly in my opinion.  Each object will then hold their value and a boolean "dirty" that indicates if it has been updated or not. I then create a "container object" that holds objects of the same type (like an "Order object" that holds a TObjectList of OrderRow objects internaly).

I link the objects to the "data property" of a listview or simular, when the user updates the text I also update my object. This way the user can update all the "order rows" in an order, then push a button "save" that executes a procedure that starts a transaction which checks each object if it has been updated and then call a stored procedure that handles the update for those who has been. This reduces network traffic.

To make sure only one user at the time can edit each order (which is my locking stategy in this case), I open up a query which ask for an orderid with update look each time the orderform is opened. If the request fails then instead I prompt a message that this order is busy or I open up the order form as "Read only". When the orderform is closed I close the query as to unlock. Here is an example from one of my applications:

function TdmData.LockOrder(Order: TOrder): Boolean;
  if ( not Assigned(fADOqryLock) ) then
   fADOqryLock.Connection := adocOrderDB;
   fADOqryLock.CursorLocation := clUseServer;
   fADOqryLock.CommandTimeout := 7;
   fADOqryLock.SQL.Add('WHERE Order_ID=:Order_ID ');
   fADOqryLock.Parameters.ParamByName('Order_ID').Value := Order.ID;
   if (NOT fADOqryLock.EOF) then
    Result := True;
    Result := False;
    Result := False;

procedure TdmData.UnlockOrder;
 if (Assigned(fADOqryLock)) then

The other limitation here was that each user could only view one order at the time, but this was ok in this case. Maybe in another case you have to lock each "order row" or perhaps "all orders" if only one person does those at any one time.

To learn more about locks, browse the SQL-Server help files. There is alot to read there.

Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
morgantopAuthor Commented:
Peter, how can i lock a record in Edit mode via ADO? both in access and sqlserver
Access database don't support locks in the same way SQL-Server does. You can't lock individual records in the same way as with SQL-server. Have never tried doing a multi-user application using access because of this, but my knowledge there is limited.

In Sql-server SQL, you use

WITH (UPDLOCK, READPAST) ' or any other access restriction (locking hint)you like. Check the SQL-Server help files or MSDN, they show which ones you can choose from and what they do.

When using the DB components, some locks are issued automatically when put in edit mode. There are some parameters you can set on the componets as to which. If you are using SQL-server, you can check which locks are issued in a certain situation by calling the stored procedure sp_lock. You may use the SQL-server Query Analyser for this.

IMHO your GUI should not depend on record locking explicitly. I use select query for retrieving some data and another update/insert/delete query (or sequence of queries) in one transaction for saving the changes. I also don't use db controls at all.

Regards, Geo
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.