Error when using ODBC connection in user account

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.

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.

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?

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.

The SQL Server authentication I'm using in the DSN is an admin account I think, sa.

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.

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

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.

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?

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?

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 :)

No one was able to solve the issue.