Blog

MS Access: when was my form changed? Is ServerFilter set?

What forms have I changed recently?

Sub CheckForms()
    'checks forms in db


    Dim s As String
    Dim d0 As Date
    
    d0 = DateAdd("d", -7, Date)
    
    Dim ao As AccessObject
    
    s = "Forms modified in past 7 days:" & vbCrLf

    For Each ao In Application.CurrentProject.AllForms
        If (ao.DateModified > d0) Then
            s = s & ao.Name & " modified " & ao.DateModified & vbCrLf
        End If
    Next
    MsgBox s
End Sub
    

What forms have ServerFilter set? You can accidentally leave this set to a value if you make a small change to the design of the form. Write a sub to check your forms before you publish.

For Each ao In Application.CurrentProject.AllForms
        fn = ao.Name

        DoCmd.openForm fn, acDesign
        Set f = Forms(fn)
        filt = "" & f.Properties("ServerFilter")
        If Len(filt) > 0 Then
            s = s & fn & " Server Filter " & filt & vbCrLf
        End If
        DoCmd.Close acForm, fn

    Next

Notepad++ regex capture groups: a=b to b=a

got some equations like

param1 = src.Name
param(2) = src.Width
param(3) = src.Name
param(1) = src.Name

and you want to change them to

src.Name = param1
src.Width = param(2)
src.Name = param(3)
src.Name = param(1)

Using Notepad++ Find and Replace: Regular Expression mode

Find ([^=\n\r])=([^=\n\r])
Replace With ${2}=${1}

VSCode cannot find js / tsc / module in a typescript node app

vscode sometimes can’t find files or executables, on windows, because of the path separator. This includes tsc, ionic and cordova

For example,

error TS5058: The specified path does not exist: c:projectssomeprojectstuff.js
cannot find tsc
 \\node_modulesmochabin_mocha 

This could be a bash/cmd thing.

Some possible workarounds…

Add the tsc, ionic, cordova or vscode directory to your path

C:\Users\nick\AppData\Roaming\npm\node_modules\typescript\bin
C:\Users\nick\AppData\Roaming\npm\node_modules\ionic\bin
C:\Users\nick\AppData\Roaming\npm\node_modules\cordova\bin

change to the bash (or cmd) shell in .vscode/settings.json

 {
    "terminal.integrated.shell.windows": "C:\\Program Files\\Git\\bin\\bash.exe",
    "xxterminal.integrated.shell.windows": "C:\\windows\\system32\\cmd.exe"

} 

Make sure there’s a SPACE in your project folder (e.g. c:\projects\My Project). This forces tsc.js to put “” around the path

Install Typescript locally (npm i typescript –save)

Override the typescript in .vscode/tasks.json with your version that includes the full path to tsc…

 {
    "version": "2.0.0",
    "tasks": [
        {
            //"type": "typescript",
            //"tsconfig": "tsconfig.json",
            "label": "tsc",
            "problemMatcher": [
                "$tsc"
            ],
            "group": {
                "kind": "build",
                "isDefault": true
            },
            "type":"shell",
            "windows":{
                "command": "/C/Users/nick/AppData/Roaming/npm/node_modules/typescript/bin/tsc",
                "args": [
                    "-p",
                    "\"${workspaceFolder}/tsconfig.json\""]
            }
        }
    ]
} 

Instead of type: typescript we’ve got type: shell, and a windows {} property. This includes the unix version of the full path to the tsc bash batch file.

This is the build task, but to make sure it’s completed before you launch your node typescript code, in .vscode/launch.json, set it as preLaunchTask like this…

 "version": "0.2.0",
    "configurations": [
        {
            "type": "node",
            "request": "launch",
            "name": "Launch Program",
            "program": "${workspaceFolder}\\index.ts",
            "outFiles": ["${workspaceFolder}\\index.js"],
            "preLaunchTask": "tsc"
        } 

https://github.com/microsoft/vscode/issues/48149

https://github.com/Microsoft/vscode/issues/35593

Azure, Visual Studio: Add your Ionic/Angular/PWA (serverless computing?)

You’ve got a C# webapi that you’ve written in Visual Studio. You’ve got one or more Angular/Ionic/PWA front-endd. You want to host your front-end on the same Azure web app as the web api.

In VS Code, edit your angular app and make sure the API points to your url http://allmyapps.azurewebs.net/api

Compile your angular app using –prod

ionic build --prod

In VS, create a MyDogsApp folder, then create a Pre-Build Event

xcopy C:\Projects\MyDogs\www*.* C:\Projects\myapps\MyDogsApp /E /Y

In VS, build your project, and make sure the built files from your angular app get copied to the folder within your VS project

In VS, UNLOAD your project.

Now Edit your project (.csproj)

Add an ItemGroup

<ItemGroup>
    <Content Include="MyDogsApp\**" CopyToOutputDirectory="Always" />
</ItemGroup>

In VS, Reload your project.

In VS, Publish your project to a folder and check the MyDogsApp files are in a folder next to Content.

Next time you build, the latest build will be copied to the folder within your project. Then when you publish, those files will be copied to the output. So you can get to your wonderful front-end using

  https://allmyapps.azurewebsites.net/MyDogsApp

VBA function to join strings

Building SQL in VBA? BAD programmer. BAD. me too.

'join strings using joiner. a can be list of string params, or a single collection, or a single array
Function Join(joiner As String, ParamArray a() As Variant) As String
    'examples
    'w = join(" and ","customer=12","color=red")
    'w=join(" and ",array("length=3","width=2"))
    'dim col as new collection: col.add("height=77")
    'col.add("material=steel")
    'w=join(" and ",col)

    Dim s As String
    Dim i As Integer
    Dim c As Variant

    If Not IsArray(a) Then
        s = ""
    ElseIf UBound(a) = 0 Then
        If TypeOf a(0) Is Collection Or IsArray(a(0)) Then
            For Each c In a(0)
                If Len(c) > 0 Then
                    If Len(s) > 0 Then s = s & joiner
                    s = s & c
                End If
            Next
        Else
            s = ""
        End If
    Else
        For i = LBound(a) To UBound(a)
            If TypeOf a(i) Is Collection Then
                For Each c In a(i)
                    If Len(c) > 0 Then
                        If Len(s) > 0 Then s = s & joiner
                        s = s & a(i)
                    End If
                Next
            ElseIf Len(a(i)) > 0 Then
                If Len(s) > 0 Then s = s & joiner
                s = s & a(i)
            End If
        Next i
    End If
    Join = s

End Function

Sub TestJoin()
    Dim s As String
    Dim t As String
    Dim arr As New Collection
    Dim a As Variant

    t = "apples,pears,oranges"

    a = Split(t, ",")

    s = Join(",", Split(t, ","))
    If s <> t Then
        MsgBox "the sword has not been mended"
    End If

    s = Join(" ", "hello", "access", "world")
    If s <> "hello access world" Then
        MsgBox "I'm sorry Dave I can't do that"
    End If

    arr.Add "hello"
    arr.Add "access"
    arr.Add "world"

    s = Join(" ", arr)
    If s <> "hello access world" Then
        MsgBox "i only wanted to say hello"
    End If

End Sub

Embedding an Ionic app in a MVC / C++ web app

You want to embed a PWA version of your ionic app in your web site, but don’t want to pay to host another site (or have to remember another URL)

Build the prod version of your app

ionic cordova build browser

Ionic / Cordova wants a file at /config.xml, so copy your config.xml there

Create a folder for your ionic app in the project folder.
To get the published files into that folder, add a Pre Build Event.

xcopy C:\Projects\MyFluJab\platforms\browser\www*.* C:\Projects\faf02b\Faf02\MyFluJab /E /Y
xcopy C:\Projects\FafIonicAdmin\www*.* C:\Projects\faf02b\Faf02\FafAdmin /E /Y

Include it, and all the files, in the .csproj.
Right-click and Unload Project.
Right-click and Edit Project File.
Add the following ItemGroup:

You will need the following mime types in web.config

<system.webServer>

<staticContent>
<mimeMap fileExtension=".json" mimeType="application/json; charset=UTF-8" />
<mimeMap fileExtension=".woff2" mimeType="font/woff2" />
<mimeMap fileExtension=".xml" mimeType="application/xml; charset=UTF-8" />
</staticContent>

VSCode, Typescript, Node : ts.cmd not found, exit code: 127

/usr/bin/bash: tsc.cmd: command not found
The terminal process terminated with exit code: 127

The full error message I got was

/usr/bin/bash: c:Projectsnicksprojectnode_modules.bintsc.cmd: command not found
The terminal process terminated with exit code: 127

This post: https://github.com/Microsoft/vscode/issues/35593 shows how to fix it: create a settings.json file in your project folder (c:\projects\nicksproject\settings.json) containing

{    
    "xxterminal.integrated.shell.windows": 
        "C:\\Program Files\\Git\\bin\\bash.exe",    
    "terminal.integrated.shell.windows": 
        "c:\\Windows\\SysWOW64\\cmd.exe"  }

Angular Interceptor authorization: headers are now immutable

In Angular 4, headers are immutable, so to add (say) an Authorization header in your interceptor you have to use a cunning overload of the .clone method.

My complete interceptor looks like this…

import { HttpInterceptor, HttpRequest, HttpHandler, HttpEvent } from "@angular/common/http";
import { Observable } from "rxjs/Observable";
//import { FafService } from "./faf-service";
import { Injectable } from "@angular/core";
@Injectable()
export class FafInterceptor implements HttpInterceptor{
public theToken:string = null;
constructor(){
}
intercept(request: HttpRequest, next: HttpHandler): Observable<HttpEvent>
{
let theToken = window.localStorage.getItem('theToken');
//TODO: IsGuid
if(theToken && theToken != 'null'){
var NewRequest = request.clone(
{
setHeaders:{'Authorization': 'Bearer ' + theToken}
}
);
//https://angular.io/api/common/http/HttpRequest
console.log('auth header now ' + NewRequest.headers.get('Authorization'));
return next.handle(NewRequest);
}
return next.handle(request);
}
}

Angular mocking XHRBackend: how to call mockRespond

I’m adding unit tests to my API and had a bit of bother mocking the API response. However after a lot of console.log and much head scratching I found some voodoo that works.

TWO KEY TAKEAWAYS

1 . Josh Morony’s blog post said the call is

connection.mockRespond ( new Response ( newResponseOptions(...

That didn’t work for me, I had to use

connection.mockRespond ( newResponseOptions ( body: myObjects

2. But that’s not all, my live response (from a MS WebApi) has a ._body property, my mocked response has a .body. So I had to do something like this


if(res['body']){ //works with mocks
//...
}else if(res['_body']){//works in live system
//...
}

I started with this complete example:

git clone https://github.com/ionic-team/ionic-unit-testing-example.git

(The version numbers of the npm packages have to be compatible)

And worked through this blog post (and others!):

Test Driven Development in Ionic 2: Http and Mocks

This would have been a lot easier if I could figure out how to attach a debugger to Karma, no luck with that so far. Anyhow my complete dummy-service.spec.ts (including code for the service) is…

(just as soon as i figure out how to paste code into wordpress…)

import {Injectable} from '@angular/core';
import { Http, HttpModule, XHRBackend, ResponseOptions, Response } from '@angular/http';
import { TestBed, inject } from '@angular/core/testing';
import { MockBackend } from '@angular/http/testing';

@Injectable()
export class DummyService {
  access_token:string;
  constructor(public http:Http){}
  get(){
    console.log('DummyService Test1 F');
    this.http.get('http://localhost').subscribe((res:Response)=>{
      console.log('DummyService Test1 G got ' + JSON.stringify(res));
      if(res['body'] && res['body']['access_token']){
        this.access_token = res['body']['access_token']; //works with mocks
        console.log('loginUser got token from body (we are using a mock)');
      }else if(res['_body'] && JSON.parse(res['_body']) 
&& JSON.parse(res['_body']).access_token){
        this.access_token = JSON.parse(res['_body']).access_token; //works with live system
        console.log('loginUser got token from _body (we are live)');
      }
      console.log('access_token is ' + this.access_token);
    });
  }
}

describe ('DummyService http method',()=>{
  beforeEach(()=>{
    TestBed.configureTestingModule(
      {
        imports:[HttpModule],
        providers:[
          {provide: XHRBackend, useClass:MockBackend},
          DummyService
        ]
      }
    );
  }
  );

  it('should call get and get response',inject([DummyService, XHRBackend],(dummyService, mockBackend)=>{

    console.log('DummyService Test1 A');

    let r1 = "hello"; //works
    let r2 = "{access_token:'SOMETOKEN'}"; //works
    let r3 =  //doesn't work, service gets empty object {}
        new Response(
            new ResponseOptions(
            {
                url:'url',
                status:200,
                body: "here is the info"
            }
            )
        );
    let r4 = JSON.stringify(r3);//doesn't work, service gets empty object {}
    let r5 = new ResponseOptions( //aha THIS is the bunny...
        {
            body: {access_token:'SOMETOKEN'}
        }
        );

    mockBackend.connections.subscribe(
      connection=>{
        console.log('DummyService Test1 D v4 r5');
        connection.mockRespond(r5); 
      }
    );

    console.log('DummyService Test1 B');

    dummyService.get();

    console.log('DummyService Test1 C');
    
    expect(dummyService.access_token).toEqual('SOMETOKEN');

  }));

});

 

Upload changes only to your published web site (patch your web site files)

When you publish a web site you get all the files in a .zip or in a folder.

That’s a lot of files to transfer, especially if you can’t easily publish a package or you have a slow connection to your web server.

But with each version of the web site you have only changed a few files.

So after the first publish, write a .cmd or .bat file using xcopy to put just the changed files into another folder. Something like…

 

xcopy MyProject MyProjectUpdate /D:2-19-2018 /S /I /Y

 

/D:m-d-y Copies files changed on or after the specified date.
If no date is given, copies only those files whose
source time is newer than the destination time.
(change this every few days, and also clear out the destination folder MyProjectUpdate )

/S Copies directories and subdirectories except empty ones.

/I If destination does not exist and copying more than one file,
assumes that destination must be a directory.

/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.