Fetch data from Access to Excel

[vishu.royal]

How to get the data from the access database for the following scenario

User enters the date of joining of the employee in the A1 cell and When the user clicks the fetch button(Active X) the people who have the joining date similar should be populated in the excel sheet along with other employee details.
if not throw a message.

Please Help Me with the code...

Its urgent someone please help Me...

 

[tehNellie]

I think most people here are happy to give pointers or help trying to fix problems but "write my code for me" isn't something that many will do for free.

That being said, quick overview of retrieving data using DAO (set a reference to Microsoft Data Access objects n.nn in the Excel VBA Editor) and a parametised query.

Sub when the button is clicked()
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rs As DAO.Recordset
    
  Set db = OpenDatabase("the Path to the access database")
  Set qdf = db.QueryDefs("the query that returns your data")
    qdf.Parameters("startdate").value = Sheets(1).Cells(1, 1)
  
  Set rs = qdf.OpenRecordset
  
  If Not rs.EOF Then
  
    'do some stuff with the data that you have.

  Else

    'Send a message to the user telling them no-one was found.
  
  End If
  
End Sub

Whether DAO is the best method to do this is open to debate but however you go about it I'd personally not recommend sticking SQL in code outside of Access because it's completely invisible to whoever maintains the database whereas that Query "qryGetEmployeesBasedonStartDate" at least tells them that something likes to query that table.

 

[vbaInet]

Also, what you're asking for is an Excel solution, not an Access thing. This is the Access VBA section of the forum. We also have an Excel section too.

CopyFromRecordset method (i.e. no looping):

http://www.exceltip.com/st/Import_d...l_(DAO)_using_VBA_in_Microsoft_Excel/428.html

 

[Kiwiman]

Howzit

An alternative is to use MS QUERY from within excel to get the data. You can set parameters and link to an excel cell. See Data >> Import External Data >> New Database Query

The data can be automatically refreshed on open and or on change of parameter value. This requires no VBA.