Results 1 to 3 of 3
  1. #1
    payout1985 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    1

    Access 2007 sql server linked database closes slow


    We have a perplexing problem. We recently converted to 2007 from 2003. We have a large database with a lot of linked sql server 2005 odbc tables that is used by about 15 users (many times all at once). Response times on queries is good but the problem comes with closing the database. In 2003 the database closed in about 5 seconds; in 2007 the database closes in about 30-35 seconds. I have googled this problem to death and have come up with zero answers. We have converted this database over to .accdb format and that did not improve the problem. The closing problem seems to be when there is a lot of users in the database because if I go into the database after 4:30 PM and close it the darn thing shuts down immediately. Also, the compact on close is unchecked. Is there any way to optimize this database to make shorten this shutdown time? Thanks for all help in advance.

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    See this link: http://www.dbforums.com/6274786-post19.html

    It's a vb script I created 10 years ago and it basically entails having the users open the MSAccess accdb/accde/mdb/mde via a vb script instead of opening the MSAccess file itself. I guarantee it will solve any and all issues with performance and is ideal for linked SQL Server tables (I have all my users open my accdb or mde's via a vb script and use linked SQL Server tables in all my apps). Basically all the vb script does is it copies a "source" accdb, accde, mdb, or mde file I have and it pastes it with the user's windows loginID and then opens that pasted accdb/accde/mdb/mde file.

    Since each user is in their own (freshly) cloned copy of the source file (instead of 15 users sharing 1 MSAccess file), the performance of the MSAccess accdb/accde/mdb/mde will never cause you any performance issues ever again (as well as never getting a 'locked by another user' message or any other issues) I actually have 50+ users in my MSAccess app using this method and my users experience optimal performance of the MSAccess app since again, they're each in their own cloned copy of the source. Using this script, you could have unlimited (100+) number of users without a single hiccup in your app.

    Again, I just create a vb script for each of my MSAccess applications and just have the user's open the vb script instead of the MSAccess file. It has saved me many headaches over the last 10 years.

    Plus it has many other advantages (I can tell who's currently in the db as well as when they last opened it or copy new source code at any time without making all the users close out of the app.) If 1 user happens to corrupt the *.ldb file, it won't affect any other users and I just have them re-open the app via the vb script (since it will copy a new freshly cloned copy, it automatically fixes a corrupt *.ldb file.) I never ever have to hunt down all the users who have the MSAccess file open and make them all close out so I can fix a corrupt *.ldb file. I also never ever have to compact/repair the use's MSAccess file since again, each user gets a freshly cloned (non-bloated) copy of the source file each time when they run the vb script. I can also copy a new "source" mdb/mde file at any time without making any users close out of the app (I just send them an email saying I've copied a new source file and tell them to cloes and re-open the app via the vb script again to see the new code.)

    I use this script religiously for all my apps. The actual code in the vb script is only about 15 lines of code and you only have to change the 1 line of code (ie. LUName) in the vb script to point to where your "source" accdb, accde, mdb, or mde file is located (or the line of code which does the pasting of the file to the appropriate *.mdb/mde to whatever you're using if it's an accdb or accde). Just make sure the user's always open the MSAccess file via the vb script instead of the MSAccess file itself. (I put all the vb scripts for each of my apps in 1 folder and have the cloning done in another folder). All my users then only have to go to 1 folder to open any database by running the appropriate vb script).

    Note: since the dbforums is experiencing problems with the attached zip files, I put the actual code for the vb script in the post itself.

    I guarantee you it will save you many, many, many headaches.

    Here's the code in the vb script (and also in the posted thread - see post for more details):

    'vb script created by pkstormy -- make sure to save as *.vbs extension so it can be executed.
    'Use notepad to create this and just copy/paste the code below into a notepad file. Again, save as *.vbs extension.
    'Edit/change the appropriate code as commented below
    'note: when saving, change *.txt to *.* files and again, save as *.vbs file (ex: MyAppName.vbs)
    'Then just have the user's double-click on the MyAppName.vbs file to execute instead of your MSAccess file.

    Set WshNetwork = CreateObject("WScript.Network")

    'Now simply grab the user's windows loginID and save to variable GetUser to use later in pasting
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing

    'change below LUName to point to the location of your "source" MSAccess file, ideally use UNC name
    LUName = "c:\HelpForOthers\PKSampleXP.mde"

    oldname = LUName

    'change below to "accdb" or "accde" or "mdb", whichever you're using. Below simply adds the GetUser to newName variable
    'ex: PKSampleXP.mde becomes 'PKSampleXP<LoginID>.mde for newName variable
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'below simply copies a "source" file (oldname to newName) and pastes it
    retval = objFSO.CopyFile(oldname, newName, True)
    Set objFSO = Nothing

    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")

    'now it just simply opens the cloned copy (ie. PKSampleXP<LoginID>.mde)
    objShellrun "MSAccess.exe " & newName

    Set objShell = Nothing


    In regards to your specific problem with the db taking 30 seconds or more to close, without seeing the code or size of the MAccess file itself or the network setup, I might try deleting all the linked tables and re-linking them. Also make sure you don't have an ODBC named slightly different. I've seen an mdb/mde close slowly if a specific user is still "sitting" in the mdb file which ties up the *.ldb file. Since MSAccess has to maintain who's using the file within the *.ldb, my guess is that it might be trying to resolve either a slow user connection to the *.ldb or one who maybe doesn't have full permissions to the folder itself where the mdb resides or possibly slow mapping of the drive where the MSAccess file resides (there could be a few different reasons). I would also try possibly moving the MSAccess file to another folder for testing. What would be key is seeing which users are still in the MSAccess file when it's slow closing for other users. One specific user could be tying it up for several different reasons. If you're not using an accde or mde, I would also create one for the users. I also found having some users opening an MSAccess file with 2007 and others with 2003 didn't work so well with some specific coding. I personally still prefer 2003 over 2007 only because I know where everything is in 2003 (versus spending hours trying to find where Microsoft decided to put it in their somewhat dumbfounded reason for moving it somewhere else within the menu system in 2007 which pretty much says "screw all the veteran developers who have been developing with 2003 and prior since 1992").

    I wouldn't have it compact/repair though on close. I usually frown on this technique since it often leads to a corrupt *.ldb file with multiple users or other problems and since you're using SQL Server linked tables, there's really no big advantage for any MSAccess tables to doing this on close.
    Last edited by pkstormy; 08-22-2010 at 08:52 AM.

  3. #3
    martinhough is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    4
    Hi PK

    I've successfully used your VBS coding to run a DB with several users at once (thanks, it was great).

    I have just had to change the location of the folder on the network where the db (front end and back end) were located. I have re-linked the two halves of the db and updated the shortcut vbs. This now presents me with an error and I'm perplexed as to where the problem lies. I've enclosed the coding with my locations included. The error I get is "The command line you used to start MSAccess contains an option MSA doesn't recognise". The file it reports it is trying to point to is \\tdcfandp01\tdcdata\leisure.mdb which is bizzare as nowhere in this coding is this file (which doesn't exist btw) listed. What have I done wrong with this? Am I missing something obvious here?

    Here's the code;-

    Set WshNetwork = CreateObject("WScript.Network")
    'Now simply grab the user's windows loginID and save to variable GetUser to use later in pasting
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    'change below LUName to point to the location of your "source" MSAccess file, ideally use UNC name
    LUName = "\\tdcfandp01\tdcdata\Leisure Facilities & Resorts\Leisure\SharedDbs\Orders.mdb"

    oldname = LUName
    'change below to "accdb" or "accde" or "mdb", whichever you're using. Below simply adds the GetUser to 'newName variable
    'ex: PKSampleXP.mde becomes 'PKSampleXP<LoginID>.mde for newName variable
    newName = Replace(LUName, ".mdb", "") & GetUser & ".mdb"
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'below simply copies a "source" file (oldname to newName) and pastes it
    retval = objFSO.CopyFile(oldname, newName, True)
    Set objFSO = Nothing
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    'now it just simply opens the cloned copy (ie. PKSampleXP<LoginID>.mdb)
    objShell.run "MSAccess.exe " & newname '(replace **** with . run without space)
    Set objShell = Nothing

    Thanks in anticipation

    Martin

Please reply to this thread with any new information or opinions.

Similar Threads

  1. .adp in access 2007 to SQl server 2008
    By NoellaG in forum Access
    Replies: 5
    Last Post: 09-07-2010, 09:18 PM
  2. Linked Table To SQL Server?
    By snkscore in forum Import/Export Data
    Replies: 3
    Last Post: 12-09-2009, 06:36 AM
  3. Replies: 0
    Last Post: 03-09-2009, 12:20 PM
  4. Slow Access 2007 Project
    By pbyrum in forum Access
    Replies: 0
    Last Post: 10-23-2008, 05:43 PM
  5. Replies: 1
    Last Post: 12-09-2005, 09:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums