Announcement

Collapse
No announcement yet.

UserForm: File/Path Access Error & I/O Error

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UserForm: File/Path Access Error & I/O Error



    Dear All,

    Please can you help?

    I've rambled on for serveral threads recently about problems I am experiencing with userforms. Finally I have managed to replicate my problem in the smallest file I can.

    First off, the spreadsheet needs to be copied to a directory called "C:\downloads" as it contains a ODBC query to itself (In reality, this is a query to an External Oracle Database)

    On loading, it should pop up a simple userform, with a combo and two command buttons, which when pressed takes you to a (hidden) tab that displays a pivottable.

    All works well until I try to close /save when 60% of the time, Excel encounters problems and closes and will not load up the file the next time until either quit excel or disable macros. Messages include "file/path access error", "I/O Error" or get restarts excel.

    On a casual run through, I expect you might report back that "All worked ok for me". Please can you give it a bit of a thrashing, comment out the userform show, save the file (frequently) becuase i assure you it will break ultimately!

    This is a brand-new file and I've tried it on about 5 different PC running different versions of Excel and generally get the same result.

    I must be doing something wrong.

    I would be extremely grateful for any help / guidance you can provide.

    Sincere regards,


    Pete
    Attached Files

  • #2
    Re: Userform Corruption

    My guess would a memory leak in MS Query. I could not see anything with your sample so I extended the database to 30k lines. I could then see the Excel memory usage slowly increasing with each query. I coded it to repeat and after a while I managed to use most of the memory and and received the i/o error.

    Then I rewrote the query using ADO and put the 30k lines into an Access db. I asked it to query 1000 times. It is still running but the memory looks ok.

    rough code based on Access.

    Code:
    Sub LoadData(TheType As String)
         ' This sub requires a reference to Microsoft Active X Data Object (ADO) 2.x
         ' In the VBE Tools > references and check the required object
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim ws As Worksheet
        Dim sSQL As String
        Dim DataSource As String
        Dim FieldCounter As Integer
         
        DataSource = "c:\Downloads\Test.mdb" 
        Set ws = ThisWorkbook.Worksheets("QueryTable")
        If TheType = "period" Then
            sSQL = sSQL + " SELECT Contract, Period, TheValue FROM TestTable"
    
        Else
            sSQL = " SELECT Contract,[Employee Name], TheValue FROM TestTable "
        End If
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & DataSource & ";"
        Set rs = New ADODB.Recordset
        rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
        ws.Range("A1:Z65534").ClearContents
        For FieldCounter = 0 To rs.Fields.Count - 1
            ws.Cells(1, FieldCounter + 1).Value = rs(FieldCounter).Name
        Next FieldCounter
        ws.Range("A2:AZ65534").CopyFromRecordset rs
         
         ' tidy up
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        Set ws = Nothing
    End Sub

    HTH
    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Userform Corruption

      "file/path access error" on UserForms loading is often due to how a Control is being populated on loading.

      Try these MSKB Search Results
      I/O Error
      File/Path Access Error

      Comment


      • #4
        Re: UserForm: File/Path Access Error & I/O Error

        Carl,

        Thank you for your help - much appreciated.

        If you are saying that its something to do with either MSQuery or Excel then that makes me feel a whole lot better because I had assumed it was something I was doing wrong, but couldn't see what - and that was the bit that bothered me!

        However, if it were down to MSquery, wouldn't the clever programmers at microsoft have done something to sort the problem - its inconvenient for me and my "janet & john" spreadsheet, but potentially significant for some developers?

        For fun and as a learning excercise I think I will re-write the query side of the spreadsheet using ADO using your code as a starting point.

        Thanks again,

        Pete

        Comment


        • #5
          Re: UserForm: File/Path Access Error & I/O Error

          Thanks Dave,

          I look into those.

          Pete

          Comment


          • #6
            Re: UserForm: File/Path Access Error & I/O Error

            Hi Pete,

            Hmmm. Thinking about it more, it is possible that the memory leak is due the fact that we are querying an open workbook ? If that were the case then it is strange that I only managed to recreate your i/o error when I ran out of memory ? I'm not so sure now.

            In the long run it is better to use ADO so I would go for that. Check out Dave's links first. I have had a few UserForm issues before that have been fixed by downloading the latest service packs .... so that is a good idea too. It is also a good to avoid using linked cells in controls (load controls/write cells programmatically using the events).

            From what I know MS Query is redundant. There are quite a few problems with it but I doubt they will fix them now.

            Carl
            locii Insight
            Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

            Comment


            • #7
              Re: UserForm: File/Path Access Error & I/O Error

              Just in case someone stumbles upon this thread in times to come, an update of my problem.

              Thanks to the help provided by Carl, I have re-coded my spreadsheet to use ADO instead of ODBC queries and am pleased to say that it has appeared to cure the problem.

              I guess the morale of the story, particularly if msquery is dead, is to use ADO - I certainly will in the future.

              Presumably though, Excel 2007 or Office 2007 or whatever, still ships with msquery so microsoft are still intending for it to be used?

              Anyhow, thanks again

              Pete

              Comment


              • #8
                Re: UserForm: File/Path Access Error & I/O Error

                Thanks for sharing Pete. Thanks to Carl for being so helpful.

                Comment


                • #9


                  Re: UserForm: File/Path Access Error & I/O Error

                  Nigel,

                  Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking.
                  ALWAYS start a new thread for your own questions and, if you find it helpful, include a link to this thread, or any other.

                  Start a new thread and be sure to give it a title using ONLY search friendly key words that accurately describe your [u]thread content or overall objective[//u].

                  Click the link to the forum rules in my signature and take some time to read them.
                  AAE
                  ----------------------------------------------------

                  Forum Rules | Message to Cross Posters | How to use Tags

                  Comment

                  Working...
                  X