Procedures to Bypass DSN Login Screen

[Huskergirl]

I use Access 2007 and have been searching for the steps/procedures in vba code to bypass the login screen for a DSN connection. I've been able to find different parts but can't make sense of it when attempting to piece it together.

I have several linked tables to the DSN connection (local) and a couple of the tables change monthly. I don't want to store my UID and password when linking the first table because that wouldn't accomplish what I'm really after.

I need to do this via ADO code, I found DAO "is not supported".

Thanks in advance!

Sara :banghead:

 

[Huskergirl]

I have tried the following code and get a compile error:

Option Compare Database
Option Explicit
Public Function ODBCConnection()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim sCN As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

sCN = "Provider='MSDASQL.1';Password='XXXXXX';Persist Security Info=True;User ID='XXXXXXXX';Extended Properties="DRIVER={ORACLE ODBC DRIVER};SERVER='PDSS';UID='XXXXXXXX';DBQ='PDSS';DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;""


cn.Open sCN
End Function

 

[Huskergirl]

I created a new Data Source within Administrative Tools (in the Control Panel) and configured it how I needed, therefore, I'm able to bypass the login screen.

However, I'd still be interested to know the code.

Thanks.