Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Unanswered: Access-SQL Server Connections

    Hello All!

    I have an Access front-end, SQL Server back-end application. All users have their own accounts in the .mdw file in the front-end; in the back-end on the server I’ve created a group called UserAA, secured by a password. When a user starts Access, in the Switchboard Load Even the connection string is created. The application is rather complex and generally keeps several recordsets open simultaneously, so when I looked at Current Activity on the SServer, with only 3 forms open, it showed 6 open connections for UserAA on my host computer. The other user of this application had 7 connections open.
    The application has 80 users. So if, say, 50 of them are connected at the same time and have average of 10 recordsets open, it adds up to 500 simultaneous connections for UserAA group.

    How does all of that work?…Access documentation says that Jet can handle up to 255 simultaneous connections. – in my case will it be 255 connections per a host computer(user) or per entire UserAA group?

    How can I reduce number of connections? Is it possible to handle multiple recordsets by one connection?

    Thank you in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    1. Open a connection
    2. Put the data you want into variables
    3. Close the recordset


    Values needed are now in variables
    NEVER leave recordsets open!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Posts
    75
    It sounds sort of easy, but how to implements it practically?
    Re-write the application using unbound forms?

    All VBA uses DAO. Is it possible to create a single global DAO connection object that would handle multiple recordsets (I've read that ADO has such capability)?

    Thanks.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Using unbound forms is a great way to go about it if you want very fast opening forms and not having to worry about keeping recordsets open. I used unbound forms where I had 3+ million records and it went lightning fast over the netork and through citrix. What I would do is: Open the recordset, populate the forms from the recordset values, and then close the recordset. When the user enters/updates the values on the forms and then clicks Save, I simply open the recordset, write to the recordset from the values on the form, and close the recordset. You won't find a better/faster way to handle multiple users using your application. Just remember when you are done with the recordset (rs for example), issue a rs.close and set rs = nothing!! Otherwise you have problems.
    Also, try and utilize the same recordsets. For example, use rs if possible as nomenclature for all your recordsets. Don't dim rs as adodb.recordset for a form and then another time dim rx as adodb.recordset for another form and then dim rv as adodb.recordset for another form unless you have to. Access has a tendency to not always release the memory from a recordset and re-using the same rs helps. If you have to open multiple embedded recordsets for a form, then you don't have a choice and need to use rs and rx and/or rv...etc..

    I would highly recommend against dimensioning a global connection recordset outside the function. This caused many problems for me. I always dim rs as adodb.recordset within the function.

    My ADO code would look like this (to retrieve a customer for example based on the form MySearchForm):
    Note: you can store the recordvalues in variables and then write the variables to the form (or vice versa) but this is just a waste overhead with dimensioning all the variables. I would only do this in cases where I wanted to test a specific value in a variable for validation. Otherwise, I like to write directly to the recordset from the form and vice-versa.


    Function getCustomer()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblCustomers where CustomerID = " & Forms!MySearchForm!CustID & ""
    rs.open strSQL,currentproject.connection,adOpenKeyset, adlockReadOnly
    if rs.eof and rs.bof then
    msgbox "No records returned."
    else
    Forms!MyDataForm!CustomerID = rs!CustomerID
    Forms!MyDataForm!FirstName = rs!FirstName
    Forms!MyDataForm!LastName = rs!LastName
    .....
    ....
    end if
    rs.close
    set rs = nothing
    End Function
    And to update to the recordset:

    Function updateCustomer()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblCustomers where CustomerID = " & Forms!MyDataForm!CustumerID & ""
    rs.open strSQL,currentproject.connection,adopenDynamic, adlockoptimistic
    rs!FirstName = Forms!MyDataForm!FirstName
    rs!LastName = Forms!MyDataForm!LastName
    .....
    ....
    rs.update
    rs.close
    set rs = nothing
    End Function

    Noticed I used rs in both cases.
    Noticed that I set rs = nothing after I close rs (rs.close)

    Writing a new customer:
    Function writeNewCustomer()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblCustomers"
    rs.open strSQL,currentproject.connection,adopenDynamic, adlockoptimistic
    rs.addnew
    ' Note this is not needed: rs!CustomerID = Forms!MyDataForm!CustomerID if CustomerID is an incremental integer field (i.e. autonumber type) in SQL Server.
    rs!FirstName = Forms!MyDataForm!FirstName
    rs!LastName = Forms!MyDataForm!LastName
    .....
    ....
    rs.update
    dim CID as integer
    CID = rs!CustomerID 'Note: I do this in the case where I want to pass the newly created CustomerID onto some other form.
    rs.close
    set rs = nothing
    End Function

    As a last note: You have some diehard DAO fans around. I'm an ADO fan myself and like the syntax a lot better (it's shorter).
    Last edited by pkstormy; 05-24-07 at 23:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2002
    Posts
    75
    Thank you for your advice and the code! I thought of using unbound forms, but the thing is that I do not have time to revise the application: this version is a prototype and later the whole application will re-written using a different language (even the back-end will be restructured) -- that's why I need a quick fix that would take care of the problem for now.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Someone must have gotten their way with revamping the backend (to non-Sql Server) and frontend (to non-MSAccess) because this combination is in my opinion better than any other software (I would only have to ask who's making the money for the redesign??) I mean what else could you ask for than an easy and quick programming tool with an awesome data server and the ability to easily integrate with other Office tools? I can design an application within a week in MSAccess that would take months in another language (and it would be client-server capable with the ability to handle 3+ million records.)

    But I guess there are better salesman out there than there are programmers.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Apr 2002
    Posts
    75
    I agree that SServer and Access are great for creating really successful database applications...only when these tools are in good hands...
    The back-end will be SQL Server, but its structure will be redesigned, since the current version is more like an example of how not to build dbs. As to the front end, the department supports several languages other than MS's ones and have many tools already developed that can be reused for the new application (the key question here is why having all that in mind they've stated developing in Access in the first place??)
    Anyway, do you think that switching to ADO and using unbound forms is the only way to prevent opening multiple connections per a user?

    Thank you.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SQL Server/vb script to clone MSAccess frontend

    vasilyok,

    I've attached a very nice vb script which you can use which clones the MSAccess front end, adds the user's login name, and opens that mdb/mde file.

    There is a list of things which can cause problems with multiple users using the MSAccess front end. For example, opening several forms based on the same recordset or errors in the code itself can cause problems. Incorrect security, missing dlls, pointing to the wrong temp directory, table locks, etc...can also be problems,etc...etc... Making the backend SQL Server helps tremendously for multiple users but you can still have problems if the frontend is not designed correctly (writing code which produces table locks can be a big headache for multiple users/client-server applications in any frontend even if the forms are unbound!!) SQL Server is great because it lets you monitor transactions and performance, create nice backups, tranlogs to backup on the hour (which take less than seconds to backup), stored procedures/triggers/views/DTS Packages/etc.. all which help maintain a healthy database.

    I've designed frontends with a SQL Server backend where the tables are bound to the form and have had no problems with multiple users. I've also designed frontend unbound forms to the tables which were MSAccess linked tables and have had no problems (but the size of the tables were not very large.) My personal experience has been that if you are going to deal with large recordsets and a lot of multiple users having the same form open (i.e. 10+ constant users), especially ones who are connecting externally via citrix, SQL Server and writing unbound forms gives you the best performance. Whether it's ADO or DAO (there are a lot of diehard DAO fans out there but I personally prefer ADO because the syntax is shorter and I think it's easier to understand. You can pretty much accomplish all the same tasks in either and I've seen debating over which one is better to write code in. I think ADO has a few more commands though which you can't do in DAO.)

    But you don't necessarily need to design unbound forms! It takes a lot of time to write the code for unbound forms whether in ADO or DAO! And you don't necessarily need to write EVERY form unbound! I only wrote unbound forms for stuff like a customer or business form where multiple users would have the same form open for a long time (Keep your MainMenu forms unbound though as users usually keep these forms open for a long time!) I think it's unneccessary to write unbound forms for a small sized table and the user opens the form, quickly enters a few items, and closes it. Keep this in mind when you decide to design an unbound form!! This is also where SQL Server monitoring of transactions opening and closing really helps! As a note, having multiple bound comboboxes on a form can really eat up transaction time, especially if the combobox rowsource links several large data tables together.

    So thus....Your answer is NO, you don't need to design ADO and use unbound forms for multiusers applications! If you have a good table structure and your code is well designed, bound forms though can sometimes be just as good (and it really helps using the vb script attached for mde/mdb frontends.) I guess I would consider using some unbound forms when I get table recordset sizes in the 100,000's for the form (and again, it's constantly opened). Others might differ on that though. It would also have to depend on what I monitor for SQL Server transactions.

    The cloning vb script I've attached again helps a lot with multiple users as each user is using their own MSAccess frontend which eliminates some of the problems you may get with users in the same mdb/mde file. You'll need to edit the vb script with notepad and change the LUName (location of the MSAccess mdb/mde file - make sure it's in " as the example illustrates). Then just have the user's execute the vb script (they can create shortcuts on their desktop to it's location where you put it - the script will do the rest - clone the frontend mde/mdb and launch the cloned file). You probably want to make an mde file which to clone off of as making an mde compacts the MSAccess file to a smaller size which means less time to clone the frontend (for 20 meg file size it only takes 1-2 seconds).

    **************
    Of course always compact and repair any mdb you work with (and also compile any code, even if you make a small coding change - make this a habit to do often in any programming tool you use!! (stressed again!!)) I can't count the number of times I've seen developers have problems with mdb files simply because they did not compile, compact and repair the mdb file! MSAccess is nice because this only takes seconds to do while other tools/languages can take minutes or longer (I remember compiling 4GL code which could sometimes take hours to compile.) Possibly another justification for using MSAccess??
    **************

    But again, I'm very bias because it's also so quick and easy to program in MSAccess!! (I've been told that 4GL is easier but those programmers hardly used MSAccess - I personally didn't see 4GL being easier. I guess it's all in what you're comfortable with). Those who make a living programming in 4GL/Progress will try and sell you on those products. Those that program in Visual Basic or C++ or some other language will try and sell you on those products. I've done VB/C++/Fortran/Pascal/Assembly/DBase/(some 4GL)/etc..etc and I'll still take MSAccess any day. Just don't tell me MSAccess isn't Enterprise level because I will (and have) proved that wrong!! (with SQL Server as a backend of course - otherwise I'd agree MSAccess isn't Enterprise level).

    Don't use the vb script though unless you've either split the MSAccess data tables or you are using SQL Server linked tables. Otherwise you end up with each user using a different MSAccess data table.

    The nice thing also about this script is that you can copy new mde/mdb code without having each user close out of the mde/mdb file and you can also see who's using the mde/mdb (there is an ldb file in the folder with the users login name) and you can also see when the last time was that they got into the mde/mdb file (creation date). It's been a VERY, VERY useful tool for myself for not just MSAccess files but other file types as well!! I hope you find it as useful as I have. Dan Wang and myself worked on creating it. Feel free to distribute it to others. Let me know if you have any problems with it (I haven't). The code is pretty standard and there's no other software needed (other than wscript.exe which is a standard Windows install file in the System folder). The only thing I can think of which could possibly be a problem is if your system administrator has somehow locked a computer from running vb scripts or somehow wscript was not installed (or deleted) during the Windows setup.

    I got pretty winded here (sorry about that.) But as you can see, I'm passionate about my business and what tools I use to make a living with.
    Attached Files Attached Files
    Last edited by pkstormy; 05-26-07 at 09:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2002
    Posts
    75
    Hi pkstormy,

    Sorry for not getting back sooner. Thank you for the script. But it sort of not clear how it would work in my situation: all our users have a copy of the .mde on their computers since there is no one centralized location available. Why would we need to create additional instance of the .mde?

    Sorry for such dumb questions.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It offers several advantages:
    1. You can copy new working code to the working directory over the top of the old code without having everyone get out of the mde file that's open by 1 or more users (when you need to copy new code and have to call all users in the mde and have them close out so you can copy new code, you'll see). If every user has their own mde and you make code changes, you have to copy that mde to every users computer. It would be better to centralize your mde.
    2. Everyone is using their own mde frontend which helps when users are running queries, code, etc (especially if you have it so multiple recordsets are constantly open). Since you have the mde on each users computer, this may not be a factor but I'd still consider centralizing the mde.
    3. You can tell who's in the mde file by simply looking at the folder and seeing a file such as myAppPaulk.ldb file.
    4. You can also see when the user LAST got into the mde file by looking at the creation date for myAppPaulK.mde (again, you can do 3 and 4 now but you need to look at each users computer and maintain the code in all those mde's. Why not centralize the mde and make it easier on yourself.)

    The script does pretty much what you have now but using the script, you only have to deal with and maintain 1 mde file verses several on every users machine (80 separate mde's is a LOT of mde's to maintain! - If you don't already, I'd definately consider a nightly routine to copy these from a central mde to each users machine but even this can produce problems.) I've always been a fan of not having to go to each users computer and copy a new mde file on their machine (what if they are out and their computer is off - you have to wait until they get back or log into their computer as admin otherwise you have to keep track of "I copied this change to this users computer but didn't copy the change to that users computer", etc..) For example, you make a field change on SQL Server - you need to go around and refresh everyone's mde. Or you change some code in your mdb file,.... Etc..etc.. Plus, sometimes 80 users can easily become 120 or 200...etc..It's just more work for you (what if a new user wants access to the mde?)

    If every different user has their own specific customized forms they get into, there are ways you can easily set this up in a central mde. It's all in how easy you want to make it on yourself. Personally, I don't like to keep track of multiple interfaces for different users and would rather have 1 interface which has a lookup table dictacting what form they would get into, permissions, etc. But I don't know your situation and maybe there's some reason you've separated mde's for all 80 users. It just seems like A LOT of maintenance work and I've never found a situation that necessitated doing this!

    As a last note, be very careful when you keep multiple recordsets open in the application. You generally want to close as many recordsets as possible and not keep these continously open. With 50+ users in the db at once, you almost guarantee some kind of problems having multiple recordsets constantly open. Try and make it so you only have a few forms open at one time. Utilize popup forms where data is entered and the form is then closed. Consider unbound forms on just the forms which are open constantly. And possibly consider "denormalizing" some of the tables if you have a separate table for a lot of things (i.e. you have a separate table to hold the city, a separate table for state, a separate table for zip, etc.) All these tables open to just return a customer/business address defeats the purpose. You can over-normalize a structure.
    Last edited by pkstormy; 05-28-07 at 22:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Paul,

    Sorry for my interruption in the topic.

    As you said to avoid using rs declaration more than once which means should try to use same recordset for many times as stated in your code..

    I tried using same record set to open different tables thru vba of a sub form. For your information I have ODBC connectivity (MSSQL Desktop Edition) with MSAccess as FE.

    I was trying your method on after update of a combo on sub form to perform some calculations. It refuses to proceed with second time use of rs in the same sub while I closed the rs and set rs = nothing. (please refer to attached jpeg pics 1 7 2)

    Where might have gone wrong?

    I am placing here my code of sub form combo;

    Private Sub Form_Load()
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ALRASASIADSN;Initial Catalog=ALRASASIA2"
    conn.Open conn

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockOptimistic
    rst.ActiveConnection = conn
    rst.CursorType = adOpenDynamic
    End Sub

    Private Sub CboProductCode_AfterUpdate()

    Me.ProductCode = CboProductCode.Column(0)
    Me.ProductName = CboProductCode.Column(1)
    Me.GroupCode = CboProductCode.Column(3)
    Me.Refresh
    '=========================
    Dim OBPurTrans As Long
    Dim OBPrice As Double
    Dim OBValue As Double
    Dim OBQty As Double
    Dim TotValue As Double
    Dim AvgPrice As Double
    '=========================

    Me.ProductCode.SetFocus

    ‘This is just to pick up single value from table so I used only aggregate functions which don’t bother me.

    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode=" & Me.ProductCode.Text), 0) + 1
    OBPrice = Nz(DLookup("OldPurPrice", "Product_master", "ProductCode=" & Me.ProductCode.Text), 0)
    OBValue = OBPrice * Nz(DSum("Openingbal", "Product_master", "ProductCode = " & Me.ProductCode.Text), 0)
    OBQty = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & Me.ProductCode.Text), 0)


    Dim PurQty, PurQtyValue, PRetQty, PurRetValue
    Dim ActualPurQty, ActualPurValue

    Dim SalesQty, SalesQtyValue, SRetQty, SalesRetValue
    Dim ActualSalesQty, ActualSalesValue

    Dim MaintQty, MaintQtyValue

    Dim X

    ================================================== ============

    rst.Open "select * from T_PurInvFoot where PurQty>0 And ProductCode=" & Trim(Me.CboProductCode)

    If rst.RecordCount >= 1 Then

    For X = 1 To rst.RecordCount
    PurQty = PurQty + rst!PurQty
    PurQtyValue = PurQtyValue + rst!Amount
    rst.MoveNext
    Next
    Else
    PurQty = 0
    PurQtyValue = 0

    End If

    rst.Close
    Set rst = Nothing

    ================================================== ============

    rst.Open "select * from T_PurInvFoot where PurRetQty>0 And ProductCode=" & Trim(Me.CboProductCode)
    If rst.RecordCount >= 1 Then
    For X = 1 To rst.RecordCount
    PRetQty = PRetQty + rst!PurRetQty
    PurRetValue = PurRetValue + rst!PurRetAmt
    rst.MoveNext
    Next
    Else
    PRetQty = 0
    PurRetAmt = 0
    End If
    rst.Close
    Set rst = Nothing

    ================================================== ============

    rst.Open "select * from T_SalesInvFoot where SalesQty>0 And ProductCode=" & Trim(Me.CboProductCode)
    ……
    ……
    rst.Close
    Set rst = Nothing

    ================================================== ============

    rst.Open "select * from T_SalesInvFoot where SalesRetQty>0 And ProductCode=" & Trim(Me.CboProductCode)
    ….
    …..
    rst.Close
    Set rst = Nothing

    ================================================== ============

    rst.Open "select * from T_MInv_Foot where Qty>0 And ProductCode=" & Trim(Me.CboProductCode)

    ….
    …..

    rst.Close
    Set rst = Nothing

    ================================================== ============


    ' Perform Calculations

    ActualPurQty = (PurQty - PRetQty) + OBQty
    ActualPurValue = (PurQtyValue - PurRetValue)

    ActualSalesQty = (SalesQty - SRetQty)
    ActualSalesValue = (SalesQtyValue - SalesRetValue)

    Stock = (ActualPurQty - ActualSalesQty) + MaintQty

    TotValue = (OBValue + ActualPurValue) - (ActualSalesValue + MaintQtyValue)

    ''To avoide overflow
    If Stock = 0 Then
    AvgPrice = 0
    Else
    AvgPrice = TotValue / Stock
    End If

    End Sub

    Sorry for my lengthy code lines but to get an answer I need to represent here.

    With kind regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails 1.JPG   2.JPG  

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe the issue here is that everytime you
    Code:
    Set rst = Nothing
    You then say
    Code:
    rst.Open "select * from T_MInv_Foot where Qty>0 And ProductCode=" & Trim(Me.CboProductCode)
    which is basically saying: nothing.Open
    Which is why it won't work.

    Perhaps you should only close the recordsets and set it to nothing when you clos ethe form or better still write your form_load event as a function and call it every time you need it.
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the dim rs as adodb.recordset. The message I was hoping to portray was to avoid using this as a global type declaration. In every function/sub procedure, you should dim rs as adodb.recordset IN the function/sub when you are going to utilize the rs connection but don't dim rs before your function/sub procedure. For example:

    Don't do this...

    Option Compare Database
    Dim rs As ADODB.Recordset <- Notice where rs is Dimmed (after the Option Compare Database-try to avoid doing this).
    Function isAdmin() As Boolean
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from dbo_Admin where LoginID = '" & GetUser() & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    ....
    End Function

    but instead do this...

    Option Compare Database
    Function isAdmin() As Boolean
    Dim rs As ADODB.Recordset <- Notice where rs is dimmed.
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from dbo_Admin where LoginID = '" & GetUser() & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    ....
    End Function
    Last edited by pkstormy; 05-29-07 at 13:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    On the point of dim multiple recordsets. Someone correct me if I'm wrong but what I found was that if I dimmed multiple recordsets (i.e. dim rs as adodb.recordset one time, and then dim rst as adodb.recordset in another sub/function, and then dim rx as adodb.recordset in another sub/function), I found that the size of my mdb/mde file tended to grow larger verses if I re-used dim rs as adodb.recordset.

    If anyone's experienced differently, let me know.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This begs the question: What's the point in opening multiple connections to a single data source?
    That made sense in my head anyway...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •