SQL Server Username and Password

[Dscalf1]

Hey I have written a macro that automatically pulls a query from access into excel now I am trying to make it where the user does not have to enter the SQL Server user ID and password every time they open the excel sheet because a lot of the users do not know the password.. I have copied my vba code below and any help would be greatly appreciated: (The bolded part is what is not working)

Sub BreadLinePerShift()
ActiveSheet.Range("L1") = Format(Now(), "mm/dd/yyyy hh:nn")


'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("\\bburockfsxxpp01\public\New Production Folder\WinSPC3.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Bread Line Per")


'Step 3repare the connection string
cnn.Open _
ConnectionString:="Provider=SQLOLEDB.1;" & _
"Data Source=BBUROCKWSP" & strServerName & ";Initial Catalog=winsp" & strDatabaseName & _
";User Id=wins" & strSQLUsername & ";Password=bbu.win" & strSQLPassword

'Step 4: Define the Parameters
With MyQueryDef
.Parameters("[Start Date]") = Range("B1").Value
.Parameters("[End Date]") = Range("B2").Value
End With

'Step 5: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 6: Clear previous contents
ActiveSheet.Range("A5:E10000").ClearContents

'Step 7: Copy the recordset to Excel
ActiveSheet.Range("A6").CopyFromRecordset MyRecordset


'Step 8: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(5, i).Value = MyRecordset.Fields(i - 1).Name


'Step 9: Sort the data in column
Range("A5:E60").Sort _
Key1:=Range("B5"), Header:=xlYes


'Step 10: Refresh entire workbook
ActiveWorkbook.RefreshAll


Next i
End Sub

 

[Darius]

If is for an Access database, could not be...

cnn.Open _
ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=BBUROCKWSP" & strServerName & ";Initial Catalog=winsp" & strDatabaseName & _
";User Id=wins" & strSQLUsername & ";Password=bbu.win" & strSQLPassword

 

[Dscalf1]

That did not work either I get the same error: "Run-time error '424': Object required"... Im not sure what I am doing wrong.

 

[Darius]

That did not work either I get the same error: "Run-time error '424': Object required"... Im not sure what I am doing wrong.

Object required is that the object does not exist, have you write...?
Set cnn = New ADODB.Connection
somewhere else...., the objects MUST exist, is not Access, even the recordset MUST be created.

Declaring is not enough, every object that is not part of the worksheet must be created

And destroyed afterwards, to free the memory.