• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Saving the DSN connection with a pass through connection Access 2007

I'm creating pass through connections in  Access 2007 to MS SQL 2005. When I run one I have to select the DSN etc before it will run. I saw an example on the web with an older version of Access  that allowed you to enter a connection string into the properties of the object so these prompts don't appear. Can I do something similar in Access 2007.
0
Sparky191
Asked:
Sparky191
  • 6
  • 4
1 Solution
 
jmoss111Commented:
hi sparky' this should work for you.

Jim
Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQLEXPRESS;DATABASE=MyDb;UID="
Public Const strConnect2 = ";PWD=xxxxx;ADDRESS=999.999.999.999;"
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
 Dim lpBuff As String * 255
 Dim ret As Long, Username As String
   ret = GetUserName(lpBuff, 255)
   Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
   GetCurrentUserName = Username & ""
   gUsrNm = GetCurrentUserName
     
Exit_GetCurrentUserName:
    Exit Function
 
Err_GetCurrentUserName:
        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function
 
 
db.QueryDefs("qptMyPassThru").Connect = strConnect1 & gUsrNm & strConnect2

Open in new window

0
 
jmoss111Commented:
Also if trusted connection the replace PWD param with Trusted_Connection=yes;

Jim
0
 
Sparky191Author Commented:
So I guess your saying you have to code this rather than configuring something. I'll give it a whirl.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
jmoss111Commented:
This is the easiest way; you grab your user name;  and you can user the db.querydefs statement for every query rahter than typing that info into each querydef's connect string. If you do a lot of these like I do then it saves times and cuts typing errors. If you're unfamiliar with doing things this way, you'll want to add these function calls in your main forms onLoad event:
GetCurrentUserName
SetSQLConnectString

Jim
Public Sub SetSQLConnectString()
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qptMyPassThru").Connect = strConnect1 & gUsrNm & strConnect2
End Sub

Open in new window

0
 
jmoss111Commented:
You can type the info into the connect string. Also remember that return records = yes where you are going to return records.
0
 
Sparky191Author Commented:
Great I'll give it a go in the morning.
0
 
Sparky191Author Commented:
I did it a different way, with the pass through query on design view, I turned on the property sheet and theres a property, "ODBC Connect Str" when you on the value for this it allows you to select the DSN and when you close it, you are prompted to save the password, which puts/saves the following in the as the value for that property.

"ODBC;Description=MyServerName PassThrou;DRIVER=SQL Server;SERVER=MyServerName;UID=MyUserName;DATABASE=MyDBName;LANGUAGE=us_english;Trusted_Connection=Yes
0
 
jmoss111Commented:
You did it like I said in my earlier posts : "You can type the info into the connect string. Also remember that return records = yes where you are going to return records", "This is the easiest way; you grab your user name;  and you can user the db.querydefs statement for every query rather than typing that info into each querydef's connect string. If you do a lot of these like I do then it saves times and cuts typing errors. ". Either way same results. Using the constants and setting the db.QueryDefs("qptMyPassThru").Connect = strConnect1 & gUsrNm & strConnect2 and getting the username from the function also produce the same results.

If you had 10 querydefs to modify and/or you had to do that for 100 users scattered around in different places, you'd spend a lot of  time typing in the info where all the code runs in a heartbeat.

Does it work OK for you?

Jim
0
 
Sparky191Author Commented:
My bad. I thought you were talking about the code not the access interface.
0
 
jmoss111Commented:
Like so many things in Access there are at least three or four ways of doing everything...

Have a great day,

Jim
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now