CODEWITHSUNDEEP
sqlexcelvbams-accessoledbconnection
Ning H
Ning H

Reputation: 1

How to pass Username and Password to Access DB from Excel?

I wrote my first ADO/SQL data extract codes based on book instruction.

The database has user name and password, but here there is no UserName and PassWord input.

Sub GetAccessData_With_SQL()    
    'step 1 Declare your variables
    Dim MyConnect As String
    Dim MyRS As ADODB.Recordset
    Dim MySQL As String

    'step 2 Declare your connnection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source = C:\Thorsys\TAS.accdb"

    'step 3 Build your SQL statement
    MySQL = "SELECT productID, headID, list FROM dbo_tblSTproduct"

    'step 4 Instantiate and specify your recordset
    Set MyRS = New ADODB.Recordset
    MyRS.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

    'step 5 Copy the recordset to Excel
    Sheets("ADO and SQL").Select
    ActiveSheet.Range("A2").CopyFromRecordset MyRS

    'step 6 Add column labels
    With ActiveSheet.Range("A1:E1")
        .Value = Array("productID", "headID", "list")
        .EntireColumn.AutoFit
    End With   
End Sub

Upvotes: 0

Views: 1090

Answers (1)

luna80
luna80

Reputation: 153

you have to put the username and the password in connection string...an example:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Thorsys\TAS.accdb;User ID=myUsername;Password=myPassword;

Upvotes: 1

Related Questions