Unanswered: File already in use after using QueryTables.Add
This my first post.
I'm having a struggle trying to access a database after using QueryTables.Add method. If I try as use VBA code using DAO I get a error stating error 3045 file already in use - quoting the database i'm using in the QueryTables.add method . Have I got the parameters of the .Add method wrong ? Can I access an already opened database object , if so where ?
Any hints would be gratefully received.
Function RefreshDataSource(strSQLWhere As String) As Boolean
Dim iLastRowNum As Integer
Dim strQryName As String
Dim strDBPathName As String
The query table is added in a function. This confuses me. A function can't do that. It just returns a value - in this case boolean RefreshDataSource. I think changing to a sub procedure is necessary if you want to add a query table. Then debug single step through the sub and see if you get problems.
BTW, FWIW, I often find it cleaner when adding a new query table to do so to a new worksheet.
Thanks for looking at the code.
The function RefreshDataSource is called from a previous function which does create a new worksheet. It does work , I use a function to return true of false so i can check that there are no problems opening the data source.
The problem I'm having is with the second function GetOldPCTCode , the line Set db = ws.OpenDatabase (GetDBPathName()) , it complains that the file is already in use if I have used previously the ActiveSheet.QueryTables.Add method refering to the same database.
I really don't understand, not that I let that stop me. I wondered if somehow the connection to the datasource - created with the QueryTables.Add - needs to be formally closed so that a new one can be used instead? The object browser has a property of a query table "MaintainConnection" that looks relevant - set to FALSE. Info below is copied from help. HTH, Fazza
True if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True. Read/write Boolean.
You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.
If you anticipate frequent queries to a server, setting this property to True might improve performance by reducing reconnection time. Setting the property to False causes an open connection to be closed.
This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet. The example terminates the connection after the initial refresh.
.Connection = _
.MaintainConnection = False
.CreatePivotTable TableDestination:=Range("A3"), _
.SmallGrid = False
.PivotCache.RefreshPeriod = 0
.Orientation = xlColumnField
.Position = 0
With .CubeFields("[Measures].[Count Of au_id]")
.Orientation = xlDataField
.Position = 0