Linker IT Software
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 4563 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
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.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"

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.
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:

Sub Connect()
' Connect Macro
' Macro recorded 14/06/2008 by Gerrit-Jan Linker

  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