2

First of all I don't know anything about SQL, so I apologise if I make no sense.

I have created a MS access database and our IT area migrated the tables to SQL server 2008 and linked the tables to the front end.

The database works fine, until the load increases (around 1000 users), then whenever trying to submit a record the user gets the following error:

Error 3155 ODBC--insert on a linked table 'tblMain' failed. [Microsoft][ODBC SQL Server Driver]Query timeout expired (#0)

That error occurs for about 90% of users, basically making it unusable. At around 4pm when most user leave for the day, the database begins to work again without issue.

The IT area say everything is fine on their end, there are no limits set to users or locks, and the issue is with the front end and the code being too complex. But there is no way that's it, because the code behind the submit button is as simple as it gets.

DoCmd.GoToRecord , , acNewRec

I don't know what to do as IT are being difficult, I just need to get a better understanding so I can get them to look into it further. So any advice appreciated.

Oh, and I got sneaky access to SQL, and if I go to database properties is says number of users = 8. This doesn't change if I open the database. Does that mean it's limited to 8 people?

Many thanks, Jeremy.

Improve this question

1 Answer 1

Reset to default
1

The Timeout Expired error you're seeing is very likely due to how Microsoft Access fetches rows from SQL Server. If, for instance, the form in question uses data-binding to populate controls on the form with data from SQL Server, and there is no filter on the form that selects a SINGLE row of data, Access will keep the results open, and "page" through the rows one single row at a time. This will prevent others from updating any of the rows in the table.

For a system used by 1,000 users, you should never ever use Access as a front-end, as tempting as that may be. Use Access (it you must) as a rapid design prototyping tool, then build out the real solution using Visual Studio or some other enterprise-level development software.

Improve this answer
4
  • Thanks for the response, so IT are correct.. lol. What you said is correct, I let access fetch the record. At the moment there is nothing on the form that displays the record number, is that all I have to do? Or do I have to write some VBA, I assumed all this stuff was automated, silly me. We are getting sharepoint 2012, and the plan is to publish it to sharepoint once we have it, is sharepoint a good solution? Unfortunately until then we are stuck with Access. Thanks again.
    – user37969
    Commented May 6, 2014 at 22:14
  • I'm understanding this more. I think! So I added the ID to the form, and noticed that it goes from (new) when form opens to nothing (goes blank), when I start entering data, and doesn't actually generate a ID till the record is saved. After some googling, I have found that the access record locking is bypassed when using a OBDC connection. So, I guess I need to know is this due to SQL record locking being set to 'page', does it need to be set to 'RID' for my purposes? How can I check this? Sorry if these are silly questions and/or you've already answered, I just don't get it yet. Thanks!
    – user37969
    Commented May 6, 2014 at 23:25
  • If you let Access do the work (ie you use Access data-binding) then you're at the mercy of Access. Every front end I've written in Access is programmed to only load the specific record from SQL, ie I have a Search form that allows the user to choose a record to display, then that record is displayed in the data form. If I want a new record, I use DAO (VB) to create the row with an INSERT statement then load only the new record into the form. You would certainly want to ensure the SQL Server tables and indexes used by Access allow row locking (which is on by default).
    – Hannah Vernon ♦
    Commented May 7, 2014 at 0:11
  • 1
    Great, thanks. You have been very helpful. I'll give the INSERT statement a whirl.
    – user37969
    Commented May 7, 2014 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.