?
Solved

Access 2007 - Changed ODBC connection information - queries not changed

Posted on 2014-03-21
10
Medium Priority
?
1,069 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 2000 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

594 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