Need to securely link to SQLServer DB From MSAccess - and build Package Solution

[DaveFromNH]

I have an SQLServer database on a remote server which is used by our web site.

I have a set of reports which I built using MSAccess 2010 and I use ODBC to link to that SQLServer database.

I want to build a "Package Solution" to distribute the MSAccess database, but I don't want to give the database password to everyone who will install the solution.

I've assumed that I should have my MSAccess startup page have no recordsource, but in the "on open" event I should programmatically re-link the tables providing the password in code (which I'll secure). After successfully relinking the tables, I'll then open the 'real' main menu and close this 'initiator' form.
I'm having a problem relinking the tables. The tdf.RefreshLink always throws error 3151 ; "ODBC--connection to 'Cert123.dsn' failed."
Here is my code;
Dim dbs As Database
Dim tdf As TableDef
Dim Tdfs As TableDefs
Dim strConnect As String
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
For Each tdf In Tdfs
If tdf.SourceTableName <> "" Then
strConnect = "ODBC;" & _
"Server=123.123.123.123,25000;" & _
"Database=DB123_Cert;" & _ "Uid=User123;" & _
"Password=mypwd123;" & _
"DSN=Cert123.dsn;" & _
"ProviderName=System.Data.SqlClient"
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next
Any assist is appreciated (including if there is a better way to hide the database password in the published solution).
Dave

 

[Pelerin13]

did you try to add it thru ODBC administrator ?

 

[DaveFromNH]

I built it through ODBC administrator. My problem is that I'm unable to supply the password through code.

 

[Pelerin13]

try this
Dim Oracle_Db As Database
Set Oracle_Db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DATABASE=Your_DB;UID=Your_UID;PWD=Your_Password;DSN=Your_DNS")

 

[DaveFromNH]

try this
Dim Oracle_Db As Database
Set Oracle_Db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DATABASE=Your_DB;UID=Your_UID;PWD=Your_Password;DSN=Your_DNS")

Thanks - I tried, but I'm still getting the same error. When I go to the dsn in the ODBC administrator, the connection does test successfully, I'm just not able to override the PWD parameter... I appreciate your effort.

 

[DaveFromNH]

I've been able to get around this problem with a bit of a radical approach... I found a Microsoft KB article about "How to create a DSN-less connection to SQL Server for linked tables in Access" - the KB article is 892490 in support.microsoft.com/kb (Since I don't have much history with this forum, it won't let me post a link to the article - maybe a responder will....)

Using their sample function code as a guide, I've been able to hide the DB password in code and dynamically link to the DB at run time.

 

[Pelerin13]

Thanks - I tried, but I'm still getting the same error. When I go to the dsn in the ODBC administrator, the connection does test successfully, I'm just not able to override the PWD parameter... I appreciate your effort.

Go to Linked Table Manager and select one of your linked Tables and Click OK, then it will ask for the Password ...

 

[DaveFromNH]

Go to Linked Table Manager and select one of your linked Tables and Click OK, then it will ask for the Password ...

I just tried that, and it did not ask for the password. Must be saving it. I've got a work-around now. Thanks for your help.

 

[Pelerin13]

you are welcome
Please feel free to share your results/work around with the world