Setting Up ODBC to allow a user to connect to a CEHD database (student or employee)

Client computer

  • The people needing access: R. Sharma, W. Hollis, J. Polamo, M. Robideau, A. Strader
  • Collect setup information (username, password, database, etc) from Arlen
  • Connection requires a static IP for the connection.  Be sure DHCP reservation set for these users. IP address. When one of these receive a new computer, the DHCP reservation should be updated with the new MAC address.
  •  Install ODBC driver. It's psqlodbc8.2.msi in \\cehdserver\shareddata\databases\documentation\. Or, download the newest from the web.
  • Set up ODBC connection
    1. run odbcad32.exe
    2. add a "user dsn" using "postgresql unicode odbc driver" 
      Screenshot of ODBC configuration screen
    3. Set Data Source to be "cehddb" or "facultydb" File:Odbcad32.png
    4. Set information collected above (database, server, username, password)
    5. test connection
    6. Click "DataSource" button under options group
    7. In "Advanced Options" page 1/2, check "Use Declare/Fetch" box.
    8. Click OK to return
    9. Click "Save" to save
    10. Click "OK" to close odbcad32
  • Add a shortcut to the database access file on the user's desktop. (If it doesn't already exist.)
  • The file name is FacultyDB Office2010.accdb as of April 2012.
  • Open the access file and test.
  • With Office 2007, we need additional steps:
    • Open trust center following the security warning. 
    • Check "Allow network location"
    • Add this as trusted location. \\cehdserver.cehd.tamu.edu\shareddata\databases\
    • Check the "trust subfolders" box

64-bit OSs

  • We still use the 32 bit version of Office/Access.  So, the 32 bit Postgres driver should still be installed.  However, you must create the ODBC connections through Access since the Control Panel --> ODBC screen only seems to support 64-bit connections.
  • To clear out previous connections, regedit --> search for ODBC or ODBC.ini.  Under there, you can delete keys related the connection to remove.
  • In Access 2010, External Data --> ODBC database
    • link tables
    • Select Data Source --> New (to create the new connections)
    • (Warning about admin access required for system connections, but we just need a user connection, so don't need admin rights)
    • Choose PostgresSQL unicode driver


See Knowledge Base article Setting up Users for MS Access interface fo Data Portal for additional information.