CODEWITHSUNDEEP
vbams-access
y2j
y2j

Reputation: 207

Password of connection string in Pass Through Query properties

I am calling oracle stored procedures via Pass Through Query in MS Access. In pass through query properties we are saving connection string with password. I don't want to allow the user to see the password. Is there any way to encrypt the password in pass through query.

QueryProperties.png

Connection string contains ODBC;DSN=NEW_ODBC;UID=XXXXX;PWD=XXXXXXX;DBQ=XXXXXX;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;

I am using docmd to call the pass through query

DoCmd.OpenQuery "qry_into_table", acViewNormal, acEdit

If i remove the PWD from the above string, every time it ask for the password to enter. I heard about the concept to use DSN less connection but i am not sure how to create dsn less connection for odbc. I am using user dsn.

Is there any way to encrypt the password or user can not see the password??

Thanks

Upvotes: 2

Views: 3782

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48964

If you link all your tables and also that of pass-though queries and LEAVE OUT the uid/password, then your quires and linked tables etc. will run just fine. You need to execute JUST ONE logon command at application start.

So in your DSNless re-link code, include the pass-though queries.

How to eliminate the need to include the user id and password in linked tables it outlined here:

Power Tip: Improve the security of database connections

https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/

The above also works for pass-through queries.

The result is then you can execute the pass though query like this in code:

Currentdb.Execute "qry_into_table"

And you can even pass values to a stored procedure with this code:

With Currentdb.QueryDefs("MyPass")
   .SQL = "exec sp_MyUpdate " & strParm1
   .execute
End With

Note how clean, and how we don't have to deal with connection strings in code with the above.

So the above power tip really eliminates huge amounts of code, eliminates the need to introduce ADO for calling stored procedures, and removes the need of having to deal with messy connection strings in code.

Upvotes: 3

Related Questions