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
Connect SQLOLEDB for SQLSERVER2000 in vb script (Read 4610 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Connect SQLOLEDB for SQLSERVER2000 in vb script
14.06.08 at 21:16:57
 
Connect SQLOLEDB for SQLSERVER2000 in vb script
 
Quote:
I want to connect SQLOLEDB for SQLSERVER2000 in vb script. How can I write code as script.
For Example ;

I know this code wrong ;

SQLXL.InitialiseSQLXL

SQLXL.Database.Connect UserName:="sa", Password:="sa", DBAlias:="Goldmaks", ConnectionString:="Provider=SQLOLEDB;Persist Security Info=False;User ID=sa; Password=sa;Initial Catalog=GOLDMAKS;Data Source=bak"

 
Answer:
The code does not look wrong at all. I don't know whether the connection string is good.  
 
To verify I have recorded a macro in Excel (Tools/Macros/Record New). If no code is recorded the SQL*XL macro recorder is probably still switched off. Enable it at the program preferences. See the general section.
 
As you can see in the code below, the code is similar to how you wrote it.
 
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:
Sub Connect()
'
' Connect Macro
' Macro recorded 14/06/2008 by Gerrit-Jan Linker
'

'
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="sa", PassWord:="12345", DBAlias:="master", ConnectionString:="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=VM-SQL2005XP\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=VM-SQL2005XP;Use Encryption for Data=False;Tag with column collation when possible=False", AllowTransactions:=True
End Sub
 

Back to top
 
« Last Edit: 30.07.14 at 13:53:35 by Gerrit-Jan Linker »  

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