The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Making my stand alone (vb6 MSDE) application client/server.


Here is a description of where I'm at, I'm confidant I can create a solution that will work but would really appreciate some suggestions from people who have built similar systems in the past.

So  I can build something that will work well. :)

I have a vb6 application that connects to an MSDE database server.

Currently my installer installs the msde server and the application onto the users PC.

I want to create a "client only" install option that will install the application which will
then connect to a previously installed MSDE server over the LAN.

I also want the ability to limit the number of clients that can be used acording to licencing


The issues I am considering include :

Should it be the database that is retricting the number of clients or do I need some kind of
server/service that my clients connect to as well as connecting to the MSDE server ?

How can my vb6 client application find the right MSDE server on the network ?

If my application connects and disconnects from the database server multiple times during one
session of use then I cant limit clients simply by limiting number of simultaneous connections,
I will need each client install to "register" with the db when installed and "unregister" when
uninstalled.  Then my client installer could check with the database how many are already registered
and not allow the install if the maximum number has been reached.
This would mean the db would have to be available when uninstalling ?

I realise there are multi-user issues with MSDE, I am going take the view that if my users are
hitting the limitations of MSDE they will have to upgrade to SQL Server.

Feedback appreciated.
samh Send private email
Sunday, July 09, 2006
Most of what you're asking goes in the connection string.

As for the licensing restriction stuff I'll defer to others.  There are lots of possibilities but no particular one I'd recommend.
j. freling
Sunday, July 09, 2006
j. freling,

Do you mean I just name the database instance in the connection string in the client app and it will find the database server ?

In the connection string I have to put the host name of the database server. 

If a client has installed the full stand alone version on on network pc then comes to install the client only on another, I don't know the host name of the database server at compile time.

At some point during the install I have to determine on which host the database is.

samh Send private email
Sunday, July 09, 2006

I can't really help with the "find MSDE instance" question but I have some other things for you to consider:

- Stop thinking like you are using MSDE.  You are really using SQL Server, but for your small clients they can use the free MSDE product "version" of SQL Servre.  If you start thinking like this then you will start thinking bigger regards other areas of your business.

- You really aren't looking for an instance of MSDE, you are looking for the instance of SQL Server on the local network that has your database attached.  At our place we publish instructions for setting up a system DSN on the client machine that points to the database server.  And we don't worry about seeking out the SQL Server that has our database attached.  Your client is going to have to have a password, etc. and for your larger "true" SQL Server clients you may not be able to just go find the box.  You are moving your app into a larger sphere and that requires a bit more network moxie on the part of your client.  The quality of your documentation will determine how much of a support burden this become for you.  Or maybe someone else here does know how to do what you want - I would like to know, too.

- Think about upgrades to your product.  On a standalone version this is not a big deal, but as soon as you add a second client into the mix you start getting into problems with version synchronization.  You might think about creating a "client version" folder on the server in a public folder, then using a stub app as your app startup.  This stub simply compares the version of your app on the client with that on server, and installs the new version if necessary then starts your app.  If the client version is still valid, it simply starts your app.  This way, upgrades are completely transparent to everyone except whoever administers them at your client site.

- Regarding users, the answer depends on whether you are going to license "per installed copy" or "per concurrent user."  Either way, you can manage the process by creating a LogIns table in your database.  When someone logs in, a new record is appended to this table or a flag is set in an existing record.  When they log out, or your timeout period expires, their record is deleted or the flag is cleared.

In the "per installed copy" model, each user creates a record on install and your encrypted license file controls how many installs can exist at a time.

In the "per concurrent user" model, at login the system counts the currently logged-in users and does something if the limit has been reached.

You will need administrative support to clear erroneous or missing logouts, etc. and to add new users to the license file.
Karl Perry Send private email
Sunday, July 09, 2006
Hi Samh,

MSDE is effectively SQL Server with a few limitations. The main ones being on database size and the other being on the number of concurrent users (five) before performance is affected. Note that this is not the 'maximum' number of concurrent users, just when performance starts to deteriorate.

You connect to MSDE as though it was SQL Server (hey, it is). You don't need to 'find' the server as you would an MDB file to connect to it.

Here's a simple example. Simply replace the name of the server and database with your own. If the MSDE database is own you local machine use '(local)' as the server name as in the example below.

    Private cnn As ADODB.Connection
    Private cat As ADOX.Catalog
    Private tdf As ADOX.Table

Sub ListTables()
    Set cnn = New ADODB.Connection
    Set cat = New ADOX.Catalog
    Const cstrServer As String = "(local)"
    Const cstrDbs As String = "Tracker"
    cnn.ConnectionString = _
        "Driver={SQL Server};" & _
        "Server=" & cstrServer & ";" & _
        "Database=" & cstrDbs & ";" & _

    cat.ActiveConnection = cnn
    For Each tdf In cat.Tables
        If tdf.Type = "TABLE" Then
            Debug.Print tdf.Name
        End If
    Next tdf


    Set cat = Nothing
    Set cnn = Nothing
End Sub

Whether you hit MSDE's limitations will depend on factors including the number of concurrent users and the volume of data. Where possible always use stored procedures to manipulate data (rather than opening up recordsets). I've had databases with 100 users with an average of 6 concurrent connections. The application, connected, executed the required stored procs and disconnected.

Hope this helps.
Marcus from Melbourne
Sunday, July 09, 2006
Karl Perry,

Thanks for your input, you raise some good points.

My market is not very tech savy so the more I can reduce the required moxie the better :)

I am currently working under the assumption that it is indeed technically possible for my client to "go out and find" the sql server on the LAN without any input from the user.

The versioning synchronization issue is indeed one I haven't really considered.

Your explanation of the per installed copy vs concurrent user helps. I am definately looking at "per installed copy". 

So when a new client is installed it writes to a database table recording the fact.  When the number of clients installed limit is reached and the client attempts another install the installer reports to the user "Unable to install, maximum number of clients already installed.", or some such.

I guess I have to check the count on the database during the install, otherwise the user would completely install the client before realising they cannot use it.

But I would need to check the count on the server when the user runs the program as well else they wont be able to install the client if the database is down.

So each client instance will need a unique number, when it starts it checks it's registered on the database by looking in the "INSTALLED_CLIENTS" table for it's number. If not it adds itself.

What if somone uninstalls a client when the database is down? it would never know to remove that clients number from the database, it would never know that the number of installed clients has just decreased. hmm

Thanks for your input Perry.
samh Send private email
Sunday, July 09, 2006
Gday Marcus from Melbourne,

Thanks for the connection info. 

I have msde connectivity working well in my stand alone application. 

My installation program installs my app and msde to the users PC.

I'm looking at the possibilities of somone installing a second client on the same LAN but not installing msde onto that second PC, having the second client locate and connect to the msde server on the first PC without the user having to set up the connection.

The second client goes out and locates the msde server and connects to the database.

Samh from Canberra
samh Send private email
Sunday, July 09, 2006
Hi Samh,

In the sample I provided, you'll be able to connect to an instance of MSDE on another users PC's by replacing the server with their computer name. However, you'll probably find that you still need to install MSDE on the other client's PC as MSDE (as does SQL Server) runs as a service (I'm happy to be corrected on this).

    Const cstrServer As String = "W000F2034907B"
    Const cstrDbs As String = "Tracker"

You can also check out the documentation online. Virtually whatever applies to SQL Server applies to MSDE.

Must be a bit chilly up there in the ACT - we're enjoying 11-C and a cutting Easterly.

Marcus from Melbourne
Sunday, July 09, 2006
==>I don't know the host name of the database server at compile time.

That's why we have config files isn't it? Or have them enter it on a dialog box during the install.
Monday, July 10, 2006
Google is your friend:

Private Function GetAllSqlServerCollection(colSqlServers As Collection)
    Dim intIndex As Integer
    Dim oApplication As SQLDMO.Application
    Dim oNameList As SQLDMO.NameList

    Set oApplication = New Application
    With oApplication
        Set oNameList = .ListAvailableSQLServers
        With oNameList
            For intIndex = 1 To .Count
                colSqlServers.Add (oNameList.Item(intIndex))
        End With
    End With
    Set oApplication = Nothing
    GetAllSqlServerCollection = True
End Function

This should help you find the available SQL Servers.
Cade Roux Send private email
Monday, July 10, 2006
Once you've found the servers, I would interrogate the databases which could be yours (successful login, contains a certain table with the right signature).  Then display only servers which could still possibly work.
Cade Roux Send private email
Monday, July 10, 2006
Cade Roux,

Thanks, that code snippet is helpful.
samh Send private email
Monday, July 10, 2006
Have you thought about a record-locking scheme or concurrency issues.  This may get worse as the number of users increases.

Wednesday, July 12, 2006
If scalability is an issue you wouldn't have clients connecting to the database anyway.  All it takes is a few poorly formed queries and pretty soon you're shuffling massive amounts of data over the wire.

This is why the world went n-tier a decade ago.

It also give you isloation and security at a controlled point: the application server.  I'd guess that the vast majority of such appservers are web servers (or web servers hosting "web services").  The next biggest chunk being COM+ based using DCOM, .Net Remoting, or RMI as the protocol, then you find the CORBA stuff, and so on.
j. freling
Wednesday, July 12, 2006
Oops, phrased it poorly.  Didn't mean to imply RMI is used with COM+.
j. freling
Wednesday, July 12, 2006
can any body tell me how to install msde with my vb application. i am using setup factory 5 to create setup for my appliaction.

any help will be appricated

Kshitij Sharma Send private email
Sunday, July 16, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz