Results 1 to 9 of 9

Unable to open a linked table from Access db executed by batch file

  1. #1
    Join Date
    Apr 2019
    Posts
    4

    Unable to open a linked table from Access db executed by batch file

    Windows 10, Access 2016
    If I run the Batch file directly (double click) it works, if it is started by a Scheduled Task it fails - just stops running with no obvious Event Error


    The batch command is: "C:\Program Files\Microsoft Office\Office16\MSAccess.exe" "C:\Local Shared Folders\Access FE\Create_Client_List_Stub.accdb"
    The stub (test) Access database opens a linked table on the C drive but fails when it tries to open a linked table on a network drive (Windows 2012 R2)
    The network folder and backend database have Security set for Everyone full access

    Access Code:

    Public Function Start()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "LogTime_1"
    DoCmd.SetWarnings True

    Set CurrentDatabase = CurrentDb()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Log_2"
    DoCmd.SetWarnings True

    ' Setup variables for table to contain extracted Client information
    Set ClientExtractTable = CurrentDatabase.OpenRecordset("Client_List")
    Set ExtractID = ClientExtractTable.Fields("Client_ID")
    Set ExtractLastName = ClientExtractTable.Fields("Client_Last_Name")
    Set ExtractFirstName = ClientExtractTable.Fields("Client_First_Name")
    Set ExtractAddress = ClientExtractTable.Fields("Client_Address")

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Log_3"
    DoCmd.SetWarnings True

    /Code

    There is an AutoExec Macro which executes the Start function

    The queries update a log table (linked table on the C Drive) to identify where the processing stops - the last entry is 2, before the OpenRecordset command

    I asked this question on the Microsoft Access forum but did not receive an answer

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,073
    Does the batch script use Shell Run command?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    Does the batch script use Shell Run command?
    This is the command that fails
    Start /Wait "C:\Program Files\Microsoft Office\Office16\MSAccess.exe" "\\X_Server\Access\Front End\Create_Client_List.accde"

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,073
    I use VBScript file to open db.

    Dim objFSO, oShell
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'open the Access file
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""C:\path\database.accdb"""
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    I use VBScript file to open db.

    Dim objFSO, oShell
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'open the Access file
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""C:\path\database.accdb"""
    I tried VBscript - same problem

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,073
    Was never an issue on my network.

    Perhaps this is a network permissions issue?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Apr 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    Was never an issue on my network.

    Perhaps this is a network permissions issue?
    The batch file runs ok if I execute it directly but fails when it is run as a Scheduled Task
    The first command in the file is a Copy which copies a network file so the problem is with Access

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,073
    The batch file has copy code? Why would that mean problem is with Access?

    Sorry, don't think I can help because can't replicate issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    Usually this kind of issues are caused by not having the right permissions when the scheduled task happens, there are quite a few threads out there:
    https://superuser.com/questions/6225...n-system-drive
    https://community.spiceworks.com/top...-runs-manually

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open Access 2016 With Batch File
    By kdbailey in forum Access
    Replies: 16
    Last Post: 03-15-2018, 09:26 AM
  2. Replies: 1
    Last Post: 11-30-2015, 07:30 AM
  3. Open and log in to database through batch file
    By spacekowboy in forum Access
    Replies: 9
    Last Post: 11-02-2015, 03:22 PM
  4. Replies: 4
    Last Post: 03-02-2015, 07:54 PM
  5. Replies: 6
    Last Post: 09-14-2012, 11:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums