If a VPN connection is used only once-in-a-while, is it more secure to leave the tunnel active, or activate only when needed?

Activate only when needed is probably the most secure, but this requires someone to activate and deactivate it when needed and this might not be practical. Leaving the tunnel active all the time is ok unless you do not trust the users at the remote LAN. To be more secure, you can setup packet filter rules to allow only certain remote IPs to have access to your local LAN (or a specific server on your local LAN).