VBA running Access Query from Excel

Question

I am trying to run a VBA script that will Run and Export an Access query. I'm able to get to the step in code where I run the query, however, I need to connect to DB2 to run this query in Access and I don't know how to implement into my code to enter the username and password.

Sub RunQuery()
Dim A As Object
Application.DisplayAlerts = False
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("J:\user\filename.mdb")
A.DoCmd.OpenQuery "QueryName"
A.DoCmd.ConnectString
Application.DisplayAlerts = True

End Sub

The code just stalls at the line:

A.DoCmd.OpenQuery "QueryName"

And If I open my Database from here with my query it is just waiting for my username and password. I'll try and attach a picture of the prompt.

Any help would be greatly appreciated!!

Thank you very much

You'll need to authenticate with the DB2 database. It might be a better solution to use ADO and just connect directly to the DB2 database. See this to get started: stackoverflow.com/questions/7171546/excel-and-db2-connectivity — Ryan Wildry
– Ryan Wildry, Commented Aug 29, 2016 at 23:08

Rdster · Accepted Answer · 2017-08-29 07:49:16Z

2

As Ryan said using ADO will be a better option, see below

Public Sub RunQuery()
    Dim A As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set A = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=J:\user\filename.mdb"
    strSql = "SELECT * FROM table"
    A.Open strConnection
    Set rs = A.Execute(strSql)
    arr = rs.GetRows

    'now the array arr has the data queried

    rs.Close
    Set rs = Nothing
    A.Close
    Set A = Nothing
End Sub

edited Aug 29, 2017 at 7:49

Rdster

1,8722 gold badges19 silver badges30 bronze badges

answered Aug 30, 2016 at 6:45

Arun Thomas

8651 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA running Access Query from Excel

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related