Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    best way to join access and sql server (2000)

    I have an access 2000 mdb front-end and a sql server 2000 backend. The front-end is distributed among 100 or so users. Recently, I've been having some locking problems in that one process is locking out others. I looked at the properties in sQL Server Enterprise Manager and can only see part of the sql statement that's causing the problem. It seems to be a rather large Select statement on our biggest table. I don't understand why this would lock out other users, but as I said, I can't see the end of the statement, so I don't know what the criteria or any JOINS are. We don't have our users log in, so I can't tell who's behind the locking process and if it's always the same person. Basically, what i want to ask is if I have things set up in the most efficient way or might it be better for my performance to use an adp? Or, perhaps, there are other performance-improving solutions??? I'd appreciate any suggestions.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: best way to join access and sql server (2000)

    Several questions:<UL><LI>Are you running any triggers or SQL stored procedures that might be executing when people make changes?
    <LI>What are the error messages you get?
    <LI>A SELECT statement should not be causing locking problems in SQL Server - is it possible there is more hidden in the SQL statement?
    <LI>Are you running integrated security on SQL Server?
    <LI>Are you linking to the SQL tables using ODBC?
    <LI>Are you using views in SQL Server, or are you linking directly to a table?[/list]The answer to these questions should help to narrow down the list of possible causes.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: best way to join access and sql server (2000)

    Thank you, Wendell, for responding. Here are the answers to your questions:

    1. No triggers or stored procedures when changes are made, HOWEVER, there used to be a trigger that ran on INSERTS and UPDATES. I removed this trigger and put the code to run the updates that it did into the after_update procedure of my form.

    2. I don't get any error messages, the program hangs and then crashes.

    3. There may be more to the SELECT statement, but I don't know how to view it.

    4. No integrated security - at least as far as I know.

    5. I'm linking to the SQL tables using my Linked Table Manager. I have a DSN on my computer that provides the link to the back-end on the network.

    6. NO views , I'm linking directly to the tables.

    Hope that helps. Looking forward to hearing more.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: best way to join access and sql server (2000)

    Well, the fact that Access hangs and then crashes with no error message suggests you have a problem either with the Access front-end database or with the installation of Access on some PCs, as locking errors do generate an error message. So I don't really think SQL Server is at the root of your problem. So some comments on your responses, and then more questions:

    1 - If there is a trigger running on SQL Server for UPDATES, then if you attempt to replicate that code in Access, it should benerally be in the before_update procedure, not the after_update. Unless you are using DAO or ADO code to do further manipulation on the record involved, then there is no change in the SQL Server record.
    3 - How do you know a SELECT statement is involved? Are you looking at the data source for the form? If so, you can do a SHIFT-F2 to zoom the value you are looking at and you should then be able to see the entire statement. You could also highlight the data source and do a copy, then paste it into a word processor where you caould also see it.
    4 - Check with the SQL Server administrator, unless that happens to be you. If it's you, then you probably have serious security concerns - anyone logging in as sa or dbo can do virtually anything to you SQL Server database, including deleting it, or deleting critical tables.

    Now for the further questions:
    7 - What service packs/releases have been applied to your Access/Office installations? You should definitely be at least SR-1A, and SP-3 is highly desirable.
    8 - Does each of your 100 users have the Access front-end downloaded to their workstation?
    9 - Does each user use the default system.mdw file located on their workstation?
    10 - Is your Access front-end repaired and compacted on a regular basis?
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: best way to join access and sql server (2000)

    Wendell - thank you again for your help.

    - In answer to your comments:

    I am using ADO to make changes to the record, but I need the record to be entered into the db first, that's why I run the "trigger" code in the after_update procedure.

    I know a SELECT statement is involved because I open SQL SERVER ENTERPRISE MANAGER, look at the locking activity, right-click on the process with the red exclamation mark that's "blocking" others, and select properties. That gives me the process SQL statement, but in a window that I can't expand (I think) and the statement is longer than the window shows.

    You're right, we do have serious security issues. Anyone who has access to our server can do horrible things to it. That's another story.

    - In answer to your questions:

    7. I'm working with Access 2002 SP-1 with the default file format set to Access 2000. However, my users generally don't have Access installed on their machines. They are using access runtime to run the front-end.

    8. Each user has the front-end installed on his machine.

    9. I don't know - what's the system.mdw file?

    10. Yes, whenever it closes.

    I hope this helps. I really appreciate your input!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: best way to join access and sql server (2000)

    OK TJ, I think we are making progress.
    Are you sure you need to save the record before you make changes? That is frequently the cause of these kind of errors in SQL Server - the update tries to process before the insert has actually completed. That is why you may also occasionally get this kind of error with a Trigger running - it usually happens when you try to update a record just after you've added it.
    If it is really a SELECT statement that has the record locked, that is unusual. What sort of record locking do you have in your Access front-end, and what options have you selected in SQL Server in that regard?

    9. The Access user security file is named system.mdw - that's where user IDs and GroupIDs and some other security related things are stored. By default, any access application logs in as Admin as long as no password has been set for the Admin user. Since you are using a run-time version, the system.mdw is probably being deployed with the runtime.

    Since you are using runtime packages on your workstations, this is a pretty tough thing to debug. Can you readily repeat the failure, or is it something that occurs at random on various workstations. Also, how frequently does it occur - a few times a week, a few times a day or a few times an hour? If it is repeatable, you could set the debug trace for your ODBC connection. It tells you everthing that gets passed to and from SQL Server, and you can see what it going on. Two cautions - it generates large volumes of data very quickly, so don't just turn it on and let it run for long periods of time, and it only traces an individual workstation so if it happens on a workstation other than the one you are tracing, it doesn't help. As sort of a bottom line, I suspect you will need to make some basic design changes to get this not to occur - we saw this kind of thing occasionally where we had subforms trying to update records that we had just saved, and it took some significant redesign to resolve the issue.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: best way to join access and sql server (2000)

    I just spent some time reevaluating those updates with my boss. It turns out that I can probably get rid of them by setting properties of the form instead of running SQL statements. I'm really hoping this will take care of the problem especially because there's another aspect of the situation that I didn't mention. When the user opens the main form to add a new contact, he enters the name of the contact, and the form refreshes to fetch an ID from the database. That way, the user who added the contact can pass the contact information on to one of our salespeople by giving him the ID. Whenever the form refreshes, of course, the updates to the database run. When things are busy, this might happen many times over a few minutes, possibly causing our locking problems.

    I have set the record-locking on the front-end to "edited record". However, from what I read, Sql Server couldn't care less what the access front-end says about locking. I wish I knew how to set locking options for SQL Server, but haven't been able to figure out how! I would like to set it to row-lock.

    I don't have any security on either the Access or SQL Server ends - unfortunately.

    I can't readily repeat the failure - it seems to occur randomly. For one bad day it was happening every 15 minutes or so. The next day, we rebooted the server and since then it's been pretty rare.

    Thanks again for your help, Wendell. Can you recommend some reading material that will help me beef up on the proper design of projects with access front-ends and sql server be's?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: best way to join access and sql server (2000)

    I presume what your contact table does for a key is use an autonumber field - SQL calls them autoincrement if I remember correctly. I would suggest you instead use a table that contains the next ID number, and when you start an add, read that value, use that for the key, and increment it by one and save the record. Since you are already using ADO, it should be pretty easy. The problem with ODBC attached tables is that SQL Server doesn't actually assign the value for an autonumber until it actually does the insert. Doing it that way would allow you to only do a save at the end. We did it that way on a similar design where we wanted to populate one contact to many address, phone and email tables at the same time, and it works pretty well.

    You are correct - Access ignores the locking strategy for ODBC linked tables, or at least it is supposed to. As to further reading on the subject, There are a couple of books I like - the most pragmatic is volume 2 of Alison Balter's Guide to Access 2002 Enterprise Development, and one by Microsoft Press called Microsoft Access Developer's Guide to SQL Server. Both these (and the Enterprise version of the Access 2002 Developer's Handbook) are for 2002, but apply almost across the board to 2000 as well. In fact, if you are using ADO, you also should upgrade your version of the ADO to at least 2.5 or 2.6 from the version 2.1 that comes with 2000. Hope this helps.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: best way to join access and sql server (2000)

    Thank you Wendell, you've been very helpful. The idea to use another table to store the next ID is a good one, but I'm not sure if it will work for me. I kind of like the fact that the record saves with some information initially. That way, all is not lost if the program crashes while the user has a contact form open. Also, there are times when we add new contacts to the table in bulk and I would then have to update a separate table with the new "latest" ID. It may be too much maintenance for the amount of performance that it saves. Thanks for the reading tips. I hope to make use of them really soon [img]/forums/images/smilies/smile.gif[/img]

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: best way to join access and sql server (2000)

    Your worry about crashes is valid, but if the minimum amount of data (name, sex?, salutation, etc.) is entered on the main form, and you use a subform for address, phone, etc, the record is saved automatically as soon as you move focus to the subform. The problem you are encountering happens most frequently in my experience when you save a record and immediately try to edit it. With the method I suggested that no longer happens, and the record is saved only once.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: best way to join access and sql server (2000)

    I hear your point. We actually have to reevaluate the entire design of the database and its forms and see what we can do more efficiently. I hope to get started pretty soon. In doing that, we probably will implement the solution you suggested.

Posting Permissions

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