[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1056
  • Last Modified:

Access 2007 - Changed ODBC connection information - queries not changed

We had a group in the organization who was using a heavily-modified TNSNames.ORA and a significant number of non-conforming ODBC DSNs.  We are continuing to support this in the short-term until we can resolve this issue.

The group uses a number of Access 2007 databases which have queries pointed to legacy TNSNames.ORA entries.  I have updated the table ODBC links, but this does not apparently make changes to the links the queries are using.  There are hundreds, if not thousands, of queries per Access database file.

How can I programmatically, or via the Access 2007 GUI, change these query definitions without having staff delete and recreate the queries?
0
WalkaboutTigger
Asked:
WalkaboutTigger
  • 6
  • 4
1 Solution
 
omgangCommented:
I do this to change the connection strings for linked tables.  I modified if for queries.  Test first of course.
OM Gang



Dim qdf As DAO.QueryDef
Dim strConnect As String

strConnect = "ODBC; etc. etc. etc."

For Each qdf in CurrentDb.QueryDefs
        'see if query has a connection stirng
    If Len(qdf.Connect) > 0 Then
            'display the connection string in the immediate window if desirable
        Debug.Print qdf.Connect
            'assign a new connection string
        qdf.Connect = strConnect
        qdf.RefreshLink
    End If
Next qdf

    'destroy object variable
Set qdf = Nothing
0
 
WalkaboutTiggerAuthor Commented:
I have this code as well.
In every case, the query's connection string is blank.  These queries are all select queries.
The specific code I use is:

Public Sub QryList()
 Dim qr As QueryDef
 For Each qr In CurrentDb.QueryDefs
     If qr.Name = "Human Readable Name of Query" Then
        t = MsgBox(qr.Name & vbCrLf & vbCrLf & qr.Connect)
     End If

     If Len(qr.Connect) > 0 Then
        t = MsgBox(qr.Connect)
     End If

 Next qr

Open in new window


Note that the code block
     If Len(qr.Connect) > 0 Then
        t = MsgBox(qr.Connect)
     End If

Open in new window

never results in a message block popup.  I receive one instance of the message box popup from the query name if/then construct with a blank connect string.
0
 
omgangCommented:
The queries are pass-through queries to a back-end Oracle db?  Or are the Oracle tables linked to the Access db and the queries are simple SELECT queries on local/linked tables?

OM Gang
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
omgangCommented:
I pulled this from an old app that used pass-through queries to an Oracle 10g db.  I commented out the line to assign a connection string and added a line to print the connect string to the Immediate window.  It works as expected.
OM Gang

Public Function BuildConnect()
'08/12/2010 - assigns connection strings for pass-through queries
'to Oracle database.  connection string for appropriate target
' db should be assigned to global constant in this module
On Error GoTo Err_BuildConnect

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb
   
        'enumerate each querydef in current db and set connection string for pass-through queries
    For Each qdf In db.QueryDefs
        If qdf.Type = clngPassThroughQuery Then
            'qdf.Connect = cstrConnect1
            Debug.Print qdf.Connect
        End If
    Next

Exit_BuildConnect:
        'destroy object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
   
Err_BuildConnect:
    MsgBox Err.Number & ", " & Err.Description, , "Error in function BuildConnect of module basOracleConnects"
    Resume Exit_BuildConnect

End Function
0
 
omgangCommented:
Take a look at one of the queries in design view.  If it's a pass-through it should display in SQL mode and the properties dialogue should have an entry for the ODBC Connect Str property
passthrough.jpg
0
 
WalkaboutTiggerAuthor Commented:
Except, I do not have the field, ODBC Connect Str

See attached image
0
 
omgangCommented:
No image posted.

OM Gang
0
 
WalkaboutTiggerAuthor Commented:
Okay, omgang, I determined the issue.

While I used the Linked Table Manager to update which ODBC entries needed to be used, while it updated the name of the ODBC entry, it did not update the DBQ entry so it was using the old DBQ name (which is extremely annoying).

I used the following code to fix it:

Public Sub QryList()
 Dim qr As TableDef
 For Each qr In CurrentDb.TableDefs
     If qr.Connect = "ODBC;DSN=NewDSNName;DBQ=OldDBQName;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;" Then
        qr.Connect = "ODBC;DSN=NewDSNName;DBQ=NewDBQName;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;"
        qr.RefreshLink
     End If
 Next qr

Open in new window


As you can see, I had to go back to the table definitions.  This is what was causing the problem and the reason the queries had blank connect strings.

I think it was the line you had, that I didn't, that saved the day.  If I could give you 5,000 points for this question, I would do so!

qr.RefreshLink

Open in new window


Thank you so much!
0
 
WalkaboutTiggerAuthor Commented:
Great job!  Again, thank you so much!
0
 
omgangCommented:
Excellent work.  Glad you got it figured out.
OM Gang
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now