Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: File already in use after using QueryTables.Add

    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)

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    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

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    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.

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    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.

    Code:
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = _
            "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National"
        .MaintainConnection = False
        .CreatePivotTable TableDestination:=Range("A3"), _
            TableName:= "PivotTable1"
    End With
    With ActiveSheet.PivotTables("PivotTable1")
        .SmallGrid = False
        .PivotCache.RefreshPeriod = 0
        With .CubeFields("[state]")
            .Orientation = xlColumnField
            .Position = 0
        End With
        With .CubeFields("[Measures].[Count Of au_id]")
            .Orientation = xlDataField
            .Position = 0
        End With
    End With

  5. #5
    Join Date
    Dec 2004
    Posts
    3
    Hi Fazza

    using .MaintainConnection = False did the trick

    Thank you very much for the help.

  6. #6
    Join Date
    Feb 2006
    Posts
    113
    You're very welcome, shill. cheers, Fazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •