Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

From here: https://superuser.com/questions/579900/why-cant-excel-open-a-file-when-run-from-task-scheduler

1. Open Component Services (Start -> Run, type in dcomcnfg)
2. Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
3. Right-click on Microsoft Excel Application and choose Properties
4. In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok
Keeping it as the interactive user or the launching doesn't work with the task scheduler unfortunately, even when setting the task up to run under an account that has admin access to the machine.


From here: https://www.access-programmers.co.uk/forums/threads/subreport-headers.256308/
In the Subreport do the following and you will have the Report header on subsequent pages.

1. Click on "Add a Group"
2. Select "Expression"
3. When the Expression Window appears, type in "=0" and click OK
4. Make this group only have a header section
5. Click on the group's header section
6. Go to Property Sheet and make sure "Repeat Section" is set to "Yes"

Question and solution here: https://superuser.com/questions/402070/run-task-scheduler-task-on-demand-from-limited-user-account-in-windows-7

Go to C:\Windows\System32\Tasks find the related task and assign "read and execute" rights to the user you want to be able to access it. Be sure to assign to "current object only." Then the task will be visible and runnable from the limited user, and it will work if you saved your credentials in it and checked "run whether user is logged on or not."

a) Commented out the line where the phantom breakpoint appears
b) Copied the same line to the next line.
c) Saved the file.
d) Reran.

From here: https://support.microsoft.com/en-gb/help/2957623/cannot-access-shared-files-or-folders-on-a-drive-in-windows-server-201


You may experience one of the following issues:
  • You can see the shared files but cannot open them.
  • When you open a shared file, the loading progress bar is displayed as zero percent.
  • When you open a shared file or a folder, Windows explorer freezes.
  • If you try to restart the Server service, the service freezes in the stopping state.
  • Microsoft Office Access databases (.mdb files) that are hosted on Windows Server 2012 shares may be corrupted.
  • A "file is locked by another user" or "is locked for editing" error occurs in Microsoft Excel.
  • SMB2 dialect negotiation receives only a TCP ACK response.

To work around these issues, follow these steps:

  1. Disable the leasing on the file server. To do this, run the following command:
    REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters /v DisableLeasing /t REG_DWORD /d 1 /f 
    Note After you set this registry key, SMB2 leases are no longer granted, but oplocks are still available. This setting is primarily used for troubleshooting.
  2. Restart the file server, or restart the server service. To restart the service, run the following commands:


  1. Dim cn As New ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim strSQL As String
  5. strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"
  7. Set rs = New ADODB.Recordset
  9. With rs
  10.   .Source = strSQL
  11.   .ActiveConnection = CurrentProject.Connection
  12.   .CursorType = adOpenDynamic
  13.   .LockType = adLockOptimistic
  14.     .Open
  16.    Do While Not .EOF
  17.      Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
  18.        .MoveNext
  19.    Loop
  20. End With
  22. rs.Close
  23. Set rs = Nothing


You SHOULD replace the Nz with an IIf(IsNull()) construct as it's prone to Errors for NOT reason.

See documentation for IIf and IsNull

When put together:Nz(expr, [valueifnull])
becomesIIf(IsNull(expr), valueifnull, valueifnotnull)

Default: Nz(tbl.A) => IIf(IsNull(tbl.A), '', tbl.A)
With fallback: Nz(tbl.A, tbl.B) => IIf(IsNull(tbl.A), tbl.B, tbl.A)

These are som items that need to be considered/done when upgrading from MS Access backend to SQL Server backend.

1. Replace # with single quote (') for Dates in SQL.
2. Add Timestamp to all SQL tables.
3. Add dbSeeChanges to all DAO CurrentDB.Execute SQL statements. dbFailOnError should become dbSeeChanges + dbFailOnError
4. Convert all MulitValue fields to flat fields. This usually like multi-select combo baoxes that have to be converted to Listboxes.
5. Convert all Attachment Fields to VarBinary subTables!

If you convert your MS Access backend to SQL Server backend you will lose the datagrid/datasheet filter dropdown list.

To get it back you need to go to File-Options-Current Database. Under the Filter Look Up you will see ODBC Fields. Just check that box, close and open the app. Then it's available.

One point to watch for is that if the data is read-only, it will not filter.




The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


<<  August 2020  >>

View posts in large calendar

Sign in