MS Access DSN Less Connection General Network Error

Hi All,

I'm having a bit of difficulty creating a DSN-less connection in MS Access. I'm building a database that will be distributed to mutliple users and I don't want to have to set up ODBC connections on each PC. However, when I test the connection string on my PC something odd seems to happen. When I use a trusted connection it works fine. However, when I try to embed my UID and PWD into the string (which I will be embedding a generic UID/PWD on rollout since the end users don't have individual access) it does not connect and I get the message:
 Connection Failed
...
General network error. Check your network documentation.

The code I'm using is as follows:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

I'm calling it through an AutoExec Macro using:

AttachDSNLessTable ("task", "task", "PRODSQL12\WH1", "ImageRight", "ACCESS\myUID", "myPWD")

But it works as:

AttachDSNLessTable ("task", "task", "PRODSQL12\WH1", "ImageRight", "", "")

Does anyone have any thoughts on the cause? Any help on this would be greatly appreciated! VBA is not my forte, so it's probably something simple I'm messing up! Thanks in advance for the help!
uga_godawgsAsked:
Who is Participating?
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.

Kelvin SparksCommented:
Have you correctly created this generic user at the Server end. The user must be granted login rights in generic security for that SQL Server, and then assigned appropriate rights to the database.

An initial look at your code appears OK, and I have used these many times. Also, I'd create the sConnect first before the loop to reduce the number of times the connect is created.

Kelvin
0
uga_godawgsAuthor Commented:
I'm actually using my login and password right now instead of the generic one since I definitely have rights. The odd thing is that if I let it go to trusted user (leave out my UID and pwd in the Autoexec call) it works correctly. It's only when I put in my UID and PWD in the string that it gives the error. I double checked to make sure my login matches the SQL Server login. Is there something the network guys could've done to prevent it from working when it's not getting connected using a trusted connection? I'm just kind of stumped.

Also, where should I put the stConnect? Again, VBA isn't my strong suit - kind of learning as I go along.

Thanks,
Dave
0
Kelvin SparksCommented:
Firstly, can you connect to the database using SSMS (SQL Server Management Studio) and connect to database using SQL Server Authentication and enetering the username and password you are trying to pass in the connection string?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

uga_godawgsAuthor Commented:
No issues connecting to the database using SSMS. Granted, SSMS is set up to use my NT profile automatically, so no entering login and pwd. Although, in my research, it looks like there is a way that the network/DBA guys can set it up so that you can only connect to the database using a trusted connection, which may be what they've done. I just am not sure how to go about seeing if that's what happened without going to the network/DBA team and asking directly.
0
Kelvin SparksCommented:
This is what I am sayaing, when you get to the SSMS prompt to connect, change it from Windows Authentication to SQL Server authentication. Thrusted connection is windows authentication, passing a UID and PWD is SQL Server Authentication. Can you change to SQL Server authentication and enter your username and password and still log on. I suspect not. A SQL Server can be configured for Windows authentication or SQL Server authentication or mixed (both). From memory, a UID cannot be both.
0
uga_godawgsAuthor Commented:
Sorry - was out of the office. I changed over to SQL Server Authentication and it failed. I talked to our network team and they have it set up so that you can only access it using a trusted connection. Their solution is to create a group and put all users in that group and provide read only access. I guess that will solve it.
0
Kelvin SparksCommented:
Yes, was going to be my suggestion. Usually you create an AD Group and make that a trusted user. The appropriate permissions are than assigned to that user. Staff members are then added to (or removed from) the group and inherit the permissions of the group. You just have to be careful when users belong to more than one group that you have the permission just right for your database.


Kelvin
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
uga_godawgsAuthor Commented:
So, I can still use the trusted user connection in the code at the beginning of this post, right? Then, if i follow you right, when the user runs the code, the server sees that the user is a member of X group who has permissions to read tables in said server and they'll have access to it?
0
Kelvin SparksCommented:
Yers you use the trusted connection, which picks up your windows credentials - these are matched to your membership of the AD group and you get the rights for every group you are a member. Makes it quite maintenance free.


Kelvin
0
uga_godawgsAuthor Commented:
Excellent. Thanks for your help!
0
Kelvin SparksCommented:
Great - good luck with it.
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 Access

From novice to tech pro — start learning today.