Run the query from excel

[imtmub]

Hi,
I have a access database for simple accounts. I want to run the query from my excel sheet and get the result to that respective cells.
My database contains accounts name, type, credit limit, account receivable and account payable.
I can run the query to get any account details from access.

But i need that my manager don;t wana enter into database and run qry and he need get the details in excel sheet.
I had created the excel sheet. that contains account type, credit limit,account receivabale and payable. Additionally i have the dropdown box to select the account name. Actually now i linked with another sheet that gives the data to dropdown. But i need to link with database table. Once the data is selected from dropdown the details of account type ,credit limit, account receivable and payable should change.

Imthiyaz

[NeoPa]

'These constants are set up for MY usage
'so won't work directly without modification
Private Const conQryDate As String = _
                  "SELECT [Date] " & _
                  "FROM [tblDate] " & _
                  "WHERE ([Ref]='tblReport')"
Private Const conQryDef As String = "ODBC;" & _
                                    "DSN=MS Access Database;" & _
                                    "DBQ=C:\MyDatabase.Mdb;" & _
                                    "DefaultDir=C:\;" & _
                                    "DriverId=25;" & _
                                    "FIL=MS Access;" & _
                                    "MaxBufferSize=2048;" & _
                                    "PageTimeout=5;"
 
'GetDataFromAccess regets the data in the current sheet
'from conQryName in database conDBDir\conDBName.
Private Sub GetDataFromAccess(ranDest As Range, strSQL As String)
    Dim strName As String
    Dim namQuery As Name
    Dim shtOrg As Worksheet
 
    If ranDest.Worksheet.Name <> ActiveSheet.Name Then
        Set shtOrg = ActiveSheet
        Call ranDest.Worksheet.Select
    End If
    strName = "QueryName"
    With ActiveSheet.QueryTables.Add(Connection:=strWork, Destination:=ranDest)
        .CommandText = strSQL
        .Name = strName
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        Call .Refresh(BackgroundQuery:=False)
        Call .Delete
    End With
    For Each namQuery In ActiveWorkbook.Names
        If InStr(1, namQuery.Name, strName) > 0 Then Call namQuery.Delete
    Next namQuery
    If Not shtOrg Is Nothing Then Call shtOrg.Select
End Sub
'...In the right place in your code
    Call GetDataFromAccess(Range("A1"), conQryDate)

Clearly, this will not work without modification as a lot of the code refers to data and code which you don't have available. I can explain anything you don't understand individually (Don't come back asking me to 'Explain it all').