Execute sql from vbscript

Question

I wanted to execute the below SQL Script on local SQL Express server:

Select *
from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=YES;Database=C:\localpath\file.xlsx;', 
[Sheet1$])

Query run from SQL Express works fine. But when I try to run it from vbscript:

dim dbconn, ConnectionString
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=compname\SQLEXPRESS;Database=DB;ID=username;Password="";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;"
Set dbconn = CreateObject("ADODB.Connection")
dbconn.Open ConnectionString
dbconn.Execute("\\path\query.sql")

it displays following error:

Script: Script path
Line: 4
Char: 1
Error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Code: 800A0BB9
Source: ADODB.Connection

Serenity · Accepted Answer · 2015-04-07 07:54:38Z

1

Did you mean to have a single quote for password as two quotes in a string are treated as 1.

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=compname\SQLEXPRESS;Database=DB;ID=username;Password="";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;"

answered Apr 7, 2015 at 7:54

Serenity

1843 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Adrian Chrostowski Over a year ago

Good point. I have changed that to single quote but there is still an error, different however: Error: Format of the initialization string does not conform to the OLE DB specification.

Adrian Chrostowski Over a year ago

And I have found another apostrphe missing in Properties='Excel 12.0'. After filling this in I am getting the "Could not find installable ISAM." error. Any clues?

Serenity Over a year ago

Here is a web site of connection strings. connectionstrings.com/excel

Adrian Chrostowski Over a year ago

The connection string is correct. But now I get error saying: "ActiveX component can't create object: OLEDB.Connection"

Serenity Over a year ago

Use the Query Helper in Excel. Alt + D, D, N, then double click Excel, browse, choose options, then chosse to edit query at end of wizard. Then in Editor File Save. This text file will have a connection string and sql string in it.

Adrian Chrostowski · Accepted Answer · 2015-04-07 10:28:15Z

1

Correct connection string:

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=compname\SQLEXPRESS"

answered Apr 7, 2015 at 10:28

Adrian Chrostowski

43411 silver badges31 bronze badges

Collectives™ on Stack Overflow

Execute sql from vbscript

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related