How far does Gameboy Advance SP’s backwards compatibility go

No, it is not possible to be able to link Game Boy Advance games on the Nintendo DS. Unless the games you want to play have the multiplayer feature that only uses one unit, you can only play them as one player on the DS, as the GBA games are not able to use the DS wireless features and the DS does not have a connector for a link cable.

Using VBA (I use version 1.8.0) and want to link offline (using the same PC), go to: Options > Emulator and deactivate the option "Pause when inactive window". then go to: Options > Link and activate the "Wireless Adapter".

Now open two VBA, configure joypad 1 for both of them (using different buttons on each), load the game on both, and have fun!

If you want to play in LAN (two PC without internet), but you can try to configure the Link options to the "network" option and start the server, then on the other pc, try to connect on the IP address and port that the server PC is configured.