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.