Solved

Saving the DSN connection with a pass through connection Access 2007

Posted on 2008-09-29
10
397 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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