Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access create new record
I'm trying to edit this code to open and access database (MoveRequest.mdb) and create a new record in table (MRequests) and add the information from these cells to a new record and close the database. Can this be done? Sub DAOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\MR\MoveRequest.mdb") ' open the database Set rs = db.OpenRecordset("MoveRequests", dbOpenTable) ' get all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub Cells to field in database record: B5 to Field 1 F4 to Field 2 B4 to Field 3 F5 to Field 4 A8 to Field 5 B8 to Field 6 C8 to Field 7 D8 to Field 8 E8 to Field 9 A20 to Field 10 B22 to Field 11 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I view a specific record in Access from an Excel link | Excel Discussion (Misc queries) | |||
Update current record in Access using Excel | Excel Programming | |||
Find Record in Access Using Excel | Excel Programming | |||
New record in table in Access from VBA in Excel | Excel Programming | |||
Modifying an Access record in Excel | Excel Programming |