ODBC Password

[Kirk P.]

Is there a way to take an ODBC connection string such as:

ODBC;DSN=CDWPRD02;UID=MyUser;PWD=MyPassword;DBQ=CDWPRD02;

and supply the password portion of the string from a table?

 

[BillyRogers]

pull the password from the table into a variable names myPassword and alter
the connection string like this......


"DSN=CDWPRD02;UID=MyUser;PWD=" & myPassword & ";DBQ=CDWPRD02;"





--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Kirk P.]

Can you expand on that? Not exactly sure how that works. Currently, my
connection string only exists within a SQL pass-through query - it does not
exist in VBA code, nor do I know how to create it in VBA code.

Will your suggestion work directly with the connection string contained
within my pass-through query?

 

[Billy Rogers]

Sorry, I didn't realize you were using a passthrough query...I thought you
were using an ADO connention in VBA code.

I don't know how you would do that with a passthrough query.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Douglas J. Steele]

You do it exactly the same way you suggested, changing the Connect property
of the query:

Dim qdfPassThrough As DAO.QueryDef

Set qdfPassThrough = CurrentDb.QueryDefs("NameOfQuery")
qdfPassThrough.Connect = "ODBC;DSN=CDWPRD02;UID=MyUser;" & _
"PWD=" & myPassword & ";DBQ=CDWPRD02;"

 

[Billy Rogers]

He's not using VBA......I was just saying I don't know of anyway to do that
just using the properties window of a pass-through query.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Douglas J. Steele]

You have no choice but to use VBA to reset the Connect property, as I showed
in my code.

 

[Billy Rogers]

Yeah that's what i thought.

I just don't like to say something can't be done.......I've had too many
people say that to me and then i figured out a way to do it.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Kirk P.]

OK, I've created the OBDC string in VBA as suggested - this part works fine.
How do I go about "pulling the password from the table into a variable
name"?.

 

[Billy Rogers]

using DAO

Dim MyPassword as string
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TableName")
rst.MoveFirst
MyPassword = rst.Fields(0)
rst.Close

This assusmes there's only one column and one row in the password table. I
don't know how you have your password table structured so i can't give you
SQL for that if you have multiple passwords, but just substitute the SQL
command for TableName in this code.

Also be sure you have reference set to DAO on the Visual Basic Editor.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Kirk P.]

Works great - thanks for the help!

using DAO

Dim MyPassword as string
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TableName")
rst.MoveFirst
MyPassword = rst.Fields(0)
rst.Close

This assusmes there's only one column and one row in the password table. I
don't know how you have your password table structured so i can't give you
SQL for that if you have multiple passwords, but just substitute the SQL
command for TableName in this code.

Also be sure you have reference set to DAO on the Visual Basic Editor.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/