MS Access ADODB method

  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


Undefined function 'Nz' in expression

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)

MS Access to SQL Upgrade Check List

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!

unassociate file types in windows 10 completely

OK here is how to un-associate a file extension to unknown . . . Right click your Desktop, choose 'New' - Text Document When the document opens, you need to save the document with a .bat file extension Click File - Save As At the bottom of the save as dialog, change 'Save as Type' to 'All Files' Manually enter the file name XXX.bat Now right click any file you want to disassociate and choose 'Open with' - 'Choose another app' -' More Apps' Check the box marked 'Always use this app' Scroll to the bottom and click 'Look for another app on this PC' Navigate to the XXX.bat on your Desktop and select that Finally delete XXX.bat from your Desktop and restart your system The file association will be reset to unknown . . .

MS Access Database Check Box List Filters Missing On SQL Server back end

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.

Recovering Hyper-V virtual machines that have disappeared

My Hyper-V VM disappeared and this blog save the day!! 


This may sound strange but to anyone who has used Microsoft Hyper-V for server virtualization for any length of time knows, it is something you have either confirmed to be true or you believe you are losing your mind.

In test environments, disappearing virtual machines may have been attributed to not stopping at Starbucks that morning, assumed hallucinations due to lack of sleep from the all night server maintenance from the night before or simply doubt in the busy world of IT that the missing machine had actually been created in the first place or possibly accidentally been deleted somewhere in the fray.

In some cases, when the affected virtual machine is heavily relied upon for day-to-day business and it suddenly just isn’t there, this problem is confirmed and we start hunting for solutions.

For those that are not sure they saw correctly, I am here to confirm for you that Hyper-V Virtual Machines can and do disappear.  They sometimes disappear right before your very wary sleep deprived eyes as you stare unbelievingly at your Hyper-V Management Console, straining to find that virtual machine you spent 2 weeks setting up and not finding it in the list of available machines.

What happened to it?

Well, simply, the XML configuration file for that machine has most likely become corrupt which makes the Hyper-V management service unable to process it causing the machine to not appear in the Hyper-V console.

Now don’t just go and start deleting files and copying things around.  Hyper-V uses an amusingly complicated series of GUIDs cross linked in multiple XML files and similarly named folders in a particular hierarchy to keep all the virtual machines, their hard disks and their snapshots in sync.  One wrong move here and you will lose a snapshot or possibly the entire virtual machine.  This can also be recovered if you have the virtual disk files but that is research for another article.

Back to our corrupt XML file.  The solution to this problem is really very simple.  Find the XML configuration file for the virtual machine that just disappeared, make a backup copy of it and then open it in Internet Explorer.  You should notice if you scroll down through the file that IE cannot render the entire XML file.  This is due to some malformed XML that Hyper-V has randomly inserted into your file as a bonus just to give you more gray hair and raise your blood pressure a bit if it wasnt high enough already.

If you open your XML file in Visual Studio or some other IDE that marks up XML, you can see the malformed XML and with a little bit of examination and care, determine what needs to be fixed.  I have provided an example of one of my corrupt files below.  If you look carefully, you will see that there are two </configuration> tags at the end of the file and just after the first one, it appears that some of the information from just above it was mangled just a bit and then repeated resulting in some bad XML and a second end </configuration> tag.  I removed everything after the first </configuration> tag, saved the file, restarted my Hyper-V Management Service, and my virtual machine reappeared in my Hyper-V management console.

I have seen other examples of this where the XML file contained control characters or symbols which will cause the same problem.  Removing those characters and restoring the proper XML format usually fixes the problem.

So, when it appears that all is lost, it is actually a very simple fix.  Special thanks to Microsoft for giving me a heart attack and hopefully they can tighten this up in a future build.

Good Luck.

Example of the Malformed XML at the end of the configuration file.  The text in Red is the extra repeated xml.

<stopped_at_host_shutdown type=”bool”>False</stopped_at_host_shutdown>

The end of the XML file should look like this:

<stopped_at_host_shutdown type=”bool”>False</stopped_at_host_shutdown>

To Stop and Restart the Hyper-V Virtual Machine Management Service from a command line, run the following two commands:

Net stop “Hyper-V Virtual Machine Management”
Net start “Hyper-V Virtual Machine Management”

Remote Desktop Authentication Error Has Occurred. The function requested is not supported.

From here: 

Here is the FIX for this issue ..

Change the Group Policy on your local client to use the vulnerable setting 

Run:  gpedit.msc

Go to à Computer Configuration -> Administrative Templates -> System -> Credentials Delegation -> Encryption Oracle Remediation

Open - Encryption Oracle Remediation à  choose Enable  à change protection level àVulnerable à Apply

Changing your own password on a RDP session on Windows Server 2012 R2

Some people recommend using ….control + alt + end on a RDP session to bring up that Windows Security screen, but it does NOT work on nested RDP sessions.

This procedure is the only one which worked for me on a Windows 2012 R2 RDP session:

  1. Click Start
  2. Type osk (to bring up the on screen keyboard)
  3. Hit enter
  4. Once the on screen keyboard is open, hold ctrl+Alt on your physical keyboard, then click on the del key in the on screen keyboard.
  5. Minimize the on screen Keyboard
  6. Click Change a password.


SharePoint 2013 Data-View Web Part that Filters on Multiple Lines of Text Columns


We are using the Bamboo Data-View Web Part in SharePoint 2013 to allow users to filter and export results from a single Library and I have just discovered that the Filter is hidden in a column when you have a Multiple Lines of Text in the grid. This makes the tool pretty useless now so I was hoping that someone has come across a better Web Part?


So the answer is that Multi-line Text fields in SharePoint are NOT searchable or Filterable, end of story. I have found a solution that works. This solution is to keep a SQL Server table update to date with the library data, less the documents of course, and use a nvarchar(4000) column for the multi-line text fields. We will never exceed that number of characters so this works great plus it's much faster than rendering a SharePoint Library!