Home Connect access query to excel with dynamic input parameter
Reply: 1

Connect access query to excel with dynamic input parameter

Mourst
1#
Mourst Published in 2017-12-07 16:46:53Z

I've got some queries I have connected to an excel sheet that I refresh in order to get the latest data.

Is it possible to make excel "ask" for input parameters for one column lets say?

I am looking to get data by company so wondering if it's possible to do it as in access (type [company]:) in the field criteria

If I save the query like that in access it won't let me connect it to excel

Thanks

Wookies-Will-Code
2#
Wookies-Will-Code Reply to 2017-12-12 16:22:05Z

In excel you can certainly ask for a string using a textbox or better yet if you now the field names in your data base you can make a list or listbox. There are several approaches, you could connect to the database and read the field names and then put them in a list. Whatever you choose you will be building the connection and query in excel, so that data will be populated into excel ....

This is a two part question: 1) how to capture the field name for the query (several ways above), if you state what you are comfortable with in excel we can help you from there 2) connection and query to access to get the data

The guts of part 2 would be similar to: 'pass the field name into your function

Private Sub loadDataTables(fieldSTR as String)
Dim conn As Object ' connection
Dim rs As Object 'record set
Dim strConnection As String
Dim sqlSTR As String

'set up db connection
Set conn = CreateObject("ADODB.Connection")
'provide the path
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\***PUT YOUR DB PATH HERE*****\DBname.accdb"

'open the DB
On Error GoTo ErrHandler1:
    conn.Open strConnection

'set up your query, you could also maybe choose a table name and pass it in to the function
sqlSTR = "Select " & fieldSTR & " FROM ***put table  name here***"

Set rs = conn.Execute(sqlSTR) 'here are your records

'put them somewhere, obviously you will want to set up the workbook, sheet and range
ThisWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

JumpOut:

Exit Sub

ErrHandler1:
 MsgBox ("The database file can not be accessed.  Please report this behavior.")
 Resume JumpOut:
End Sub

For help building the DB query to suit your objective

https://msdn.microsoft.com/en-us/library/office/ff821148.aspx

Cheers - WWC

Here is a Screenshot of grabbing a cell and converting to a string:

Option Explicit

Private Sub getCompany()

Dim thisWB As Workbook
Dim dataSheet As Worksheet
Dim dataCell As String


Set thisWB = ThisWorkbook
Set dataSheet = thisWB.Worksheets("Sheet1") 'adjust sheet name accordingly

dataCell = CStr(dataSheet.Cells(3, 3).Value) 'C3 adjust accordingly
'Cstr converts to a string whatever is in data cell

'Because fieldSTR is a string you will not have to represent it in quotes

'partial SQL query: WHERE COMPANY_1.COMPANY_NAME = dataCell

MsgBox ("You have selected to filter data based on Company: " & dataCell)

End Sub

-WWC

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.353094 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO