VBA PassThrough To Link SQL Tables

[DataChick]

I have an MS Access 2003 database front end that I am linking to a SQL Server 2008 backend. I need to assure that the users can log in through the ODBC DSN but NOT be able to access the password.

I have 40 tables that are linked from SQL so what I was using was a PassThrough query that doesn't have any SQL code in it, all it does it establish the connection by sending the password through (so all of the tables can then be accessed).

The problem is that it holds the password and if a user imports that query into another database, they are able to see the password. I want to be able to hold that password in an encrypted table and pass it through that way.

All of the code I've found to create the connection using VBA runs specific code to the table that has been coded in but doesn't open all of the tables (after running the code and establishing the connection, it still asks for the password when I try to open a table).

Does anyone know how to accomplish the same thing my passthrough query does in VBA code?

Under Query Properties for the PassThrough Query I run at open:

ODBC Connect Str: ODBC;DSN=RIB_SQL;Description=RIB SQL Tables;UID=RIB_APPL;PWD=pw;Network=DBMSSOCN;Address=FCDB639\DEV,61435

{All other items left as default.}

The only thing in the SQL window is "("

Any help would be greatly appreciated.

Thank you in advance!

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[DataChick]

I managed to figure out something that works for me so I can create the same pass through within the VBA code and not have to have a query that can be imported to another database with the password within the connection string:

Dim wksp As DAO.Workspace
Dim dabs As DAO.Database
Dim strSQL As String
Dim strCON As String

strSQL = " "
strCON = "ODBC;DSN=RIB_SQL;Description=RIB SQL Tables;UID=RIB_APPL;PWD=pw;Network=DBMSSOCN;Address=FCDB639\DEV,61435"
Set wksp = DBEngine(0)
Set dabs = wksp.OpenDatabase("", False, False, strCON)
dabs.Execute strSQL, dbSQLPassThrough

Thank you to anyone who was attempting to find a solution.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein