Run Access in background from task scheduler

[iojbr]

Hi:

I have two Access databases. The backend database (BE.mdb) is on a network drive (L The Front end (FE.mdb) is located at users' workstations on local drives (C. I am trying to use Windows task scheduler to run a Macro (Scheduled1) in FE.mdb to update some tables in BE.mdb. It only works when I instruct the task scheduler to run the macro every time a user logs onto his workstation or at a specific time everyday. However, the second option will only work when the user is already logged into the computer. I want the system to do a daily update in the background when the user is logged off. So far, everytime I try this, it doesn't work, and the workstation creates an .ldb file. I thought the problem might be that you need to instruct Windows to map to the network drive before running the macro, but it still won't work. Any help will be appreciated. Below is some code. IMS Back End is the folder that contains BE.mdb in Drive L:


Dim MyDriveName As String
MyDriveName = "\\dhsrlcsrv01\vrdlgroups\VRDLGROUP\IMS Back End (L)"
'------------------------------------------------------------
'- map drive
On Error Resume Next ' DRIVE MAY BE MAPPED ALREADY
Set MyDrive = CreateObject("WScript.Network")
MyDrive.MapNetworkDrive "L:", MyDriveName
DoEvents
'--------------------------------------------------------------
'- error check
If Err.Number <> 0 Then
MsgBox (" Drive already mapped or not available ")
Else
End If

 

[2Grandpa]

To use Windows schedular you must a command line like below:

START c:\"Program Files"\"Microsoft Office"\Office11\Msaccess.exe c:\AC\admin200.mdb /excl /x mac_Update

There are three parts:
1) Start Access: START c:\"Program Files"\"Microsoft Office"\Office11\Msaccess.exe
2) Open your data base: c:\AC\admin200.mdb /excl
3) The name of the macro you want to run: /x mac_Update

At one time I update my database at night until they changed security.

 

[SeeThru]

Could it be a permissions issue - if no-one is logged in the computer may not have access rights to the Back End location?

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[2Grandpa]

I forgot say to put this code in a DOS .bat file. Then schdule this the.bat file to run with Windows Schedular.

 

[iojbr]

Thanks for the reply. I went ahead and created a batch file, but I am still getting the same problem. If I set it to run at a certain time everyday, it will only work if the user is logged on. If the user is logged off, it will create an .ldb file, and the task scheduler will display the status of the scheduled task as "running" indefinetely. The macro I am running consists of two functions. The first one to attempt to map to the correct network drive (L where the backend is located (while logged off), and the second one to actually update tables in the backend once the connection has been made. By the way I tried another method of mapping the network drive which required me to supply my user name and password. The code for the two functions used by the macro is given below:

Option Explicit

'CONSTANTS
Private Const WN_SUCCESS = 0 ' The function was successful.
Private Const WN_NET_ERROR = 2 ' An error occurred on the network.
Private Const WN_BAD_PASSWORD = 6 ' The password was invalid.
Private Const CONNECT_LOCALDRIVE = 256
Private Const CONNECT_REDIRECT = 128
Private Const RESOURCE_GLOBALNET = &H2
Private Const RESOURCETYPE_DISK = &H1
Private Const RESOURCEDISPLAYTYPE_SHARE = &H3
Private Const RESOURCEUSAGE_CONNECTABLE = &H1
Private Const CONNECT_UPDATE_PROFILE = 1
Private Const CONNECT_UPDATE_RECENT = &H2
'ERROR CONSTANTS
'Success
Private Const ERROR_SUCCESS = 0
'An unexpected network error occurred.
Private Const ERROR_UNEXP_NET_ERR = 59&
Private Const ERROR_ACCESS_DENIED = 5&
Private Const ERROR_ALREADY_ASSIGNED = 85&
Private Const ERROR_BAD_DEV_TYPE = 66&
Private Const ERROR_BAD_DEVICE = 1200&
Private Const ERROR_BAD_NET_NAME = 67&
Private Const ERROR_BAD_PROFILE = 1206&
Private Const ERROR_BAD_PROVIDER = 1204&
Private Const ERROR_BUSY = 170&
Private Const ERROR_CANNOT_OPEN_PROFILE = 1205&
Private Const ERROR_DEVICE_ALREADY_REMEMBERED = 1202&
Private Const ERROR_BAD_NETPATH = 53&
Private Const ERROR_EXTENDED_ERROR = 1208&
Private Const ERROR_INVALID_PASSWORD = 86&
Private Const ERROR_NO_NET_OR_BAD_PATH = 1203&
Private Const ERROR_NO_NETWORK = 1222&
Private Const ERROR_SESSION_CREDENTIAL_CONFLICT = 1219
Private Const ERROR_BAD_USERNAME = 2202&
Private Const ERROR_ACCOUNT_LOCKED = 1909
'logon failure: unknown user name or bad password.
Private Const ERROR_LOGON_FAILURE = 1326&
'Logon failure: user account restriction.
Private Const ERROR_ACCOUNT_RESTRICTION = 1327&
'Logon failure: account logon time restriction violation.
Private Const ERROR_INVALID_LOGON_HOURS = 1328&
'Logon failure: user not allowed to log on to this computer.
Private Const ERROR_INVALID_WORKSTATION = 1329&
'Logon failure: the specified account password has expired.
Private Const ERROR_PASSWORD_EXPIRED = 1330&
Private Const ERROR_ACCOUNT_DISABLED = 1331&

'TYPES
Private Type NETRESOURCE
dwScope As Long
dwType As Long
dwDisplayType As Long
dwUsage As Long
lpLocalName As String
lpRemoteName As String
lpComment As String
lpProvider As String
End Type

'API DECLARATIONS
Private Declare Function WNetUseConnection Lib "mpr.dll" Alias "WNetUseConnectionA" (ByVal hwndOwner As Long, ByRef lpNetResource As NETRESOURCE, ByVal lpUsername As String, ByVal lpPassword As String, ByVal dwFlags As Long, ByVal lpAccessName As Any, ByRef lpBufferSize As Long, ByRef lpResult As Long) As Long
Private Declare Function WNetCancelConnection2 Lib "mpr.dll" Alias "WNetCancelConnection2A" (ByVal lpName As String, ByVal dwFlags As Long, ByVal fForce As Long) As Long
Private Declare Function GetActiveWindow Lib "user32" () As Long
Private Declare Function WNetAddConnection Lib "mpr.dll" Alias "WNetAddConnectionA" (ByVal lpszNetPath As String, ByVal lpszPassword As String, ByVal lpszLocalName As String) As Long
Private Declare Function WNetCancelConnection Lib "mpr.dll" Alias "WNetCancelConnectionA" (ByVal lpszName As String, ByVal bForce As Long) As Long
Private Declare Function WNetAddConnection2 Lib "mpr.dll" Alias "WNetAddConnection2A" (lpNetResource As NETRESOURCE, ByVal lpPassword As String, ByVal lpUsername As String, ByVal dwFlags As Long) As Long

Public Function ConnectionAdd3()
Dim tNetR As NETRESOURCE
tNetR.dwScope = RESOURCE_GLOBALNET
tNetR.dwType = RESOURCETYPE_DISK
tNetR.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
tNetR.dwUsage = RESOURCEUSAGE_CONNECTABLE
tNetR.lpLocalName = "L"
tNetR.lpRemoteName = "\\dhsrlcsrv01\vrdlgroups\VRDLGROUP"
ConnectionAdd3 = WNetAddConnection2(tNetR, "Honda02", "hlarson", CONNECT_UPDATE_PROFILE)
End Function


Public Function UpdInventory()
Dim strDbName As String
Dim MyConnStr As String
Dim Cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Dim X As String
Dim MyDriveName As String
Dim lRetVal As Long
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
strDbName = "L:\IMS Back End\IMS BE.mdb"
Set Cnn = New ADODB.Connection
Cnn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDbName & ";" & _
"Jet OLEDBatabase Password=sol;"
rs1.Open "SELECT [Part Number],[Monthly Supply Requirement (By Unit)],[Daily Supply Requirement] FROM [Products]", Cnn, adOpenStatic, adLockOptimistic
rs2.Open "SELECT [Part Number],[Monthly Supply Requirement (By Unit)],[SR-3 Requirement] FROM [Products]", Cnn, adOpenStatic, adLockOptimistic
rs3.Open "SELECT [Part Number],[Units in Stock],[Daily Supply Requirement] FROM [Products]", Cnn, adOpenStatic, adLockOptimistic
Do While Not rs1.EOF
rs1![Daily Supply Requirement] = rs1.Fields("Monthly Supply Requirement (By Unit)").Value / 31
rs1.Update
rs1.MoveNext
Loop
Do While Not rs2.EOF
rs2![SR-3 Requirement] = rs2.Fields("Monthly Supply Requirement (By Unit)").Value * 3
rs2.Update
rs2.MoveNext
Loop
Do While Not rs3.EOF
rs3![Units in Stock] = rs3.Fields("Units in Stock").Value - rs3.Fields("Daily Supply Requirement").Value
rs3.Update
rs3.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
rs2.Close
Set rs2 = Nothing
rs3.Close
Set rs3 = Nothing
Cnn.Close
Set Cnn = Nothing
End Function