Importing excel data into Access using macro

[mbbx5va2]

Hi

If I had a A1:C3 table in an excel worksheet called "Client details" with column headings "ClientID" = A1, "Firstname" = B1, "Lastname" = C3

Then if I filled in the table with data i.e in cells (A2:C3) and then wanted to create a button where if I clicked then it would transfer the data in the table including the column headings into an Access database, what code would I assign to the button?

Regards

[Doug Robbins]

Use the following code, replacing "R:\Data\Databases\Old\TASQ10 - 2.accdb" with the path\filename of your database:

Dim daoDB As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim i As Long
Set daoDB = DBEngine.OpenDatabase("R:\Data\Databases\Old\TASQ10 - 2.accdb")
Set tbl = daoDB.CreateTableDef("tblClientDetails")
Set fld = tbl.CreateField("ClientID", dbText, 255)
tbl.Fields.Append fld
Set fld = tbl.CreateField("FirstName", dbText, 255)
tbl.Fields.Append fld
Set fld = tbl.CreateField("LastName", dbText, 255)
tbl.Fields.Append fld
daoDB.TableDefs.Append tbl
daoDB.TableDefs.Refresh
Set rs = daoDB.OpenRecordset("tblClientDetails")
With ActiveSheet.Range("A1")
For i = 1 To .CurrentRegion.Rows.Count - 1
rs.AddNew
rs("ClientID").Value = .Offset(i, 0)
rs("FirstName").Value = .Offset(i, 1)
rs("LastName").Value = .Offset(i, 2)
rs.Update
Next i
End With

You will need to set a reference to the Microsoft Office ##.0 Access database engine Object Library.

[mbbx5va2]

Many thanks for your reply. I'll just try that now.

[mbbx5va2]

Sorry for the delay. Can't quite seem to get it working. Are you using an early binding method here?

I can set the reference fine. But once I run the code it doesn't work. It highlights the first line of code.

[Doug Robbins]

What version of Office are you using?

The reference that I have set here is to the Microsoft Office 14.0 Access database engine Object Library.

In addition, there are references to :

Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library

[mbbx5va2]

Right I've referenced all 5 of those. I'm using Office 2010. I think the problem could be setting the correct path to my database. I've saved the Access file where I want the data to be extracted to. It is called: Extraction.accdb

I put in this line as I've saved the Extraction file to my desktop:

Set daoDB = DBEngine.OpenDatabase("C:\users\user\Desktop\Extraction")

Thanks

[Doug Robbins]

I would think that you would also need the file extension and of course unless your user name is "user", then you would need to replace that in the path to the database with the actual user name.