Hello everyone
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.

Thanks
shill


Function RefreshDataSource(strSQLWhere As String) As Boolean
Dim iLastRowNum As Integer
Dim strQryName As String
Dim strDBPathName As String


strDBPathName = PURCHASER_DISCREP_DB_PATH_PARENT + "\" + PURCHASER_DISCREP_DB_NAME
strQryName = PURCHASER_DISCREP_QUERY_NAME_ALL

On Error GoTo ERROR_HANDLER


With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=G:\Info\DATADMIN\PBR\Discrepancy Reports\" _
, _
"AutoDataDiscreps.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLE" _
, _
"DB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" _
, _
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=" _
, _
"False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))

.CommandType = xlCmdSql


.CommandText = Array("Select * from " + strQryName + " " + strSQLWhere)
.Name = "PURCHASER_DISCREP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = strDBPathName
.Refresh BackgroundQuery:=False

End With

RefreshDataSource = True

Exit Function


ERROR_HANDLER:
RefreshDataSource = False
MsgBox Error, vbCritical, "Error refreshing PURCHASER_DISCREP Data Source"
End Function

Function GetOldPCTCode(strCode As String, strRetCode As String) As Boolean
Dim db As Database
Dim rs As Recordset
Dim ws As Workspace

GetOldPCTCode = False

'On Error GoTo GetOldPCTCode_ERROR
Set ws = DBEngine.Workspaces(0)

Set db = ws.OpenDatabase(GetDBPathName())
Set rs = db.OpenRecordset("Select * from qryOldPCTMapping where newCode =""" + strCode + """", dbOpenSnapshot)

welcome, shill

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.

HTH
Fazza

Hi Fazza
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.

Hi shill

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.

Remarks

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.

Example

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.

Hi Fazza

using .MaintainConnection = False did the trick

Thank you very much for the help.

You're very welcome, shill. cheers, Fazza