asked on

Access ODBC Connection to SQL Express - Connection Busy

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.

Jeffrey Coachman

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

untitled.JPG

nigelr99

ASKER

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 Coachman

Not really, each situation is different, you just have to click the "?" to get specific help on each option.

nigelr99

ASKER

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 Coachman

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

ASKER CERTIFIED SOLUTION

nigelr99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nigelr99

ASKER

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