Permission problems moving file in VB using CreateObject

The code that generates the xml files is as follows:

Public Function WriteTransXMLFile(strFileName As String) As Boolean
    Dim DataShapeConn As ADODB.Connection
    Dim rsTransfer As ADODB.Recordset

    Dim strDataShape As String
    Dim strPathName As String
    Dim strSQL As String

    Dim Err_Number As Long
    Dim Err_Desc As String
    Dim Err_Source As String

    On Local Error GoTo ErrorWriteTransXMLFile

    Set DataShapeConn = New ADODB.Connection

    With DataShapeConn
        .ConnectionString = strConnection ' populated alreay with valid connection string
        .Open
    End With

    strDataShape = "Provider=MSDataShape;data p" & Mid(Trim(DataShapeConn.ConnectionString), 2)

    Set DataShapeConn = Nothing

    Set DataShapeConn = New ADODB.Connection

    With DataShapeConn
        .ConnectionString = strDataShape
        .Open
    End With

    strSQL = "SHAPE {SELECT *" & _
                               " FROM T03A_Document}" & _
                                    " AS DocumentChapter" & _
               " APPEND ({SELECT *" & _
                                " FROM T03B_DocDetails}" & _
                                    " AS DocDetailsChapter & _
               " RELATE docHOCODE" & _
                      " TO ddtParentHOCode)"

    Set rsTransfer = New ADODB.Recordset
    rsTransfer.Open strSQL, DataShapeConn

    rsTransfer.Save strFileName, adPersistXML ' Saves recordset as xml file

    rsTransfer.Close
    Set rsTransfer = Nothing

    DataShapeConn.Close
    Set DataShapeConn = Nothing

    WriteTransXMLFile = True

    Exit Function

ErrorWriteTransXMLFile:
    Err_Number = Err.Number
    Err_Desc = Err.Description
    Err_Source = Err.Source

    If Not rsTransfer Is Nothing Then
        Set rsTransfer = Nothing
    End If

    If Not IsNothing(DataShapeConn) Then
        Set DataShapeConn = Nothing
    End If

    WriteAppLog ("Error in Writing Document Transfer File : " & Err_Number & " - " & Err_Desc)

    WriteTransXMLFile = False
End Function

Hope this clarifies the issue.

Thank you

Vincent

Vincent_Monaghan:
Looks like the problem is not there.

Lets try to find out exactly where the problem is by focusing on particulars.
For example try to move just one file (instead of *.xml use OneName.xml)
Also try to see what happens when you COPY the file instead of moving it.
Also check to see if there is a difference between a file already exisiting at the destination or not.

Dabas

Hi Dabas

Following your suggestions I have made some progress I replaced the 'movefile' code with :

'///////////////////////////////////////////////////////////////////////
        Dim strXMLPattern as String
        Dim strFile as String
        Dim strFromFile as String
        Dim strToFile as String
        Dim arrLocalFiles() as String
        Dim iLocalFiles as Long

        strXMLPattern = Trim(strOutgoingPath) & "*.xml"
        strFile = Dir(strXMLPattern)

        iLocalFiles = 0
        Do While Len(Trim(strFile)) > 0
            iLocalFiles = iLocalFiles + 1
            arrLocalFiles(iLocalFiles) = Trim(strFile)

            strFile = Dir   ' Get next entry.
        Loop

            For i = 1 To iLocalFiles
                strFromFile = Trim(strTransOutgoingPath) & Trim(arrLocalFiles(i))
                strToFile = Trim(strTransOutgoingArchPath) & Trim(arrLocalFiles(i))

                Set fs = CreateObject("Scripting.FileSystemObject")
                fs.Copyfile Trim(strFromFile), Trim(strToFile)
                fs.DeleteFile Trim(strFromFile), True
                Set fs = Nothing
            Next i

'//////////////////////////////////////////////////////////////////////////////////////

The 'CopyFile' works fine but the 'Permissions Error' occurs with the 'DeleteFile' statement.

In answer to the last question, if the file already existed then there is no problem.

Thank you

Regards

Vincent

Vincent_Monaghan,
That is interesting. Your copy and delete actually simulates a move.
Now we know that the issue is the source, not the destination.

Looks like even though you closed rsTransfer and set it to nothing as well as its connection, it still has a lock on the source file.

What happens if rsTransfer writes to memory instead of to a physical file?

Dabas

Hi Dabas

I'm not sure how you write the recordset to memory or how this can be transferred to a file.

Is there some way to get VB to return the file handle so that the file can be released ?

Regards

Vincent

Vincent:
I was afraid you would ask me that question!
It is quite easy to do in .NET, and a little harder in VB6

I found one of my programs where I used what is called a "disconnected dataset"
Essentially you open up the recordset without a connection:

    Set rsMemory = New ADODB.Recordset 'Not much different here
   
    For Each fld In rs.Fields 'In this case I was copying the fields from another table
        rsMemory.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
    Next
    rsMemory.Open 'Open the recordset. Note... no connection

YOu can now use rsMemory.AddNew, rsMemory.Update, etc same as any other recordset

But I am not sure this is the solution to your problem though.

Question is why do you need to save rsTransfer to C:\Outgoingpath\ only to copy minutes later to another location?
Can't you save to the other location straight away?


Dabas

Hi Dabas

I understand now what you mean by memory recordset. I have used these before but it does not suit this case. To give you some background, my client is a construction company who prepares green field sites for construction of office or housing projects. They setup offices out of 40ft containers with a PC and a telephone line. Truck and digger drivers hand in work dockets to the cleck who inputs this data into my application. On a periodic basis an xml of all this data is produced and sent to head office via email or ftp, so wages can be paid and customers charged. The problem is that dialup lines are charge at 15c per min so they cannot have online links to headoffice.

I wish to automate the transfer process, which I have succceeded in doing except for this bug.

Hope this clarifies the issue

Thank you

VIncent

Vincent:
It does clarify.
Somehow or other VB is still locking that file. (I am sure that you will not even be able to delete that file from DOS or from Windows).
Have you upgraded to the latest service packs for VB?
What version of ADO are you using? Maybe if you use another version?

Also from VB help on Save:

------------
For best results, set the CursorLocation property to adUseClient with Save. If your provider does not support all of the functionality necessary to save Recordset objects, the client cursor will provide that functionality.
------------

Have you tried changing the CursorLocation?

Dabas

Hi Dabas

Just to keep you posted on the latest developments. I have also asked Microsoft to help and their first response is as follows : (excerpt from email)

'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

As this a VB6 application the application should run under the logged on user’s credential maening if the user can manually delete the file your application should be able to do so as well.

So are you able to delete the file manually?
Or do you have to restart the machine to be able to delete the file?
And are you able to delete the file manually after a restart of the machine?
Is the application (or the user) able to delete the file before it is sent by email?
Do you have “retry-if-deletion-fails” code in your application?
If so, is the application able to delete the file after a little while?
It could be so that another process is keeping a lock on the file hence the problem to delete it.
I’m not sure that it will give us the answer, but can you please run 2 free utilities called Filemon and Process Explorer?

You can download these utilities from http://www.sysinternals.com

In Process Explorer, when the error is shown, please click the menu “Find” and then “Find handle…”
In the text box enter “xml” (without quotes) and then click “Search”.
Is the file showing up in the list?
If it is, you should see which process has a lock on the file.

If the file is not showing up in the list we could try running Filemon.
Start your application and run it up until the point _before_ xml file is created.
Then start Filemon and continue execution of your application.
When the error pops up, please stop capturing in Filemon.
Then save the log file and email it back to me please.
 
Other causes might be anti virus software running in the background.
Is it possible to pull the network cable from a machine and then disable the anti virus software to see if it makes any changes?

'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I have tried using those 2 freeware apps and they provide an excelent way of tracking what an app is doing.

I will keep you posted with progress!

Thank you

Vincent

At last I have a resolution to the file locking problem. It was simple really, The file zipping app that I was using had a lock on the file. I was able to get an upgrade for the app which solved the problem. As a matter of interest Microsoft sent me this article which helped.

[A while back we featured an article in the MSDN Library about how to add .zip-capabilities to your applications in VB6:
 Add Some Zip to Your Apps (Visual Basic Programmer's Journal 2001)

 http://msdn.microsoft.com/library/en-us/dnvbpj01/html/gs0108.asp?frame=true

 This article was in fact published in agreement with the Visual Basic Programmers Journal, and the original article along with the source-code can be found here:
 Add Some Zip to Your Apps

 http://www.ftponline.com/Archives/premier/mgznarch/vbpj/2001/08aug01/gs0108/gs0108.asp
]
 
Thank you for your help

Regards

Vincent

Vincent:
Thanks for the update.
It is appreciated!

I am glad you have been able to solve your problem too!
And it seems that the problem was not directly related to the VB code at all!

Dabas