Darrell Porter
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
OM Gang
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
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
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
passthrough.jpg
ASKER
Except, I do not have the field, ODBC Connect Str
See attached image
See attached image
No image posted.
OM Gang
OM Gang
ASKER
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:
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!
Thank you so much!
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
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
Thank you so much!
ASKER
Great job! Again, thank you so much!
Excellent work. Glad you got it figured out.
OM Gang
OM Gang
ASKER
In every case, the query's connection string is blank. These queries are all select queries.
The specific code I use is:
Open in new window
Note that the code block
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.