Connection String

[Phil]

Hi,

I am using the following connection string to run a number of SQL stored
procedures in Excel VBA

"ODBC;Driver={SQL
Server};Server=nae-is-sql1;Database=Database;Trusted_Connection=no;UID=UserID;pword=Password"

I have created a specific login account for UserID with a password, and
allowed it to run the necessary stored procedures, look at tables and look at
certain databases.

When I try to run my script, it keeps showing a dialogue box with the
Username of UserID and it keeps wanting me give it the password which I have
already specified, have I done something really silly here, I want this to be
a DNS less connection, I am using SQL Server 2000 and Excel version 2003.

Thanks PD

 

[NickHK]

Phil,
One way to create/test connection strings is:
- Create a text file suitable. Rename it to "Whatever.udl".
- Double click this file. Enter the appropriate info. Test.
- When connection is successful, close and open the file in your favourite
text editor.

NickHK

 

[Phil]

Hi Nick,

I tried to check my connection against what you suggested and I keep getting
an error of not a valid link file, futher to that I have been running some
profiler checks in SQL when I run the VBA code and it just ignores the
credentials that I have entered and just pass's in my network login details
that I use for my PC, and as I have SQL set up to Windows NT authenticate it
works, but will break if it is run from any other machine, here is my full
piece of code if it offers any more help.

Sub CollegeReport()

Worksheets("College AI Deployment").Activate
Range("A1:X50000").Select
Selection.Delete Shift:=xlUp

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={SQL
Server};Server=Server1;Database=Database;Trusted_Connection=no;UID=User;pword=pass" _
, Destination:=Range("A1"))
.CommandText = Array("select * from CCI2")
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Sheets("College AI Deployment").Select


End Sub


Thanks Phil

 

[Vergel Adriano]

Phil,

Try pwd for the password attribute name instead of pword.

ODBC;Driver={SQL
Server};Server=nae-is-sql1;Database=Database;Trusted_Connection=no;UID=UserID;Pwd=Password

 

[Phil]

Hi Vergel,

Thanks for that, I had just fixed it and was about to re-post, cant believe
how long that took to fix due to a sill syntax error.

Thanks again, Phil