updating Access table with Excel data

[newbie]

Hello,
I'm new to office soln dev so bear with me. How do I
take data on an Excel sheet and update an access table
with it? I have an empty access table and I need to get
info from Excel into it. How do I go about this? Some
bulk operation would be the best because I can imagine
that going line by line would take too long. Thanks a lot
in advance for your help!

 

[Tom Ogilvy]

You can start here - this should give you some ideas:

http://www.erlandsendata.no/english/vba/adodao/

Regards,
Tom Ogilvy

 

[Jose Rojas]

Hi there try this:
Sub CopyDatatoAccess
Dim Range1 As Range
Dim Array1 As Variant
Dim x As Variant
Dim Db1 As Dao.Database
Dim Rs1 As Dao.Recordset


'First, open the database. Chane the Path Name and the Database Name for
yours
Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\sampdata.mdb")

'Then, open the recordset. Again Change the name of the table for yours
Set Rs1 = Db1.OpenRecordset("distributors", dbOpenDynaset)

'Then, determine the size of the worksheet range.
Set Range1 = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0)
Set Range1 = Range1.Resize(Range1.Rows.Count - 1, Range1.Columns.Count)
Range1.Select

'Read the worksheet range into an array.
Array1 = Range1.Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For x = 1 To UBound(Array1, 1)
With Rs1
.AddNew
.Fields("Field1") = Array1(x, 1)
.Fields("Field2") = Array1(x, 2)
.Fields("Field3") = Array1(x, 3)
.Fields("Field4") = Array1(x, 4)
.Fields("Field5") = Array1(x, 5)
.Fields("Field6") = Array1(x, 6)
.Fields("Field7") = Array1(x, 7)
.Fields("Field8") = Array1(x, 8)
.Fields("Field9") = Array1(x, 9)
.Update
End With
Next
'Keep adding the fields depending on how many Colunms you may have in
excel
'Close the database.
Db1.Close

End Sub