mohan.r1980
New Member
- Joined
- Oct 27, 2010
- Messages
- 43
Hello Friends,
i have excel data and i have created a table in database file and add the data.
below code make the database file
Now i want to extract this data from database to excel.
i also want to do if i put SrNo in Input box then msgbox will pop up with Client Name.
Please suggest me
i have excel data and i have created a table in database file and add the data.
below code make the database file
Code:
Option Explicit
Sub DAOFromExcelToAccess()
' Please Select first "Microsoft DAO 3.6 Object Library fron Tool>>Reference
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim iSh As Worksheet
Dim db As Database, rs As Recordset
Dim i As Long
Set iSh = ThisWorkbook.Sheets("Sheet1")
' Set db = OpenDatabase("D:\Temp\Export To Access\My Data.accdb")
Set db = OpenDatabase("D:\Temp\Export To Access\My Data.mdb")
' open the database
Set rs = db.OpenRecordset("PENDINGTABLE", dbOpenTable)
For i = 1 To WorksheetFunction.CountA(ActiveSheet.Range("A1").EntireColumn) - 1
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("SRNO") = iSh.Range("A" & i + 1).Value
.Fields("BCM_CLIENT_ID") = Range("B" & i + 1).Value
.Fields("STATUS_NAME") = Range("C" & i + 1).Value
.Fields("FIRST_HOLDER_NAME") = Range("D" & i + 1).Value
.Fields("PIN_CODE") = Range("E" & i + 1).Value
' add more fields if necessary...
.Update ' stores the new record
End With
Next i
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Now i want to extract this data from database to excel.
i also want to do if i put SrNo in Input box then msgbox will pop up with Client Name.
Please suggest me