Maintain connections to multiple databases Quote:Is it possible to maintain connection to more than 1 database so multiple databases can be updated from excel or do you need to connect/disconnect in between manipulating different
database(s) ?
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.