We help IT Professionals succeed at work.

Access VBA - ODBC Connection to Oracle database

Murray Brown
Murray Brown asked
on
Hi

I found the following Access VBA code to create a connection to an Oracle database.
Does this still apply in Access 2007 or is there a better way to do it?

Also, could anyone steer me to an article on the latest way to make such a connection?

Thanks
Function OracleConnect() As Boolean

    Dim ws As Workspace
     Dim db As Database
     Dim LConnect As String

    On Error GoTo Err_Execute

    'Use {Microsoft ODBC for Oracle} ODBC connection
     LConnect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

    'Point to the current workspace
     Set ws = DBEngine.Workspaces(0)

    'Connect to Oracle
     Set db = ws.OpenDatabase("", False, True, LConnect)

    db.Close

    OracleConnect = True

    Exit Function

Err_Execute:
     MsgBox "Connecting to Oracle failed."
     OracleConnect = False

End Function

Open in new window

Comment
Watch Question

Database Analysist Senior
Commented:
you can also user Oracle Gateway for connecting to access DB.

check this doc for configuration details..
http://docs.oracle.com/cd/B28359_01/gateways.111/b31043/configodbc.htm
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much