Transfer data from Excel to Access.

Asked By Heather Finn 20 points N/A Posted on - 04/03/2012

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

• Status: Open
• Question Views: 1370
• Answer Count: 2
• Vote Up 1 Vote Down

• Answer Accepted: Yes
• Question Category: Microsoft Office

Best Answer by Gideon Yasti

Go To Solution

Best Answer

Best Answer

Answered By Gideon Yasti 15 points N/A #115918

Transfer data from Excel to Access.

Hello,

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.

Thanks.

About Gideon Yasti

Questions
3

Answers
316

Best Answers
69

• Vote Up 0 Vote Down

• Posted on - 04/04/2012
• Question Category: Microsoft Office

Answered By sjamora 0 points N/A #115920

Transfer data from Excel to Access.

 

Hi,

 

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.

 

cn.Open

 

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–>

 

rs.MoveNext

 

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.

 

 

Good Luck.

 

~Sjamora

About sjamora

Questions
0

Answers
2

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/16/2012
• Question Category: Microsoft Office