Programming corner wildboy85: 2019

Hi,

I had to convert a database access to Microsoft sql express
I wanted to keep the forms in access to minimize the work

Whatever i did, the search from access was always slower in mssql than it was with a simple access file (.mdb)

I used linked tables, 13 seconds to go to the end of the 13000 records with ms access search
I used a query (in access) directly to the sql server with a dsn, 3-4 seconds to search last recordm and sometimes, even with an index, my form became readonly...

I had to use a mixed technique, to fill a recordset from ms sql, then push it as source in the ms access form

Here the precious VBA code to do that in a form:

-------------- vba macro in access --------------------------

Private Sub form_open(cancel As Integer)

sql01 = 1

If sql01 = 1 Then
db_Server01 = "mssqlipaddressorservername\sqlexpress,1533"
database01 = "databasename"
db_login01 = "sa"
db_password01 = "password"

Dim Conn1 As New ADODB.Connection

x = 0
Dim arrcon01()
con01 = ""

'=== mssql express 2017 connection build
ReDim Preserve arrcon01(x): arrcon01(x) = "Provider=SQLOLEDB": x = x + 1
ReDim Preserve arrcon01(x): arrcon01(x) = "Data Source=" & db_Server01: x = x + 1

If Len(db_login01) = 0 And Len(db_password01) = 0 Then
'=== use windows login if no login adn password
ReDim Preserve arrcon01(x): arrcon01(x) = "Integrated Security=SSPI": x = x + 1
ReDim Preserve arrcon01(x): arrcon01(x) = "Persist Security Info=True": x = x + 1
Else
'=== adodb login and pass (sqlserver is different syntax)
ReDim Preserve arrcon01(x): arrcon01(x) = "user id=" & db_login01: x = x + 1
ReDim Preserve arrcon01(x): arrcon01(x) = "password=" & db_password01: x = x + 1
End If

'=== ref code
'ReDim Preserve arrcon01(x): arrcon01(x) = "Initial Catalog=" & db_name01: x = x + 1

For i = 0 To UBound(arrcon01)
If i < UBound(arrcon01) Then
con01 = con01 & arrcon01(i) & ";"
Else
con01 = con01 & arrcon01(i)
End If
Next

Conn1.ConnectionTimeout = 3
'Conn1.CommandTimeout = 3600
Conn1.ConnectionString = con01
Conn1.Open

Set Rs01 = CreateObject("ADODB.Recordset") ' recordset
'Rs01.LockType = adLockOptimistic
'Rs01.CursorType = adOpenKeyset

method01 = 1
If method01 = 0 Then

ElseIf method01 = 1 Then
'https://sourcedaddy.com/ms-access/working-with-ado-recordsets.html
Dim cmd1 As New ADODB.Command
Set cmd1.ActiveConnection = Conn1

s = "SELECT * "
s = s & " FROM database.dbo.table order by no"

'MsgBox ("sql access cmd: " & s)

cmd1.CommandText = s
cmd1.CommandType = adCmdText ' adCmdStoredProc
Rs01.Index = "id"
Rs01.CursorLocation = adUseClient
Rs01.CursorType = adOpenStatic
Rs01.LockType = adLockBatchOptimistic

Rs01.Open cmd1, , adOpenKeyset, adLockPessimistic

ElseIf method01 = 2 Then
'=== method 2
'https://stackoverflow.com/questions/37013322/receiving-an-error-the-object-you-entered-is-not-a-valid-record-set-property
' was slow, removed

End If
ElseIf sql01 = 0 Then

End If

If Not Rs01.EOF Then
'stDocName = "principal"
'Me.RecordSource = rs01
'MsgBox ("query: " & s)
'DoCmd.OpenForm stDocName, OpenArgs:=""
'DoCmd.OpenForm stDocName, RecordSource:=s
'=== reference from florida
'Me.RResidentContrat_sous_formulaire1.Form.RecordSource = sql
'Me.RResidentContrat_sous_formulaire1.Form.Requery
'MsgBox ("query" & s)
'DoCmd.OpenForm (stDocName), OpenArgs:=s
'Forms(stDocName).RecordSource = s
'Me.RecordSource = s
Set Me.Recordset = Rs01

Else
msg01 = "ERROR" & vbCrLf
msg01 = msg01 & "Il n'y a aucun enregistrement dans dbo.dos" & vbCrLf
msg01 = msg01 & "Base de donnee SQL non disponible" & vbCrLf
MsgBox (msg01)
End If

Exit_Procedure:
Exit Sub

End Sub

Hello,

I had to recreate a vm often with a virtual hard disk, because exporting a vm was quite long
So i started storing my virtual disk on a removable usb3 drive

Since hyperv is a service, I needed to recreate the vm on any computer to work with
Only the hard disk was important to restart the vm on another computer quickly

I found code in powershell on internet and adapted it to my needs

I added powershell code references at the end for futur use

2019-08-03 added the state of hyper-v service

################# powershell ####################
# create a vm from a vistual disk
# windows or maybe anything
# 2 cpu
# 4 gig ram

# windows 7
# Set-ExecutionPolicy RemoteSigned
# windows 10
# Set-ExecutionPolicy -scope currentuser RemoteSigned

$test01 = 0
#$test01 = 0

# logfile
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$scriptname = split-path -leaf $MyInvocation.MyCommand.Definition
$Logfilename = $scriptname + "_log.txt"
$logfile = $scriptPath + "\" + $Logfilename
$logall = 1

$msg01 = "START hyper-v vm creation from disk image with same name"
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile}

# error management object
$error01 = New-Object PsObject
$error01 | Add-Member NoteProperty -Name number -value ''
$error01 | Add-Member NoteProperty -Name description -value ''
$error01 | Add-Member NoteProperty -Name error02 -value ''

function error_display($error01)
{
write-host "===== " $error01.description
write-host "===== " $error01.error02
write-host "===== " $error01.error02.InvocationInfo.PositionMessage
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + "===== " + $error01.description | Out-File $logfile -append}
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + "===== " + $error01.error02 | Out-File $logfile -append}
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + "===== " + $error01.error02.InvocationInfo.PositionMessage | Out-File $logfile -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.Exception.itemname: " + $error01.error02.Exception.itemname | Out-File $filnamfull_log01 -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.InvocationInfo.MyCommand.Name: " + $error01.error02.InvocationInfo.MyCommand.Name | Out-File $filnamfull_log01 -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.ErrorDetails.Message : " + $error01.error02.ErrorDetails.Message | Out-File $filnamfull_log01 -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.InvocationInfo.PositionMessage: " + $error01.error02.InvocationInfo.PositionMessage | Out-File $filnamfull_log01 -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.CategoryInfo.ToString: " + $error01.error02.CategoryInfo.ToString() | Out-File $filnamfull_log01 -append}
#if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " error02.FullyQualifiedErrorId: " + $error01.error02.FullyQualifiedErrorId | Out-File $filnamfull_log01 -append}

}

# hyper-v requis
# Windows 10 Enterprise, Pro, or Education
# 64-bit Processor with Second Level Address Translation (SLAT).
# CPU support for VM Monitor Mode Extension (VT-c on Intel CPUs).
# Minimum of 4 GB memory.
# The Hyper-V role cannot be installed on Windows 10 Home.

#Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All
#Install-WindowsFeature -Name Hyper-V -IncludeManagementTools -Restart
#Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All

# check if hyper-v optionnal feature is installed
cls

write-host ""
write-host "Checking if hyper-v is installed..."

$features01 = Get-WindowsOptionalFeature -online

[string[]]$arr_optionnal_feature = "Microsoft-Hyper-V-All" #State : Enabled
$arr_optionnal_feature+= "Microsoft-Hyper-V"
$arr_optionnal_feature+= "Microsoft-Hyper-V-Tools-All"
$arr_optionnal_feature+= "Microsoft-Hyper-V-Management-PowerShell"
$arr_optionnal_feature+= "Microsoft-Hyper-V-Hypervisor"
$arr_optionnal_feature+= "Microsoft-Hyper-V-Services"
$arr_optionnal_feature+= "Microsoft-Hyper-V-Management-Clients"

$found01 = 0
foreach ($feature01 in $features01)
{
if ($feature01.FeatureName -eq $arr_optionnal_feature[0])
{
if ($feature01.State.tostring() -eq "Enabled")
{
#write-host $arr_optionnal_feature[1]
$found01 = 1
$feature01.FeatureName
$feature01.State

}
else
{
$feature01.FeatureName
$feature01.State
}
}
}

if($found01 -eq 1)
{
write-host "--> OK hyper-v is installed"
####################################################
# disk path to create vm from with same name
####################################################
write-host ""
write-host "Getting all virtual disk in current folder..."

# scan file in this folder

$filelist01 = Get-ChildItem -Path $scriptPath -Filter "*.vhd*"

[System.Int32]$i_max = ( $filelist01 | Measure-Object ).Count + 1

write-host ""
write-host "Liste des disques virtuels dans le dossier actuel: "

write-host ""
for($i=1; $i -lt $i_max; $i++)
{
write-host ($i) ($filelist01[$i-1].name)
}

write-host ""
$choice01 = Read-Host -Prompt 'Entrez le choix du disque virtuel'

if (($choice01 -gt 0) -and ($choice01 -lt ($i_max)))
{
write-host ""
$msg01 = "--> Choice: " + $choice01 + " " + $filelist01[$choice01 -1]
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}
write-host $msg01

$filename01 = $filelist01[$choice01 -1]
$vhd_path01 = $scriptPath + "\" + $filename01

# extract vm name from disk image name (we want it to be the same)
$vm_name_arr01 = $vhd_path01.split("\")
$vm_name01 = $vm_name_arr01[$vm_name_arr01.GetUpperBound(0)]
# remove extension after .
$vm_name01 = $vm_name01.Substring(0,$vm_name01.LastIndexOf('.'))

write-host ""
write-host "virtual disk full path: " $vhd_path01
write-host "virtual machine name..: " $vm_name01

if ($vm_name01.length -gt 0)
{
$vm01 = Get-VM| where {$_.name -eq $vm_name01}
if ($vm01.count -eq 0)
{
write-host ""
write-host "Getting switch list, finding any external switch... (so we can patch windows on internet)"

$switchs01 = Get-VMSwitch

$found01 = 0
$switch_name01 = ""

foreach ($switch01 in $switchs01)
{
if ($switch01.switchtype.tostring().tolower() -eq "external")
{
write-host "External switch found: " $switch01.name
$switch_name01 = $switch01.name
$found01 = 1 # caca
}
}

if($found01 -eq 0)
{
################################
# create external v-switch
################################
$netadapters01 = Get-NetAdapter

$found01 = 0

foreach($netadapter01 in $netadapters01)
{
if($netadapter01.name.tostring().tolower() -eq "ethernet")
{
write-host "found ethernet"
$found01 = 1
}
}

if($found01 -eq 1)
{
$switch_name01 = "external01"

$msg01 = "Creating external switch, network might get disrupted"
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}

New-VMSwitch -name $switch_name01 -NetAdapterName Ethernet -AllowManagementOS $true
}
}
else
{
# an external v-switch was found
# we dont create one
}

############################## futur dev ############################
## futur multiple vm creation from array

#[string[]]$arr_vmname = ""
#[string[]]$arr_vmMemoryStartupBytes = 2147483648*2
#[string[]]$arr_Generation = 1
#[string[]]$arr_VHDPath = $vhd_path01
#[string[]]$arr_NewVHDSizeBytes = 120687091200
#[string[]]$arr_BootDevice = "VHD"
#[string[]]$arr_path = Path = $scriptPath + "\" + $vm_name01
#[string[]]$arr_SwitchName = "Nouveau commutateur virtuel"

## parameter number 2 to inputbox

#$arr_vmname += "vm2"
#$arr_vmMemoryStartupBytes += 2147483648*2
#$arr_Generation += $vhd_path01
#$arr_VHDPath += "textbox"
#$arr_NewVHDSizeBytes += 120687091200
#$arr_BootDevice += "VHD"
#$arr_path += $scriptPath + "\" + $vm_name01
#$arr_SwitchName += "Nouveau commutateur virtuel"

# array of parameters
#=== vm parameters
#https://docs.microsoft.com/en-us/powershell/module/hyper-v/set-vm?view=win10-ps

$VM = @{
Name = $vm_name01
MemoryStartupBytes = 2147483648*2
Generation = 1
VHDPath = $vhd_path01
#NewVHDSizeBytes = 53687091200
BootDevice = "VHD"
Path = $scriptPath
SwitchName = $switch_name01 #(Get-VMSwitch).Name
}

try
{
$error01.number = 0
New-VM @VM | out-null
$msg01 = "creating vm: " + $vm_name01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}
}
catch
{
$error01.number = 1
$error01.description = "ERROR " + $msg01
$error01.error02 = $_
}

if ($error01.number -eq 0)
{

$msg01 = "vm created: " + $vm_name01
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}

try
{
$error01.number = 0
set-vmprocessor -vmname $vm_name01 -count 2

$msg01 = "vm processors set to 2: " + $vm_name01
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}
}
catch
{
$error01.number = 1
$error01.description = "ERROR " + $msg01
$error01.error02 = $_
}

if ($error01.number -eq 0)
{
# checkpoints off

$msg01 = "vm checkpoints: disabled"
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}
set-vm -vmname $vm_name01 -CheckpointType disabled
}
else
{
error_display($error01)
}

###############################
# start hyper-v manager
###############################
& (Get-Command "$($env:systemroot)\system32\mmc.exe") @("$($env:systemroot)\System32\virtmgmt.msc")

}
else
{
error_display($error01)
}

#Set-VMNetworkAdapterVlan -VMName vmfrompowershell1 -Access -VlanId 121

}
else
{
$error01.number = 1
$error01.description = "ERROR vm already exist, name: " + $vm_name01
$error01.error02 = ""
error_display($error01)

###############################
# start hyper-v manager
###############################
& (Get-Command "$($env:systemroot)\system32\mmc.exe") @("$($env:systemroot)\System32\virtmgmt.msc")

}
}
else
{
$error01.number = 1
$error01.description = "ERROR vm_name01 empty"
$error01.error02 = ""
error_display($error01)
}
}
else
{
$error01.number = 1
$error01.description = "ERROR bad choice"
$error01.error02 = ""
error_display($error01)
}

write-host ""
$msg01 = "END hyper-v vm creation from disk image"
write-host $msg01
if ($logall=1) {(Get-Date -Format "yyyy-MM-dd HH:mm:ss") + " " + $msg01 | Out-File $logfile -append}

#ref code:
#& (Get-Command "$($env:systemroot)\system32\robocopy.exe") @("$($folder_source_c_01)", "$($folder_destination_here_01)", '/XJ', '/s', '/e', '/R:0', '/W:0', $log01)

# check if it's already existing

# reference:
# vm details
# Get-VM -Name VMWEB01 | Format-List *

# vm disks
#(Get-VM –VMName VMTest | Select-Object VMId | Get-VHD).Path

# vm memory
#Set-VMMemory "VMTest" -DynamicMemoryEnabled $true -MaximumBytes 1GB

# vm start
# Start-VM -name VMTest

#vm stop forced (not gracefull)
# Stop-VM -name VMTest -Force

# vm add checkpoint
# Get-VM -Name VMTest | Checkpoint-VM -SnapshotName “Before Upgrade”

# vm get checkpoints
# Remove-VMCheckpoint -VMName VMTest -Name "Before Upgrade"

# vm list all checkpoints
#Get-VM | Get-VMCheckpoint

#######################
# vm backup
#######################

## Backup Folder
#$Backup = "C:\Backup"
## Get VMs Name
#$VMs = Get-VM | select name

# vm backuo Delete Backup Folder and create it again

## Backup Folder
#$Backup = "C:\Backup"
## Get VMs Name
#$VMs = Get-VM | select name
#if((Test-Path -Path $Backup)){
# Remove-Item $Backup -force -recurse -erroraction:SilentlyContinue
#} else {
# New-Item $Backup -type directory
#}
## Export all the VMs
#foreach ($VM in $VMs)
#{
# Export-VM -Name $VM.Name -Path $Backup
#}

# vm backup schedule
#$trigger = New-JobTrigger -Weekly -DaysOfWeek Sunday -WeeksInterval 2 -At 12:00AM
#$options = New-ScheduledJobOption -RunElevated

#############################################
# vmsnapshot
#############################################
#get-VMSnapshot forea-demox-win10-avantage
#Remove-VMSnapshot

##################################################
# vm management advanced (server)
##################################################

#scvmm console commands
# https://docs.microsoft.com/en-us/powershell/module/virtualmachinemanager/?view=systemcenter-ps-2019

#Import-Module -Name "virtualmachinemanager"

# scvmm
# add console
#SCVMM Management console

# load pssnapin
#Add-PSSnapin Microsoft.SystemCenter.VirtualMachineManager
}
else
{

$error01.number = 1
$error01.description = "ERROR Hyper-v is not installed"
$error01.error02 = ""
error_display($error01)

write-host ""
$choice01 = Read-Host -Prompt "Do you want to install all hyper-v features? (y = yes, n=no, you will have to reboot if it was not installed)"

if($choice01.ToString().ToLower() -eq "y")
{
write-host ""
write-host "Installting all hyper-v features online"
write-host "Executing: Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All..."

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All
}
else
{
write-host "ok bye!"
}

}

Programming corner wildboy85

Sunday, August 11, 2019

utf-8 conversion in VBS script

Friday, July 12, 2019

ms access mdb to ms sql express

Sunday, July 7, 2019

vm hyperv windows 10 1903 with powershell

Thursday, March 14, 2019

Automatic login in office 365 / sharepoint with powershell