calling access data from excel

[Guest]

I have an access db with columns for example "name", "surname", "date of
birth" and "place of birth". And I have an empty worksheet in excel with the
same columns:

column1 = id number
column2 = name
column3 = surname
column4 = date of birth
column5 = place of birth

Now, is it possible to call the data by some type of "id" or something that
for example when i enter "3" in excel sheet column1, it will automatically
fill the other columns by taking data from my access db (person numbered 3 in
access db) ?

 

[Bob Phillips]

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts WHERE Person_Id = " & Range("A1").Value
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
Sheet1.Range("A6").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub



You will need to adapt the database name and SQL and ranges to suit.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)