Retrieve access data into excel

I have a VBA code that retrieves data from an Access database and populate the excel spreadsheet. However, I am only to retrieve one field at a time. I am sure there is a more efficient way of doing this. I would like to retrieve multiple fields from the database and populate several excel cells with appropriate data. The data expands across two tables. Any ideas would be appreciated. Here is what I have that populates one cell.

Const stDB As String = "\effort.mdb"

Sub GetData()
Dim cnt As ADODB.Connection
Dim stCon As String, strDB As String, stSQL As String
Dim cmd As ADODB.Command, rs As ADODB.Recordset
Dim Parm1 As ADODB.Parameter, Parm2 As ADODB.Parameter
Dim ID As Long
Dim section As ADODB.Parameter
ID = Range("tuid").Value
Range("c5").Value = ""
'database path - currently same as this workbook
strDB = ThisWorkbook.Path & "\EFFORT.mdb"

stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

stSQL = "select Name from Physicians where Code = " & ID & " "

'set connection variable
Set cnt = New ADODB.Connection
Set cmd = New ADODB.Command

'open connection to Access db and run the SQL
cnt.Open stCon
If cmd.CommandText = "" Then
cmd.ActiveConnection = stCon
With cmd
.CommandText = stSQL
.CommandType = adCmdText
.CommandTimeout = 15
End With
End If

'// here's a simple way to specify the date params
' cmd.Parameters(0).Value = [A1].Text ' i.e. [Start Date:]
'cmd.Parameters(1).Value = [B1].Text ' i.e. [End Date:]

Set rs = cmd.Execute()
[c5].CopyFromRecordset rs

'close connection
cnt.Close
'release object from memory
Set cnt = Nothing
End Sub


Thanks.

Hi

Have you tried putting the extra fields you require into the SQL string?

stSQL = "select Name from Physicians where Code = " & ID & " "
to (as an example)
stSQL = "select Name, Address, Phone from Physicians where Code = " & ID & " "

rylo

Yes. I have tried including all the fields, but get an error Method "Execute" of Command Object failed on the following line.. 'Set rs = cmd.Execute()".

I will need each field to go in a designated cell.

I tried to use your code and it seems to run fine.

Microsoft says the fast 'CopyFromRecordset' instruction will fail if the recordset
contains an OLE object field or array data such as hierarchical recordsets.

For more info: http://support.microsoft.com/kb/246335

Regards,
Antonio

ok.. i got it to work mostly. The error pops up when I use a field in the select statement that has "NULL" for some of its data.

Thanks