ms access mdb to ms sql express

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

vm hyperv windows 10 1903 with powershell

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!"
    }



}