473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How to import data from Excel into Access RecordSet using VBA

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
2 10748
ADezii
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
20
by: TC | last post by:
I need an automated procedure to copy data from an Access table to a SQL Server table. Speed is important. What is the recommended technique? I can export the data from Access, copy it via FTP,...
2
by: connormurphy | last post by:
How do I import data from Access into an Oracle database? I'm looking at hetergeneous services but I was hoping to simply set up an ODBC connection and pull accross the data easily. But I cannot...
1
by: qasimkhans | last post by:
hi, i m new in VB 6. i want to print all data which retrieved from database.i.e i have 3 required records now i want to print these records. i am using DataGrid. is there any option in DataGrid...
1
by: shwetagupta | last post by:
Hi I am Trying to insert data in M S Access through ASP, but it is neither showing any error nor updating the database. Please tell me ,where i am making the mistake code: <html> <% set...
0
by: bbeshlian | last post by:
I was given the code to intially develop an interface for a web based system I will eventually administer. I tested the code and I can write my SQL statements to retrieve and display the data I...
0
by: paulquinlan100 | last post by:
Hi Could someone point me in the right direction. Im trying to update an Access DB using the code below, it seems to all run correctly and I dont get any error messages, however it doesnt...
5
by: Dan2kx | last post by:
Good evening folks, i have yet another question for the experts, i need to be able to import a list of dates and ID numbers from a text file i suspect i need to format my text file as such ...
0
by: nilanjangm | last post by:
I am using MS Access 2007. I have one of my tables where I store files as "Attachment" datatype against each employee record. Till this point everything is okay. Now, what I want is to display...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.