No of visitors who read this post: 515
Type: Question
Author: Heather Finn
Your rating: None Average: 5 (1 vote)

Is there a faster way to add data from Excel 2010 to an Access 2010 database using VBA?

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

# Comment Id: 52451


You want to import data from excel to access. That’s why you need do import data from excel to access first and then normalize the data by using Table Analyzer Wizard. Finally you should connect access data from excel spreadsheet.

At first import all data from excel to access with attentively. Then drag selected columns to a new table for create relationship. Then you should use button commands to rename a table then you should add a primary key then you should make an existing column a primary key. After complete previous process let you should connect access data.


# Comment Id: 54385

If you are trying to do some VBA codes for transferring Excel data to Access, then here is a VBA script that uses RecordSet in Importing XLS data, this might be able to help you out:
  • First, you have to create a new form in MS Access, which could be found on "Create" tab, then click "Form Design" button;
  • Then, create two objects (a text box and a button) in the form that you just created;
  • Then, click the button you have created;
  • You are now redirected to the VBA editor where you would be able to insert lines of codes.
  • Always remember to place the VBA code inside this function call button property "Click".

Private Sub Command8_Click()

<--- lines of codes here-->
End Sub
For this question, insert these lines of codes:
Dim rs as New ADODB.Recordset
Dim cn as New ADODB.Connection
Dim cmd as New ADODB.Command
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "Data Source=" TextBox1.Text; " & " Extended Properties=Excel 8.0;" //Note: TextBox1.Text should contain the path of where the Excel File you want to Import is located.
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From [Sheet1$]" //Note: You can do filtering on this query if needed. Sheet1$ is the first worksheet on your data source spreadsheet.
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open cmd
While Not rs.EOF
<--- do some stuff in here-->
Then, try running the form you have made. Done. You should now be able to add your Excel data into Access using VBA codes.
Hope this helps.
external data-import excel spreadsheet
Good Luck.