getting data from Access at specific cells of the worksheet

[flowergirl]

This code retrieves data all on the leftmost corner of the worksheet. How can I modify data so that Employee_Name is in B2, Employee Salary is in B3 and the headers are not there. I could not attach the Access db because of security restrictions. It's structure is as:

Employee ID Employee_Name Employee Salary
3 Sam 800
4 Mary 500
5 Bill 600

Thanks for your help.

Sub AccessToExcel()
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet2")
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\Test_Access_db.accdb"
dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName _
& ";Persist Security Info=False;"

strSQL = "SELECT Test_Access_db.* from Test_Access_db where Employee_name = 'Bill'"

DestinationSheet.Cells.Clear

With dbConnection
.Open
.Cursorlocation = adUseClient
End With

With dbRecordset
.Open strSQL, dbConnection
Set .ActiveConnection = Nothing
End With

DestinationSheet.Range("A2").CopyFromRecordset dbRecordset
DestinationSheet.Range("A1:C1").Value = Array("Employee ID", "Employee Name", "Employee Salary")

dbRecordset.Close
dbConnection.Close

Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing
End Sub

[Bob Phillips]

Untested



Option Explicit

Sub AccessToExcel()
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
Dim mtxData As Variant

Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet2")

dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\Test_Access_db.accdb"
dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName _
& ";Persist Security Info=False;"

strSQL = "SELECT Test_Access_db.* from Test_Access_db where Employee_name = 'Bill'"

DestinationSheet.Cells.Clear

With dbConnection
.Open
.Cursorlocation = adUseClient
End With

With dbRecordset
.Open strSQL, dbConnection
Set .ActiveConnection = Nothing
End With

mtxData = dbRecordset.Getrows
DestinationSheet.Range("A1:C1").Value = Array("Employee ID", "Employee Name", "Employee Salary")
DestinationSheet.Range("A2").Resize(UBound(mtxData, 1) - UBound(mtxData, 1) + 1, UBound(mtxData, 2) - UBound(mtxData, 2) + 1) = mtxData

dbRecordset.Close
dbConnection.Close

Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing
End Sub

[flowergirl]

Thanks a lot

The matter has been resolved.