Solved

Saving the DSN connection with a pass through connection Access 2007

Posted on 2008-09-29
10
403 Views
Last Modified: 2012-05-05
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
Comment
Question by:Sparky191
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22597079
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22597391
Also if trusted connection the replace PWD param with Trusted_Connection=yes;

Jim
0
 
LVL 1

Author Comment

by:Sparky191
ID: 22597784
So I guess your saying you have to code this rather than configuring something. I'll give it a whirl.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Expert Comment

by:jmoss111
ID: 22598269
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22599095
You can type the info into the connect string. Also remember that return records = yes where you are going to return records.
0
 
LVL 1

Author Comment

by:Sparky191
ID: 22600153
Great I'll give it a go in the morning.
0
 
LVL 1

Author Comment

by:Sparky191
ID: 22616485
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22616828
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
 
LVL 1

Author Comment

by:Sparky191
ID: 22629421
My bad. I thought you were talking about the code not the access interface.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22629611
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 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