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
ConnectionType = ??? (Read 5972 times)
GrayDuck
YaBB Newbies
*




Posts: 1
ConnectionType = ???
06.02.06 at 20:36:12
 
I'm trying to follow the below example to create a macro that connects to our database.  I'm having 'issues' with the connection type.  If I leave it as is (lit0040), remove that line all together, or set the type to garbage, I get this message:
 
"SQL*XL could not use the OO4O driver.  If you intend to connect to an Oracle database please install the OO4O driver or use an ADO connection and use an OLE DB or ODBC provider (driver).  If you intend to connect to another database than an Oracle database you need to specify an ADO connection as well.  Please choose a provider (driver) that is specific to your database type."
 
If I set it to '40', the database link properties dialog box comes up.  I can fill in the appropriate values and test the connection (it works) - but then the macro completes and tells me "Connection Failed".
 
I've yet to find any other value that does anything (ADO gives the same message).
 
I'm able to connect manually (Provider: MSDASQL, Connection Type: ADO - ODBC and OLEDB compliant).
 
So, my question is: How to I configure the macro to connect via type ADO?
 
 
 
Private Sub Connect()
 
     If SQLXL.Database.Connected = True Then
           SQLXL.Database.DisConnect
     End If
     SQLXL.Database.ConnectionType = litOO4O
     SQLXL.Database.Connect "scott", "tiger", "beq-local"
     If SQLXL.Database.Connected = True Then
           MsgBox "Connected"
     Else
           MsgBox "Connection Failed"
     End If
End Sub
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: ConnectionType = ???
Reply #1 - 07.02.06 at 09:07:29
 
There are two possible values for the ConnectionType: litOO4O and litSQLXLADO.  
I have programmed an error message that prevents you from setting it to anything else to avoid the confusion.
This will be made available in SQL*XL 4.0.39
 
To find the correct syntax, always use the macro recorder in Excel. That is by far the easiest. I recorded a macro connecting to Oracle using ADO:
 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/02/2006 by Gerrit-Jan Linker
'
 
'
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="scott", PassWord:="tiger", DBAlias:="ora817", ConnectionString:="Provider=MSDAORA.1;User ID=scott;Data Source=ora817", AllowTransactions:=True
End Sub

 
With a little bit of editing you can get the syntax more clear:
 

Sub Connect()
  Dim strUser As String
  Dim strPass As String
  Dim strDBAlias As String
  Dim strConnectionString as String
 
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litSQLXLADO
 
  strUser = "scott"
  strPass = "tiger"
  strDBAlias = "ora817"
  strConnectionString = "Provider=MSDAORA.1;User ID=scott;Data Source=ora817"
  
  SQLXL.Database.Connect strUser, strPass, strDBAlias, strConnectionString
End Sub

 
Note that the last two parameters to the Connect function are optional. I have omitted the AllowTransactions in the last example.
 
Please note that the ConnectionStringMaker to make your connection strings. See the SQL*XL release log for further details where to get the Connection String Maker. It will be released in the future as a separate software program. See:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Back to top
 
« Last Edit: 07.02.06 at 09:29:17 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: ConnectionType = ???
Reply #2 - 30.07.14 at 13:42:11
 
Syntax change
 
 
Please note that in SQL*XL 5.2 a change was made to the API. The ConnectionType parameter has become read only and instead the connection type is passed as a parameter to the Connect routine. For more details, see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html
Back to top
 
 

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