Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Maintain connections to multiple databases (Read 3109 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Maintain connections to multiple databases
20.09.10 at 10:38:47
 
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:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
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.
Back to top
 
« Last Edit: 30.07.14 at 13:50:01 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1