Only difference between the Private Subnets and Public Subnets is that, the latter one has connectivity to Internet Gateway established by the Subnet's Route table entry [ example : 10.0.10.0/24 ig-abcdef12 ].
So in your example you would put the ELB in front of the Instances which are in Public Subnet. For the record, you can also create an Internal Load Balancer [ for scenarios like Web Servers in public subnet talking to App Servers fronted by an ELB fully insider the VPC - Private Subnet ]
There is no native support in VPC for what you need.
The root of the problem is that VPC's Hardware VPN isn't really designed for connections to third party networks. It's designed for interconnecting to your VPC to your physical data center network -- a trusted connection. A VPC VPN connection is effectively wide open, subject only to the limitations of your security groups and Network ACLs -- it doesn't have a route table or any filtering of its own, and has some other limitations, so it's really not the best choice for external connections. For connections to your data center, of course... it's excellent.
As we found for outgoing connection, the only way is Cisco AnyConnect
That isn't the only way... but it does have to be done with an EC2 instance running IPSec VPN software. There are three packages I'm familiar with, all of which are similar: openswan, libreswan, and strongswan. You can build your own tunnel server.
If you go this route, it's a little bit tricky to get the IP addresses configured correctly, but it's a viable solution. This is how I establish IPSec with external companies.
The circumstances aren't the same, but the idea of your address being split between the instance's private IP and the instance's Elastic IP (EIP) would be similar to what I suggested for the "left" side ("our" side, by my convention) in Strongswan VPN tunnel between two AWS instances won't connect:
left=10.10.10.10 # instance private IP of local system
leftsourceip=10.10.10.10 # instance private IP of local system
leftid=203.x.x.x # elastic IP of local system
leftsubnet=10.x.x.x/xx
Alternately, there are probably other offerings in the AWS Marketplace that will provide you with an EC2 instance that terminates IPSec tunnels... but there isn't another alternative, unless you have an offsite hardware gateway, outside of AWS, and you want to spoke both a VPC Hardware VPN connection and your third-party connections out of that device.
Best Answer
Yes, you can setup a PC-based VPN into your VPC. (Windows, Mac, Linux)
It's slightly more complicated than say adding a VPN to a firewall oddly, but with some googling you can get it.
The product you're looking for is "Client VPN Endpoint" and would be setup to connect to a specific VPC.
A good overview and instructions are here:
https://docs.aws.amazon.com/vpn/latest/clientvpn-admin/how-it-works.html
I had to hack our Client VPN to work slightly, as we use g-suite SSO, and that required a slight mod not available via the GUI. See: https://stackoverflow.com/questions/63151685/aws-vpn-using-federated-login-with-google-idp-app-not-configured-for-user/63590967#63590967
Once we did that, it works fine.
Some of the other comments mentioned making the RDS instance publically available - from a security standpoint, while obscurity is no security, neither is just making everything public and locking down passwords.
Accessing your RDS only via a VPN is a much better way to go; or you could go to a "Bastion" server which is a machine you connect to (usually SSH CLI, but in principal could be a GUI) and then IT connects to the RDS instance. That's a PITA as you have to install all of your developer tools on it - but is in theory even more secure.