Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Db Connection

Posted by: tykra Dec 7 2017, 05:49 PM

I recently migrated to SQL Server; I have finally completed configuring everything to work with SQL and it is working great on my local machine. When I save a copy of the front end to another users pc the connection fails. The linked tables used a file dsn, which I thought was dsn-less by default and once I saved the front end it would be included.

The database is set up with windows authentication and we use AD; do I need to set every end user up with read access on the SQL server? Should I set a username/pw on the DB? Any thoughts on possible things to look into?

Thanks for the help.

Posted by: theDBguy Dec 7 2017, 05:52 PM


Yes, did you give your AD users permissions to the SQL Server instance?

Posted by: GroverParkGeorge Dec 7 2017, 06:03 PM

Okay, a DSN, whether file or system, resides on the computer where it was created.

If you want to transfer the Accdb FE to another computer and use that same DSN, you'll need to copy/paste the File DSN, or create a new system DSN on the target computer.

I'm not sure where the notation comes from that "it would be dsn-less by default".

The issue of whether you use AD and Windows Auth or SQL Auth is separate. Either one can be set up in a DSN or DSN-less.

You might try

Posted by: tykra Dec 7 2017, 07:35 PM

DBguy; my username is the only one currently set as a user on the SQL side. I had the obviously incorrect notion that I could handle the connections on the access side.

Do i need to set up every single user as a db user? Possibly I could forego that and set a username/pw for the database?

Grover; I had read it on another site ...

A database which used a File DSN links does not need the DSN created on each target workstation, all it requires is the appropriate ODBC driver to be available.

Below is a comment from one of our customers (***********) and we thought that this is an additional appropriate clarification.

In creating a File DSN, you have to create a .DSN file, however the connection that is created is “DSN-less”. If you move the Access application with the connection in place to a users machine, then they don’t need the .DSN file, however it had to exist on the machine when the connection was originally created.

By your comments I am going to guess that is not accurate?

Also found this on another site

When you create a linked table, simply use the ribbon import and link – and then ODBC database. Just choose a file DSN. The reason for this is that access by DEFAULT will use a DSN-less connection. In simple terms, this means when you link the tables, then you can distribute your applcatation to each workstation and there no need to setup a SYSTEM/Machine DSN.

So just keep in mind that use the default file DSN – once Access creates the link to SQL server, then such links are DSN-less, and you don’t need any setup on each workstation.

That was from AlbertKallal wink.gif Albert how about taking a rookie to school?

Posted by: theDBguy Dec 7 2017, 08:42 PM


Yes, SQL Server requires user permission to access the database. You can either assign permission to each user or assign permission to an AD group and make all users a member of the group.

Posted by: tykra Dec 7 2017, 08:52 PM

Thanks DB! I will give that a try and see how it goes.

Posted by: theDBguy Dec 7 2017, 09:37 PM


You're welcome. One way to test Albert's comment is for you to log in to another computer and see if the file still works for you.

Good luck!

Posted by: AlbertKallal Dec 8 2017, 02:04 AM

Yes, I stand by that statement.

However you have to “close” read what I stated:

Access automatic uses a DSN-less connection and you do NOT need to copy the DSN to each workstation. (this is correct!!).

However all my comments were in regards to a FILE dsn! What I stated does not apply to the other types of DSN (system/machine is another choice you get – but I STRONG recommend you avoid these other choices since they store their values in the registry).

So this ONLY applies to a FILE dsn you create and use to link the tables.

I should point out that a “file” DSN is quite much the default if you just go along with the default prompts.

So the ODBC prompts do result by default in a FILE dsn. And I STRONG recommend you do use a file DSN.

So in fact I am comfortable to state that Access by default WILL create DSN-less links for you.

Note that ***if*** you use a “system” DSN, then what I stated does NOT apply (you must re-create the DSN on the target machine – and WORSE that setting is in the registry – don’t bother!!).

So to keep this simple?

You do NOT need to copy the FILE dsn “file” to the target machine.

Access ALWAYS ALWAYS uses and creates a DSN-less connection for you when you use the linked table manager (assuming you chose a file dsn).

The FILE dsn is ONLY used during the link process – after that you can toss it out, or better in this context is copy the front end to any workstation and it will work just fine.

So you do NOT need VBA code to re-link as DSN-less, since that is the default that Access uses. So to get DSN less connection, you can quite much do nothing and not have to write one line of VBA code.

It seems like your plan is to create a single SQL logon and use that for everyone
(a rather fine and common setup if you not going to use windows authentication for SQL server).

And in fact even on “domain” networks (windows authentication for SQL server), I STILL most often use the SQL logon, as then I don’t have to mess with the “domain” server and bother the IT department if I create a new database or some such (too much pain to manage and give every user rights to SQL server).

I don’t really care much about the windows logon, but only that everyone can use my Access database linked to SQL server. So you save tons of headaches by adopting this approach (and that seems what you are doing anyway!).

Group hug!!! - hope this helps!

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Posted by: tykra Dec 8 2017, 07:39 AM

".... after that you can toss it out"

I did not know that; I thought the .dsn file had to be saved in a location that all users had access to. COOL!

It seems like your plan is to create a single SQL logon and use that for everyone

I'm just looking for the best solution to get the application working on end-user machines. smile.gif Getting IT to add users to a db group that is read/write to the db seems like a viable option; would be as simple as adding the new users to the AD group. OF course using a single login would mean there is little to no additional leg work; which sounds great.

I will do some testing today. thanks for chiming in Albert!

Posted by: GroverParkGeorge Dec 8 2017, 08:50 AM

Albert, although I've seen that before, it's not been my experience.

I was, therefore, surprised to see that creating a link via a FILE DSN does indeed result in linked tables that are stored with a DSN-less connection.

I have always avoided doing that because, at some point, I must have had the opposite experience and never learned better.

Thanks, as always.

Posted by: AlbertKallal Dec 8 2017, 01:39 PM

Thank you kindly George.
I kind of feel this is a “good” message to the general community.

In fact I think we just not given a lot of “thought” to this issue and what Access does has not really been pointed out to the community with a “high” degrees of clarity.

So “file” DSN’s IN Access are by default linked as dsn-less. In fact, I don’t think you can link tables and get a “dsn” linked table if you try! (Again, only for file dsn).

The big “hint” is no “DSN=” results in the string after linking.

It actually quite a nice default in Access, since it means Access tends to be by default DSN-less. That is good since then as a general rule you can link to SQL server, and then distribute that front end.

And due to “recent” discussions, you also find that these manual re-links also preserve the PK for linked views. But using code – even dsn re-link code DOES loose the PK for a linked view.

So Access does with ease create dsn-less connections, and even using the linked table manager and no re-link code also preserves the PK for linked views.

Using re-link code will “loose” the PK for linked views.

Group hugs all around,

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Posted by: GroverParkGeorge Dec 8 2017, 02:06 PM

Actually, I checked an older application that I support. It does show the file DSN's stored in the Connection field. I think that's where I got the idea.

Posted by: tykra Dec 8 2017, 04:26 PM

I tested this today by logging into another computer; the application launched without issue.

Thus the issue I was having is that the users did not have permission to access the DB; very easy fix there.

Thanks again!

Posted by: theDBguy Dec 8 2017, 04:29 PM


Congratulations on sorting it out. That's the difference between Access and SQL Server. Access can allow anyone to open the file, but SQL Server requires a little bit of setup because it has better security features than Access.

Good luck with your project.

Posted by: PDTech Jan 21 2018, 04:23 AM

Thanks Albert

I had it in my head (and all my solutions for at least the past 5 years!) that we had to create the DSN-Less connection via code. I have been using Doug Steele's solution for this (which has served me very well) but will experiment with the FILE option in future!