By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to import data from Excel into Access RecordSet using VBA

P: 11
I'm working on a table of user accounts for an application and want to be able to import a list of accounts and append them into the table. I have a macro that uses the TransferSpreadsheet function that works fine, but I want to read the accounts from the spreadsheet into a recordset first so I can do some error and duplicate checking first before adding them to the database. I have an Excel import template where the headers match the field names in the table. I'm just not sure how to open the file and read it into a recordset. I should be able to add the error checking myself. Also, the file will vary in length (could be 10 users, could be 100). Everything is 2007 version. Can anyone help me on this?

Let's just use the following and I'll adjust:
File to import: c:\import.xlsx
Fields: [Name]|[email]|[LoginID]
Feb 3 '12 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,597
The following Code will Import the File Import.xls in the C:\Test Folder into a Table named 'User Accounts', change the [Name] Field to [UName] since Name is a Reserved Word, Create a Recordset based on the Imported Table (User Accounts), then Loop through the Recordset printing all Field Values for each Record.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "User Accounts", "C:\Test\Import.xls"
  2.  
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb
  7. Set rst = MyDB.OpenRecordset("User Accounts", dbOpenDynaset)
  8.  
  9. With rst
  10.   Do While Not .EOF
  11.     'Print the User's Name, EMail Address, and Login ID
  12.      Debug.Print ![UName], ![email], ![LoginID]
  13.       .MoveNext
  14.   Loop
  15. End With
  16.  
  17. rst.Close
  18. Set rst = Nothing
Feb 4 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
We are not allowed simply to provide solutions for project requests (ADezii ;-)), but I'm happy to point you in the general direction for you to look at in more detail yourself. If, after that, you would like to post a proper question asking for help with a detail of your project you're having trouble with then that would be fine.

Typically, this is done by importing the data into a special 'buffer' table where you can examine the data and check it for correctness. From this point you can decide whether to import all the data if there are no errors or, if there are errors, to import the error-free lines or to fail the whole batch. Importing, from this point, consists of appending the selected data from this table into your main one.
Feb 4 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.