Maintain connections to multiple databases
 
Quote:

 
Multiple database connections are possible from SQL*XL 5.0.3 but at the moment there is no user interface for it yet.  
 
Please consider the following VBA example how you can programmatically connect to two databases at the same time and how you can switch between them.  Run the Init routine to establish the connections. Switching connections can be done using the activate subs.
 
Edited:

 
Code:

Option Explicit

Dim strIndexNWind As String
Dim strIndexBiblio As String

Sub ConnectNWind()
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="Admin", PassWord:="", DBAlias:="NWIND.MDB (c:\NWIND.MDB)", ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\NWIND.MDB;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False", AllowTransactions:=True
  strIndexNWind = SQLXL.Database.ConnectionIndex
End Sub

Sub ConnectBiblio()
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="Admin", PassWord:="", DBAlias:="BIBLIO.MDB (c:\BIBLIO.MDB)", ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\BIBLIO.MDB;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False", AllowTransactions:=True
  strIndexBiblio = SQLXL.Database.ConnectionIndex
End Sub

Public Sub Init()
  SQLXL.InitialiseSQLXL
  ConnectNWind
  ConnectBiblio
End Sub

Public Sub ActivateNWind()
  SQLXL.Database.ConnectionIndex = strIndexNWind
End Sub

Public Sub ActivateBibio()
  SQLXL.Database.ConnectionIndex = strIndexBiblio
End Sub
 

 
To create this code I have recorded the connection to the two databases in separate macros. This will ensure the correct connection strings will be created and coded. Then I coded the init sub. The first line to SQLXL.InitialiseSQLXL ensures correct initialisation of the software. Then I call both connection subs that I just recorded. For each sub I added a line to save the connection index. We need these to switch connections.