Access ODBC Connection to SQL Express - Connection Busy

nigelr99
nigelr99 used Ask the Experts™
on
Hi,
I have an Access 2007 front-end connecting to SQL Server Express 2008 R2 back-end database.

An 'admin' form consists of several sub-forms to manage small back-end tables. The sub-forms have views as their record source and are opened as 'snapshots'. I have buttons to add new records, which open up unbound forms and then use stored procedures to add the new records.

My test system runs sql server on xp pro with access running on another win 7 pc and everything runs as expected but on a client system I get the shown error when trying to add new records. They have a dedicated win7 machine running SQL Express 2008 R2 and Win7 clients.

Any suggestions please? Thanks. ODBC Error Message
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Take a look at some of the ODBC settings in Access that have to do with this:

untitled.JPG

Author

Commented:
Thanks.. I'll take a look at this when on site in the next day or so. Are there any 'recommended' values for SQL Server?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Not really, each situation is different, you just have to click the "?" to get specific help on each option.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I've accidentally managed to replicate this problem on my test machine and it was by using a file dsn instead of a machine dsn. Any ideas why this would cause a problem? I was using a file dsn for ease of setup at the client's premises.
(I tried modifying the values hghlighted above but none of them seemed to make any difference to the error received)
Thanks
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
A file dsn will be available to all who have access to the file
A Machine Dsn will be available to anyone on that machine
Commented:
I managed to fix this error by using a different connection string (using sqloledb) but have still to understand why the error occurred.

Author

Commented:
This was the only solution which made the error go away! Not ideal but it works.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial