1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Error 3051 - Cannot open file in Access

Discussion in 'Business Applications' started by gfg, May 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. gfg

    gfg Thread Starter

    Joined:
    Aug 18, 2005
    Messages:
    128
    I have export code in Access 2003 that creates an Excel export.

    It works fine in XP pro - does not work in Vista business

    I get the message:

    The Microsoft Jet database engine cannot open the file c:\Users\Ansley\Documents\05_08_08.xls. It is already opened exclusively by another user, or you need permission to view it's data.


    Here is the code:


    Public Function ExportFile(ExportType As String) As Boolean

    Dim strSQL As String
    Dim DPath As String
    Dim sl As String
    Dim response As String
    Dim Complete As Boolean


    DPath = "C:\Users\Ansley\Documents\"

    DoCmd.SetWarnings False

    strSQL = "DELETE tblExport_1.* FROM tblExport_1;"
    DoCmd.RunSQL (strSQL)

    response = MsgBox("You are going to create an export in Excel. Do you want to continue?", vbYesNo, "Export")
    If response = vbYes Then
    FName = InputBox(Prompt:="What file name do you want to use?", _
    Title:="File Name", Default:=Format(Date, "MM_DD_YY") & ".xls")
    If FName = "" Or IsNull(FName) Then
    MsgBox "Exiting - no file name given.", vbCritical
    Exit Function
    End If
    Path = InputBox(Prompt:="Where do you want to save the file?", _
    Title:="File Path", Default:=DPath)

    If Path = "" Or IsNull(Path) Then
    MsgBox "Exiting - no path given.", vbCritical
    Exit Function
    End If

    DoCmd.SetWarnings False

    If Nz(ExportType, "") = "Standard" Then

    If Me.chkGift = -1 Then
    DoCmd.OpenQuery "qryExport_3"
    Else
    DoCmd.OpenQuery "qryExport_1_NoGift"
    End If
    Else
    If ExportType = "Entered" Then DoCmd.OpenQuery "qryGiftsEnteredToday"
    If ExportType = "Received" Then DoCmd.OpenQuery "qryGiftsReceivedToday"
    End If

    DoCmd.SetWarnings True
    DoCmd.TransferSpreadsheet acExport, , "tblExport_1", Path & FName, True

    MsgBox "Table " & FName & " " & "Exported to " & Path, vbInformation, "Export Information"

    Else
    MsgBox "Did not create export.", vbCritical
    End If


    DoCmd.SetWarnings True

    ExportFile = Complete

    Exit_cmdExport_Click:
    Exit Function

    Err_CmdExport_Click:
    MsgBox Err.Description
    Resume Exit_cmdExport_Click

    DoCmd.SetWarnings True
    End Function


    Any suggestions? I do not think it has anything to do with security or rights.


    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You have to be very carefull of Permissions in Vista compared to XP Pro, so it could be the Folder permissions.
    Have you run this code once or twice.
    I have noticed that Access VBA quite often leaves the Excel file open and unobtainable to everything except VBA, but it can even stop that using it.
    Have you completely closed down the computer and run the code again?
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Does the Excel File exist?
    Is it created?
     
  4. gfg

    gfg Thread Starter

    Joined:
    Aug 18, 2005
    Messages:
    128
    the file does not exist

    the funny part is that the code ran once last week, and then stopped working

    the default file name is the current date - so it is a new name every day, at least one time, and it never created this file.

    The folder is owned by the user - so I cannot understand why it would be a security issue

    I heard that it could be related to the registry - that certain file types are restricted in Access 2003 - but it works on my XP PC.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I notice that you are putting it in c:\Users\Ansley\Documents, Vista may not give Access rights to that Folder as it belongs to Ansley, who is presumably you.
    Have tried changing the code to put the file directly on Drive C:
     
  6. gfg

    gfg Thread Starter

    Joined:
    Aug 18, 2005
    Messages:
    128
    Oh how I love Vista :-(

    I am logged in as Ansley when I try to run this code

    I have tried c:\ with the same results
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I get exactly the same error & message, so it is not just you or your computer, I am sure that it is something to do with Vista.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/712149