How to import data from Excel into Access RecordSet using VBA

[Smecker]

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 TransferSpreads heet 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]

[ADezii]

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.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "User Accounts", "C:\Test\Import.xls"

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("User Accounts", dbOpenDynaset)

With rst
  Do While Not .EOF
    'Print the User's Name, EMail Address, and Login ID
     Debug.Print ![UName], ![EMail], ![LoginID]
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[NeoPa]

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.