Solved

MS Access DSN Less Connection General Network Error

Posted on 2012-03-30
11
827 Views
Last Modified: 2012-08-13
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!
0
Comment
Question by:uga_godawgs
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37789429
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
 

Author Comment

by:uga_godawgs
ID: 37789491
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37789836
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
 

Author Comment

by:uga_godawgs
ID: 37791066
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37791774
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:uga_godawgs
ID: 37808031
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 37808693
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
 

Author Comment

by:uga_godawgs
ID: 37808702
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37808739
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
 

Author Comment

by:uga_godawgs
ID: 37808805
Excellent. Thanks for your help!
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37808843
Great - good luck with it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now