SQL Server Query Macro - Run-time Error '1004'

Question

Hi,

I am new to macros and trying out my luck for the first time. My requirement is to run a query against sql server db to retrieve data. The data will be retrieved\refreshed each time a Form Button is pressed. I have added the query\DB connection using Data Connection Wizard. But it would not work for all users as sharing of passwords might be an issue. So I am using Macros and have the below code right now but it is throwing run-time error '1004' at the connection statement.

Sub Submit_Refresh()

With ActiveSheet.QueryTables.Add(Connection:= _

"Provider=SQLOLEDB.1;Persist Security Info=True;Password=Pass123;User ID=dbuser;Initial Catalog=testdb;Data Source=sqldb;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=qalab;Use Encryption for Data=False;Tag with column collation when possible=False;" _

, Destination:=Range("J1"))

.Sql = Array( _

"select BG_STATUS, BG_PRIORITY, BG_BUG_ID from BUG" _

)

.FieldNames = False

.RefreshStyle = xlOverwriteCells

.RowNumbers = False

.FillAdjacentFormulas = True

.RefreshOnFileOpen = False

.HasAutoFormat = True

.BackgroundQuery = True

.TablesOnlyFromHTML = True

.Refresh BackgroundQuery:=False

.SavePassword = True

.SaveData = True

End With

End Sub

Answer 1

Hi Bill,

Thanks for the suggestion. But I tried with removing and then adding the parameters but it still did nto work. Any other possible cause you can think of?

Answer 2

I would start by stripping out all the parameters of the connection except for Provider, Password, User ID and Data Source and seeing if that worked. 

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk