SQL Server Username and Password

D

Dscalf1

#1
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 3:prepare 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
 
Last edited by a moderator:
Elsmar Forum Sponsor

Darius

Quite Involved in Discussions
#2
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
 
D

Dscalf1

#3
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

Quite Involved in Discussions
#4
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.
 
Thread starter Similar threads Forum Replies Date
C Creating a Multiple Table SQL (Structured Query Language) Query in Minitab Using Minitab Software 10
ScottK PRC (China) Safety Quality License (SQL) for boilers and pressure vessels Various Other Specifications, Standards, and related Requirements 3
R Question about MINITAB's SQL Syntax Using Minitab Software 2
V CAR NCMR Software - SQL based, just for NCMR's and CARs Quality Tools, Improvement and Analysis 2
was named killer CLOUD BASED QUALITY DOCUMENTATION vs. SERVER BASED Document Control Systems, Procedures, Forms and Templates 5
S Moving from client-server to cloud-based, is that a new submission? Medical Information Technology, Medical Software and Health Informatics 3
M Trouble with Europa website - Internal Server Error (March 2019) EU Medical Device Regulations 5
Marc Server RAM Failure - 7 Dec 2018 Forum News and General Information 3
D Nonconformance on document control - Unapproved document on production server Document Control Systems, Procedures, Forms and Templates 4
J Dummy SMTP server for email testing Software Quality Assurance 3
H Requirements for Data Storage as to Geographic Server Location Medical Information Technology, Medical Software and Health Informatics 11
P Blackberry Server Down - October 2011 Coffee Break and Water Cooler Discussions 4
V Do we need to Validate a Server/Back-End System? Qualification and Validation (including 21 CFR Part 11) 2
kisxena Transferring a validated software platform to a virtual server Software Quality Assurance 8
B Controlled Copies of a Quality Manual in an electronic media (file/server) Quality Management System (QMS) Manuals 9
J What Can You Do with Active Server Pages? Quality Tools, Improvement and Analysis 0
C User Acceptance Testing best practice - conducted on production server or test server Software Quality Assurance 0
ccochran Synthis software review - Process Modeler and Process Server Book, Video, Blog and Web Site Reviews and Recommendations 1
L Forms in MS Word - Trying to put forms on a server and prohibit modifications Document Control Systems, Procedures, Forms and Templates 84
W Is it permissable to maintain "controlled quality documents" on the EDP server only? Document Control Systems, Procedures, Forms and Templates 1
S ISO 9001 software package that will work with Outlook on an Exchange server Quality Assurance and Compliance Software Tools and Solutions 2
A Document Matrix - Can the Server Directory Listing(s) be used? Document Control Systems, Procedures, Forms and Templates 13
S Data/Drawing Control - All documents controlled on a head-office server? Document Control Systems, Procedures, Forms and Templates 1

Similar threads

Top Bottom