How to Update Database Connection Properties

Update Excel File

Open Excel file
Click Data/Queries & Connections menu
Click Connections tab on Queries & Connections Panel
Right Click on Query choose "Properties..." from pop-up menu
Navigate to "Definition" tab
Locate connection string field
Edit connection string and update referenced parts as needed:
1. Update DSN=<datasource name>
2. Update DBQ=<database connect name>
  - This can be TNSName or EZ Connect string
3. Update UID (if updating username)
4. Update PWD (If password stored in connect string)
Repeat for other Queries as needed.
Save Excel document

Update Access DB

Open Access DB
Click to ExternalData->Linked Table Manager menu
Click Checkbox next to top level ODBC link
Click "Edit" button on right side of dialog to edit the link info
Edit connection string and update referenced parts as needed:
1. Update DSN=<datasource name>
2. Update DBQ=<database connect name>
  - This can be TNSName or EZ Connect string
3. Update UID (if updating username)
4. Update PWD (If password stored in connect string)
Save changes
Repeat for other connections as needed.
Close Linked Table Manager dialog
Verify connections by opening respective tables

Reference

TNS Names

Name as found in tnsnames.ora file, reference \\research.colostate.edu\NETLOGON\Software\Oracle\tnsnames.ora

Example entry:

VPRTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvprtest.is.colostate.edu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vprtest.infosys.colostate.edu)
      (UR=A)
      (FAILOVER_MODE =
        (TYPE = select)
        (METHOD = basic)
      )
    )
  )

EZ Connect String

\\hostname:port\servicename

Example: \\dbvprtest.is.colostate.edu:1521\vprtest.infosys.colostate.edu

ODBC Connect String

DSN=ODSPROD; UID=OSRUSER; PWD=SuperSecretPassword; DBQ=ODSPROD; 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;