Solved

Access 2007 - Changed ODBC connection information - queries not changed

Posted on 2014-03-21
10
963 Views
Last Modified: 2014-03-24
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
Comment
Question by:WalkaboutTigger
  • 6
  • 4
10 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39946498
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
 
LVL 15

Author Comment

by:WalkaboutTigger
ID: 39946569
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
 
LVL 28

Expert Comment

by:omgang
ID: 39950530
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
 
LVL 28

Expert Comment

by:omgang
ID: 39950552
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
 
LVL 28

Expert Comment

by:omgang
ID: 39950575
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 15

Author Comment

by:WalkaboutTigger
ID: 39951296
Except, I do not have the field, ODBC Connect Str

See attached image
0
 
LVL 28

Expert Comment

by:omgang
ID: 39951668
No image posted.

OM Gang
0
 
LVL 15

Author Comment

by:WalkaboutTigger
ID: 39951752
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
 
LVL 15

Author Closing Comment

by:WalkaboutTigger
ID: 39951758
Great job!  Again, thank you so much!
0
 
LVL 28

Expert Comment

by:omgang
ID: 39951776
Excellent work.  Glad you got it figured out.
OM Gang
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now