asked on

VBA Code to Automatically Connect to an Oracle Server in my Pass Through Queries within MS Access

I have the following VBA Code where I'm trying to automatically connect to an Oracle Server in my pass through queries.

The Function "CreateMyOracleConn" is being called from the Sub Form_Load. After the form loads, a "Select Data Source" dialog box pops up and wants me to select the data source.

Function CreateMyOracleConn()
On Error GoTo Err_Execute

Dim strMySQLServerHostWINP As String
Dim strMySQLLoginWINP As String
Dim strMySQLPswdWINP As String

Dim strMySQLServerHostEDMP As String
Dim strMySQLLoginEDMP As String
Dim strMySQLPswdEDMP As String

Dim strMySQLServerHostPDB3 As String
Dim strMySQLLoginPDB3 As String
Dim strMySQLPswdPDB3 As String

Dim LConnect As String


' Connect to Oracle Server WINP
strMySQLServerHostWINP = "WINP"
strMySQLLoginWINP = "zread"
strMySQLPswdWINP = "READONLY"
strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP

OracleConnect = True


' Connect to Oracle Server EDMP
strMySQLServerHostEDMP = "EDMP"
strMySQLLoginEDMP = "readall"
strMySQLPswdEDMP = "readall"
strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP

OracleConnect = True


'' Connect to Oracle Server PDB3
strMySQLServerHostPDB3 = "PDB3"
strMySQLLoginPDB3 = "z999999"
strMySQLPswdPDB3 = "PDB3"
strMySQLConnPDB3 = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostPDB3 & "; User=" & strMySQLLoginPDB3 & ";Password=" & strMySQLPswdPDB3

OracleConnect = True

Exit Function

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

End Function

Open in new window

What am I doing wrong?

Thanks,
gdunn59

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdunn59

ASKER

Scott McDaniel,

So if I set the connect property with the Code that you provided, then I leave the Connection String blank in the pass through query, correct?

Thanks,
gdunn59

PatHartman

Yes. if that is what you want to do. I find it very inconvenient to not have embedded connection strings in my pass-through queries when I am testing so I always embed them. To modify the connection string when you switch from test to QA to prod, etc, just include the pass through queries in your relink loop and Access will fix up the connection strings for you.

gdunn59

ASKER

What if I have more than one pass through query using the same connection, and others using 2 different connections?

Thanks,
gdunn59

PatHartman

You would relink each back end separately. The code is pretty custom but you should be able to work it out. The tables were in two different databases and each database had three stages. So I'm pretty sure the form let you choose a database and then a stage - test, QA, prod and using a string that was common in all stages for a given database, found all the tables and constructed the appropriate connection string. The primary assumption is that the connection string for all objects of a single BE database would be the same. If some tables used different logins, then you would have to tablize the whole process so you could have unique connection strings for specific objects. I wasn't actually using "normal" pass-through queries for this app but I was creating pseudo indexes so that views would remain updateable. When you run relink code, you are not prompted to specify a unique index for views and so they would become non-updateable because Access would "forget" the choice I made when the view was linked originally.

I don't have the code handy. I'll try to remember to search for it tonight if no one posts it sooner.

gdunn59

ASKER

Here's the code I have, and it worked:

Function CreateMyOracleConn()
'On Error GoTo Err_Execute

Dim qdfWINP As DAO.QueryDef
Dim qdfEDMP As DAO.QueryDef
Dim qdfPDB3 As DAO.QueryDef

' ODBC Connections for ANORM
strMySQLServerHostWINP = "WINP"
strMySQLLoginWINP = "zread"
strMySQLPswdWINP = "READONLY"

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_ANORM_BASE_DATA")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_ANORM_SUNDRY")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_SUNDRY_DATA")
qdfWINP.Connect = strMySQLConnWINP
Set qdfWINP = Nothing


' ODBC Connections for OPEN Wells (EDMP)
strMySQLServerHostEDMP = "EDMP"
strMySQLLoginEDMP = "readall"
strMySQLPswdEDMP = "readall"

strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP
Set qdfEDMP = CurrentDb.QueryDefs("ptq_DYNAMIC_OPEN_WELLS")
qdfEDMP.Connect = strMySQLConnEDMP

strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP
Set qdfEDMP = CurrentDb.QueryDefs("ptq_OPEN_WELLS_BASEDATA")
qdfEDMP.Connect = strMySQLConnEDMP

strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP
Set qdfEDMP = CurrentDb.QueryDefs("ptq_OW_RIG_OPS_FINISH")
qdfEDMP.Connect = strMySQLConnEDMP

strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP
Set qdfEDMP = CurrentDb.QueryDefs("ptq_OW_SURVEY_HEADER")
qdfEDMP.Connect = strMySQLConnEDMP

strMySQLConnEDMP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostEDMP & "; User=" & strMySQLLoginEDMP & ";Password=" & strMySQLPswdEDMP
Set qdfEDMP = CurrentDb.QueryDefs("ptq_WELL_STATUS")
qdfEDMP.Connect = strMySQLConnEDMP
Set qdfEDMP = Nothing


' ODBC Connection for PDB3
strMySQLServerHostPDB3 = "PDB3"
strMySQLLoginPDB3 = "z999999"
strMySQLPswdEDMP = "PDB3"

strMySQLConnPDB3 = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostPDB3 & "; User=" & strMySQLLoginPDB3 & ";Password=" & strMySQLPswdPDB3
Set qdfPDB3 = CurrentDb.QueryDefs("ptq_DYNAMIC_PDB")
qdfPDB3.Connect = strMySQLConnPDB3

End Function

Open in new window

Thanks,
gdunn59

PatHartman

This is what I used:

Private Sub lstConnectionStrings_DblClick(Cancel As Integer)
    Dim db As DAO.Database
    Dim tdef As DAO.TableDef
    Dim qdef As DAO.QueryDef
    Dim constr As Variant
    Dim DatabaseName As Variant
    Dim Relinked As Boolean
    Dim TableCount As Integer
    Dim QueryCount As Integer
On Error GoTo Err_Proc

    Set db = CurrentDb
    
    Relinked = False
    TableCount = 0
    QueryCount = 0
    constr = Me.lstConnectionStrings.Column(3)
    DatabaseName = Me.lstConnectionStrings.Column(1)
    
    DoCmd.RunMacro "mSetWarningsOff"
    For Each tdef In db.TableDefs
        If InStr(tdef.Connect, DatabaseName) Then
            tdef.Connect = constr
            tdef.RefreshLink
            Relinked = True
            TableCount = TableCount + 1
        End If
    Next
    

' relink pass-through queries
    For Each qdef In db.QueryDefs
        If InStr(qdef.Connect, DatabaseName) Then
            qdef.Connect = constr
            QueryCount = QueryCount + 1
        End If
    Next

    If Relinked = True Then
        DoCmd.RunMacro "mAddPrimaryKeysTo" & DatabaseName
        MsgBox "Re link completed --- " & TableCount & " tables relinked and " & QueryCount & " queries relinked"
    Else
        MsgBox "No tables were relinked"
    End If
Exit_Proc:
    DoCmd.RunMacro "mSetWarningsOn"
    Exit Sub
Err_Proc:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_Proc
End Sub

Open in new window

gdunn59

ASKER

Ok. I guess my code I posted is not working 100%.

It still stops and prompts me to enter the Service Name, User ID and Password.

What am I doing wrong?

By the way, this is and ODBC Connection to ORACLE. I'm not linking tables, I need this connection for my Pass Thru queries.

Here is the code again:

Function CreateMyOracleConn()
Dim qdfWINP As DAO.QueryDef

' ODBC Connections for ANORM
strMySQLServerHostWINP = "WINP"
strMySQLLoginWINP = "zread"
strMySQLPswdWINP = "READONLY"

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_IDLE_WELLS")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_IDLE_WELLS_ALL_CMPL")
qdfWINP.Connect = strMySQLConnWINP

strMySQLConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMySQLServerHostWINP & "; User=" & strMySQLLoginWINP & ";Password=" & strMySQLPswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_PDBANORM_HYDRO")
qdfWINP.Connect = strMySQLConnWINP

Set qdfWINP = Nothing

End Function

Open in new window

Thanks,
gdunn59

PatHartman

Did you try the code I posted? It shouldn't matter if the BE is Oracle. You just have to get the correct connection string.

Scott McDaniel (EE MVE )

Are you 100% sure the connection string is correct? I use this resource to get the syntax right:

https://www.connectionstrings.com/oracle/

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdunn59

ASKER

PatHartman,

After I linked a table and then opened the MySysObjects Table, I had "User" and it should have been "UID", and I had "Password" and it should have been "PWD".

It still keeps stopping on the following code, prompting for the ODBC Connection Login:

Function RefreshStatusTables() As Long
  On Error GoTo ErrHandler
  Dim strSQL As String
  
  DoCmd.SetWarnings False
  DoCmd.RunSQL "DELETE * FROM LU_ACTIVITY_STATUS"
  DoCmd.RunSQL "DELETE * FROM LU_WELL_STATUS"
  
  CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM").SQL = "SELECT SRT_WELL_ACTIVITY_CODE, SRT_WELL_ACTIVITY_DESC FROM WINS.SRT_WELL_ACTIVITY"
  DoCmd.RunSQL "INSERT INTO LU_ACTIVITY_STATUS SELECT ptq_DYNAMIC_ANORM.* FROM ptq_DYNAMIC_ANORM;"
  
  CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM").SQL = "SELECT SRT_WELL_STATUS_CODE, SRT_WELL_STATUS_DESC FROM WINS.SRT_WELL_STATUS"
  DoCmd.RunSQL "INSERT INTO LU_WELL_STATUS SELECT ptq_DYNAMIC_ANORM.* FROM ptq_DYNAMIC_ANORM;"  
  
ErrHandler:
  DoCmd.SetWarnings True
  RefreshStatusTables = ErrorHandler(err, "RefreshStatusTables")
End Function

Open in new window

Here is my code for the ODBC Oracle Connection String:

Function CreateMyOracleConn()
Dim qdfWINP As DAO.QueryDef
Dim strMyOracleConnWIP As String
Dim strMyOracleServerHostWINP As String
Dim strMyOracleLoginWINP As String
Dim strMyOraclePswdWINP As String

' ODBC Connections for ANORM
strMyOracleServerHostWINP = "WINP"
strMyOracleLoginWINP = "zread"
strMyOraclePswdWINP = "READONLY"


strMyOracleConnWIP = "ODBC;Driver={Oracle in orahome64};Server=" & strMyOracleServerHostWINP & "; UID=" & strMyOracleLoginWINP & ";PWD=" & strMyOraclePswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM")
qdfWINP.Connect = strMyOracleConnWIP
'Set qdfWINP = Nothing


strMyOracleConnWIP = "ODBC;Driver={Oracle in orahome64};Server=" & strMyOracleServerHostWINP & "; UID=" & strMyOracleLoginWINP & ";PWD=" & strMyOraclePswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_IDLE_WELLS")
qdfWINP.Connect = strMyOracleConnWIP
'Set qdfWINP = Nothing


strMyOracleConnWIP = "ODBC;Driver={Oracle in orahome64};Server=" & strMyOracleServerHostWINP & "; UID=" & strMyOracleLoginWINP & ";PWD=" & strMyOraclePswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_IDLE_WELLS_ALL_CMPL")
qdfWINP.Connect = strMyOracleConnWIP
'Set qdfWINP = Nothing

strMyOracleConnWIP = "ODBC;Driver={Oracle in orahome64};Server=" & strMyOracleServerHostWINP & "; UID=" & strMyOracleLoginWINP & ";PWD=" & strMyOraclePswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_PDBANORM_HYDRO")
qdfWINP.Connect = strMyOracleConnWIP
'Set qdfWINP = Nothing

End Function

Open in new window

I just can't figure out what the issue is. When the ODBC Connection fails, I'm getting the Error #3151.

Also, when I looked at the MySysObjects Table Object, here is the full ODBC Connect String:

DRIVER={Oracle in orahome64};SERVER=WINP;UID=zread;PWD=READONLY;DBQ=WINP;DBA=W;APA=T;EXC=F;XSM=Default;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=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;

Open in new window

gdunn59

ASKER

I figured out what the issue was.

The database was converted from a Windows 7 environment to a Windows 10 environment, and from Access 2013 to Access 2016. In the Windows 7 and Access 2013 environment, the code worked find, but in Windows 10 and Access 2016 it didn't.

The issue in the Windows 10 and Access 2016 environment was that I needed to add the DBQ (Database Qualifer) to the connect string in the code:

' ODBC Connections for ANORM
strMyOracleServerHostWINP = "WINP"
strMyOracleDBQWINP = "WINP"
strMyOracleLoginWINP = "zread"
strMyOraclePswdWINP = "READONLY"

strMyOracleConnWINP = "ODBC;Driver={Oracle in orahome64};Server=" & strMyOracleServerHostWINP & ";DBQ= " & strMyOracleDBQWINP & ";UID=" & strMyOracleLoginWINP & ";PWD=" & strMyOraclePswdWINP
Set qdfWINP = CurrentDb.QueryDefs("ptq_DYNAMIC_ANORM")
qdfWINP.Connect = strMyOracleConnWINP

Open in new window

PatHartman

Glad you worked it out. I always link a new table to get the connection string in a new environment. It is easier than trying to find a current sample on the web.

gdunn59

ASKER

Thanks Pat!