Error when using ODBC connection in user account

My finance team have an Access db that uses ODBC to pull data from an ACcess Accounts SQL server. I have a DSN set up in the domain admin account that works but when I set up the same DSN in the same way in the user's domain account, it won't work. If I create a new blank Access db in the same user account and link it to the SQL server, it works. I'm out of ideas, any suggestions?

Thanks
TangibleITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I use DSN-less connections, so I don't have to install DSNs on individual computers.

Check out this site for instructions.

I'm on my iPad or I would copy the procedure I use.  I have a subroutine which I simply pass a database name and a schema name (optional) which builds the connection string, creates a local table with a name that drops the normal "dbo_" prefix that you get when you link to a SQL Server table, sets the tdf.Connect property and the tdf.SourceTableName property and creates the link.

This is a little more taxing than using the External Data - ODBC dialog, but is easy for me.
0
TangibleITAuthor Commented:
Hi Dale, thanks for the suggestion, but that all looks like it might stretch my limited Windows skills unfortunately. I'd prefer to keep things simple.
0
Dale FyeOwner, Developing Solutions LLCCommented:
are you sure the user has permissions on the SQL Server?

Are you using Windows or SQL Server authentication for access to the SQL Server?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TangibleITAuthor Commented:
I did some more testing and if I use another domain account, it still doesn't work. So there seems to be something around the permissions for a standard domain account as opposed to a domain admin account that makes a difference.
0
TangibleITAuthor Commented:
The SQL Server authentication I'm using in the DSN is an admin account I think, sa.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Define what you mean by "it won't work" with the other account. Can you connect, but not edit/write data? Or can you not connect?

FWIW, I agree with Dale - you have a permissions issue on the SQL Server.
0
TangibleITAuthor Commented:
Hi Scott, thanks for you help on this. When setting up the DSN the 'test connection' button returns a successful test. When the call is made from within the Access DB it returns a connection failed message. If I create a new blank Access DB and link to the tables it works, so there appears to be something different about this particular Access file, within this type of user account. I'm using the same credentials to connect to the SQL server at all times, so I don't quite see how the permissions on the SQL server could be affecting this? I have to admit though that my knowledge of SQL is extremely limited.

Cheers
0
Dale FyeOwner, Developing Solutions LLCCommented:
It sounds like you either have a corrupt database, or the tables that are linked to the original database have invalid DSN connections.  If linking the tables into a new Access DB works, with your current DSN, then do that, then import all of the other objects from the old Access db into the new one.
0
TangibleITAuthor Commented:
OK, I'll have a chat with the guy who built this db and see how much work is involved. However, the db works fine in a domain admin account though, how would you explain that? Logically, if there were some form of corruption in the db it wouldn't work under any circumstances?
0
TangibleITAuthor Commented:
I'm told it would take too much work to rebuild this db and given the user's infrequent use of the tool, my Head of Finance would prefer not to go down this route. Like me he feels the problem lies elsewhere. What is different about a standard domain user account and a domain admin account that causes the db to stop working? Are there permissions on the workstation, or on the fileserver where this db resides perhaps, that are wrong?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've always found it easier to create the DSN directly in Access, instead of creating one with the ODBC applet and using that.

Note too that if you're using the 32-bit version of Access on a 64-bit machine, you must use the odbcad32.exe file found in Windows\Syswow64 and NOT the applet in the control panel.

Also, you might consider deleting the LINKED tables in the database, and recreating the links. You do this with the External Data ribbon item - select the "ODBC Database" item and follow the prompts. Again, be sure you're using the correct applet to create the DSN. Unless you're running 64-bit OFFICE, that should be the one found in SysWOW64, and not the Control Panel.

I'm told it would take too much work to rebuild this db
By "rebuild", we don't mean "recreate". We mean to create a new, blank database and use the Access Import feature to move everything from the old db to the new. Shouldn't take more than about 5 minutes, assuming you grab a cup of coffee during the process :)
0
TangibleITAuthor Commented:
I'll close this as we haven't solved it and my colleagues have decided they don't want to invest any more time in fixing the issue.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TangibleITAuthor Commented:
No one was able to solve the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.