Link to home
Start Free TrialLog in
Avatar of nigelr99
nigelr99Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Access ODBC Connection to SQL Express - Connection Busy

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. User generated image
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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

Avatar of nigelr99


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?
Not really, each situation is different, you just have to click the "?" to get specific help on each option.
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)
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
Avatar of nigelr99
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This was the only solution which made the error go away! Not ideal but it works.