Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calling access data from excel
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) ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calling access data from excel
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) "batuhan" wrote in message ... 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) ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting data in Excel permanently to Access | Links and Linking in Excel | |||
Data from Excel to Access Table | Excel Discussion (Misc queries) | |||
How to import data from a password protected Access DB into Excel. | Excel Discussion (Misc queries) | |||
New Excel data does not appear in linked Access table | Excel Discussion (Misc queries) | |||
MS Excel data reading in MS Access | Excel Discussion (Misc queries) |