PDA

View Full Version : Replace Query Database



jo15765
08-10-2014, 04:51 PM
How in Excel 2007/Excel 2010 can you use VBA to replace the database of all queries on the worksheet?

westconn1
08-11-2014, 02:33 AM
not enough information

jo15765
08-11-2014, 12:13 PM
not enough information
Let's say there is an imported Access Query into Excel and the query text is (SQL behind the query)

Select * from qry_Uno
FROM c:\AlphaDawg\12345.qry_Uno qry_Uno


How can I change the 12345 (database name) to 84321

jonh
08-12-2014, 03:50 AM
Sub test()

Const oldpath As String = "C:\old"
Const newpath As String = "C:\new"
Const db As String = "\mydb.mdb"

Dim c As WorkbookConnection
For Each c In ActiveWorkbook.Connections
Select Case c.Type
Case 1
c.OLEDBConnection.Connection = _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & newpath & db
c.OLEDBConnection.SourceDataFile = newpath & db
Case 2
c.ODBCConnection.Connection = _
Replace(c.ODBCConnection.Connection, oldpath, newpath)
c.ODBCConnection.CommandText = _
Replace(c.ODBCConnection.CommandText, oldpath, newpath)
End Select

c.Refresh
Next
End Sub

jo15765
08-13-2014, 06:28 PM
What are the 2 different cases account for?

jonh
08-13-2014, 11:42 PM
If I remember correctly, the first case is for tables/queries linked directly, the second is for queries written using the query builder thing.
Judging by the sql you posted above you would be using the second case.