Solved

Bulk Add Blank Records in Access 2013

Posted on 2015-02-01
11
287 Views
Last Modified: 2015-02-13
Hello,
Is there a easy way that I can add several new records in a access table? Or several new records with a generic letter in the first field?

What I have to do is copy several cells of data from a excel spread sheet into specific fields in a table. Usually about 700 cell at a time. What I have been doing is simply hitting the down arrow key and typing a "a" so it will create a blank new record so I have the room to paste the data as needed.

What I am trying to do is see if there is a easier way to get those blanks records so I can paste the data.

The excel sheet I am using is not in the same order as the table so I have to take a few columns of data from excel and paste it in a different order in the Access spreadsheet.
0
Comment
Question by:REIUSA
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40583191
I think in this case it will be better to use External Data->Import Excel Spreadsheet. The wizard will allow you to select columns in the source and destination tables and works really fast. You are also able to save the import steps for future use
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40583193
What do you mean they're not in the same order?  Are you referring to the order of the columns.  If so, you can just copy the and paste all the columns at one time and then go into the table's design and reorder the columns.

Ron
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40583326
Yes, this can be frustrating.
Depending on how the data is structured in both applications, ...copy/paste of specific data in Excel can be very tricky to paste into Access as new records.
First I will say that a system of adding blank rows in Access, then copy/paste data from Excel is not the most efficient way to update/add data.
It is OK as a one-shot deal, ...but not for continuous data transfer.

But for now, you can use code like this to add any number of "blank" rows to an Access table:
Dim i As Integer
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourTableName")
    'Add the number of blank rows you need
    For i = 1 To 700
      rst.AddNew
      rst.Update
    Next i
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

Open in new window


Now, ...If this will be a common occurrence then you need to look into moving the data entirely to Access.

JeffCoachman
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 40583422
You must bear in mind that a relational database is a very different animal from a spreadsheet.

First, there is no concept of a "blank row" in a relational database.

Second, there is no inherent concept of "order"  in a table.  Order can be imposed by an ORDER BY clause in a query, or by the user specifying a sort order (really just a query) in table view.

I suggest you link (NOT import) the Excel spreadsheet as a foreign table in your Access database.  Then you can write an append (INSERT INTO) query to select the required rows from your Excel spreadsheet and add them to your Access table.

Best wishes,
Graham Mandeno [Access MVP 1996-2015]
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 40584167
Generally, when  you need to add rows to be filled in later, there are a few fields that will need values.  Here is some typical code for this purpose (it adds a set of records for weekdays, with a starting date argument):

Public Sub AddSet(dte1stMonday As Date)
'Created by Helen Feddema 11-Feb-2013
'Last modified by Helen Feddema 11-Feb-2013

On Error GoTo ErrorHandler

   Dim rstStudents As DAO.Recordset
   Dim rstAttendance As DAO.Recordset
   Dim lngStudentID As Long
   Dim dteNext As Date
   
   Set rstStudents = CurrentDb.OpenRecordset("tblStudents")
   Set rstAttendance = CurrentDb.OpenRecordset("tblAttendance")
      
   Do While Not rstStudents.EOF
      lngStudentID = rstStudents![StudentID]
      Debug.Print "Adding records for Student ID " & lngStudentID
      
      'Create set of weekly records for this student
      With rstAttendance
         'Add Monday date
         .AddNew
         Debug.Print "Adding " & CStr(dte1stMonday) & " records"
         ![StudentID] = lngStudentID
         ![AttendanceDate] = dte1stMonday
         dteNext = DateAdd("d", 1, dte1stMonday)
         .Update
      
         'Add Tuesday date
         .AddNew
         Debug.Print "Adding " & CStr(dteNext) & " records"
         ![StudentID] = lngStudentID
         ![AttendanceDate] = dteNext
         dteNext = DateAdd("d", 2, dte1stMonday)
         .Update
         
         'Add Wednesday date
         .AddNew
         Debug.Print "Adding " & CStr(dteNext) & " records"
         ![StudentID] = lngStudentID
         ![AttendanceDate] = dteNext
         dteNext = DateAdd("d", 3, dte1stMonday)
         .Update
         
         'Add Thursday date
         .AddNew
         Debug.Print "Adding " & CStr(dteNext) & " records"
         ![StudentID] = lngStudentID
         ![AttendanceDate] = dteNext
         dteNext = DateAdd("d", 4, dte1stMonday)
         .Update
         
         'Add Friday date
         .AddNew
         Debug.Print "Adding " & CStr(dteNext) & " records"
         ![StudentID] = lngStudentID
         ![AttendanceDate] = dteNext
         .Update
      End With
      
      rstStudents.MoveNext
   Loop
   
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in AddSet procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

This code is from my Access Archon article on Creating a Set of Records:

http://www.helenfeddema.com/Files/accarch220.zip
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:REIUSA
ID: 40597576
I'm not real familiar with how to apply that code to a table, can someone explain what steps I would need to run it?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 40597613
Create a form
Add a button to the form
On the Click Event of the button, ...insert the code I posted
Dim i As Integer
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourTableName")
    'Add the number of blank rows you need
    For i = 1 To 700
      rst.AddNew
      rst.Update
    Next i
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

Open in new window


You will have to read the code and adjust your own values where needed.

give it a good go on your own, and post back if you have any questions

JeffCoachman
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40598621
Jeff's code will work fine if you just need blank rows (presumably with an AutoNumber field, so technically they would not be totally blank).  If you need values in one or more other fields, see my code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40599296
Yes,

It is always best to have two different ways of doing things.
My code is "Brute Force" where as Helen's appears to be more elegant....

As any expert here will state, ...adding 'blank' records to an Access table is frowned upon because of the inconsistencies it can create if anything goes wrong, ...for example:
The code fails
Then code is triggered at the wrong time.
You insert the wrong number of blank rows
You count records at some point, and the blanks are inadvertently included
...etc
...In other words, this is not meant to be a "permanent" solution.

Consider in the future:
Linking the Excel data to Access, or Moving the main data out of Excel and into Access.

JeffCoachman
0
 

Author Comment

by:REIUSA
ID: 40600500
Great, thanks. I will try this as soon as I can.

Jeff,
When you link data from Excel does it depend on the excel spread sheet in another location? If I delete the source spread sheet will it cause a error in Access?

What I am using it for is a inventory database so I can keep track of all of the items in inventory, when they were bought, when they were sold, our cost etc. I used access because it is a lot more robust than excel and can handle larger numbers of rows.

The particular thing I am trying to do now is take a excel sheet that has a list of items that were purchased so I need to input those into the access DB. I can take a column of data and then paste it into Access, that is why I am adding new rows with a "a" or whatever so I can then paste the cells of data. I am sure there is a much better way to do this, I will start looking into linking or better ways to import the data.

If anyone has a better suggestion for something to use as a inventory DB please let me know. It is important to be able to import data from a cvs file and update a record based on a item number.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40600600
Inventory systems can end up being very complex.
Without us knowing the full scope of this system, it is difficult to make targeted suggestions.

My thoughts now are to suggest moving the entire system to Access.

As I said ..."adding blank rows" should only be a temporary system.
MS Access had an "Inventory Tracking" database template.
It also had the Northwind sample database (use the 2003 version only)
You can do a web search for either...

JeffCoachman
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now