Disconnecting from SQL server (Access97)

Topic

New Reply

Some of the tables we use are on an SQL server. When we need to access these tables we have to login to the SQL server. We have 5 licenses and one of these is appointed to the user when logging in. Even though the user may not need to access the SQL server again for a few hours the license stays with them all the time. After accessing the SQL tables I would like to free up license and avoid getting this message…

Login failed. The maximum simultaneous user count of 5 licenses for this ‘Standard Edition’ server has
been exceeded. Additional licenses should be obtained and installed or you should upgrade to a full version.

…we are currently needing to close down the application to free up the licenses.

Is it possible to free up the licenses using VB?

Reply | Quote

Replies

As long as the Access database stays open, the ODBC connection to the SQL Server tables stays up, thus occupying a license. I presume of course that you are dynamically connecting the SQL tables, but using permanently linked tables. That might be a solution – implement some code to connect to the SQL tables via ODBC only when the user needs to, and when they finish whatever tasks require the tables then drop the connection. I’ve never actually tested to see if licenses are released if you drop all connections, so try it first by manually opening the tables, closing them, and then deleting the connection – I’m making another assumption that you have a split database where each user has their own copy of the front-end. On the other hand, if you aren’t pretty comfortable with VBA then it would probably be a better bet to buy 5 additional licenses. Hope this helps.

Reply | Quote

Addtionally, I believe each client may make more than one connection behind the scenes, effectively reducing the number of user licenses you have available. While watching the processes running on my SQL Server, I:

Opened an Access XP db with a linked table — no processes from my workstation
Ran a query on the linked table — 3 processes from my workstation ID, 2 sleeping, 1 runnable
Closed the query, left the Access XP db open — 1 process with a status of sleeping
Closed the Access XP db — no processes from my workstation

So, how many processes count against your 5 license limit at any given time will be highly dependant on exactly what’s going on at each client. Perhaps the easiest steps to take are to encourage your users to close the app when not in use, or dynamically link to the SQL Server tables only as necessary, and sever the link immediately after. Best of luck!

Reply | Quote