Solved

Failure: Access97, Intersolv, DBProcess

Posted on 1998-12-11
7
264 Views
Last Modified: 2012-06-27
I have an application written in Access97 and it is connected with SQL-server over INTERSOLV ODBC-drivers. When I work a little bit an make some harder things, the following happens:

Access opens the standard-login window for SQL-Server. When I try to login now I get the following message (German version):
Maximum number of processes (DBPROCESS) already reserved

original message:
Maximale Anzahl von Prozessen (DBPROCESS) bereits reserviert
0
Comment
Question by:lo102698
  • 3
  • 3
7 Comments
 

Expert Comment

by:rayasam
ID: 1092231
please check the SQL server configuration by running sp_configure 'user connections'. The error generally means that maximun number of allowed SQl Server 'user connections' are already used. Look in the SQL Server errorlog for messages like maximum number of user connections reached( this is not the exact message), If you find anything similar to these then increase the "user connections" server configuration parameter using sp_configure stored procedure.
0
 

Author Comment

by:lo102698
ID: 1092232
OK, the reason for the SQL-Server is that the maximum number of users is reached. But the big question is WHY?! The maximum number of usres is 10, but I am working allone. The problem is, that the INTERSOLV-driver opens many connections if I open more recordsets
0
 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
ID: 1092233
How old are your INTERSOLV drivers?  Do you know what version of ODBC they support?  It is a problem with older versions of ODBC especially, and of ODBC in general that one user may open up multiple connections.  

Are you connecting to the SQL Server tables through linked tables, or are you opening  up the database in code?  If you are connecting through code, be careful of the number of OpenDatabase statements you have, and make sure you close your database when finished.

MD
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 18

Expert Comment

by:mdougan
ID: 1092234
One additional comment,

When you open a recordset, first check to see if you have any records at all, then movelast, then movefirst again.  What happens is that ODBC holds the connection until all of the records from the result set have been read into the program.  When you movelast then movefirst, ODBC can release the connection to let it handle opening and reading another recordset...

MD
0
 

Author Comment

by:lo102698
ID: 1092235
I use the Version 3.11 of INTERSOLV. The date is 1.7.98. This is the actual version.
In zhe main case I open linked tables and also queries which depend on liked tables. If this is a problem I can also connect direct to the SQL-Server. Sometimes I open a ODBC-direct connection. But the failure happens also when I do this not.
Well, I am not marryed with the MS-SQL server. Maybe you know a better SQL-Server which is not too expensive (ORACLE for example is too expensive if you have 50 users). Or maybe you know alternative ODBC-Drivers for the SQL-server.
But now I first make som experiments with moveFirst and move last.

Thank You
0
 

Author Comment

by:lo102698
ID: 1092236
One comment more:

When I open tables in the database-window, I can open as many as I want.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 1092237
Your INTERSOLV drivers sound fairly recent, so I would suspect that they support newer versions of ODBC.  Since you're using Access 97, I assume that you have a fairly recent version of ODBC (2.0 or 3.0) installed.  So, your problem is probably not due to outdated ODBC.

Linked tables are fine for debugging and development work, but they can be unrelyable for a production system.  Sometimes the links become invalid and need to be refreshed.  It sounds like your links are fine, though, since you're able to open the tables in datasheet mode.

The following is an example I got out of the Access DAO help file.  It illustrates the type of code I usually use to get to SQL Server through Access.  I don't do this often from inside of Access, as I don't use that as a front end development environment, but I do a similar thing from VB with good results.  You'll see that you have to open an Access database (even though this Access database does not have any links or tables) in order to have a place to create temporary querydef objects.  Then, you set those temporary querydef objects up as "PassThrough" queries.  Note that you close everything when you're done!

MD

Sub ClientServerX1()

      Dim dbsCurrent As Database
      Dim qdfPassThrough As QueryDef
      Dim qdfLocal As QueryDef
      Dim rstTopFive As Recordset
      Dim strMessage As String

      ' Open a database from which QueryDef objects can be
      ' created.
      Set dbsCurrent = OpenDatabase("DB1.mdb")

      ' Create a pass-through query to retrieve data from
      ' a Microsoft SQL Server database.
      Set qdfPassThrough = _
            dbsCurrent.CreateQueryDef("AllTitles")
      qdfPassThrough.Connect = _

"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
      qdfPassThrough.SQL = "SELECT * FROM titles " & _
            "ORDER BY ytd_sales DESC"
      qdfPassThrough.ReturnsRecords = True

      ' Create a temporary QueryDef object to retrieve
      ' data from the pass-through query.
      Set qdfLocal = dbsCurrent.CreateQueryDef("")
      qdfLocal.SQL = "SELECT TOP 5 title FROM AllTitles"

      Set rstTopFive = qdfLocal.OpenRecordset()

      ' Display results of queries.
      With rstTopFive

strMessage = _
                  "Our top 5 best-selling books are:" & vbCr

            Do While Not .EOF
                  strMessage = strMessage & "    " & !Title & _
                        vbCr
                  .MoveNext
            Loop

            If .RecordCount > 5 Then
                  strMessage = strMessage & _
                        "(There was a tie, resulting in " & _
                        vbCr & .RecordCount & _
                        " books in the list.)"
            End If

            MsgBox strMessage
            .Close
      End With

      ' Delete new pass-through query because this is a

' demonstration.
      dbsCurrent.QueryDefs.Delete "AllTitles"
      dbsCurrent.Close

End Sub
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 38
Help in Understanding a SQL Query 7 28
Backup Job question 4 20
Copy Database Wizard Error 3 22
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now