Avatar of nigelr99
nigelr99
Flag for United Kingdom of Great Britain and Northern Ireland 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. ODBC Error Message
Microsoft SQL ServerMicrosoft AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
nigelr99

8/22/2022 - Mon
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nigelr99

ASKER
This was the only solution which made the error go away! Not ideal but it works.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.