Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Bulk Add Blank Records in Access 2013

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
REIUSA
Asked:
REIUSA
  • 4
  • 2
  • 2
  • +3
2 Solutions
 
chaauCommented:
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
 
IrogSintaCommented:
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
 
Jeffrey CoachmanCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GrahamMandenoCommented:
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
 
Helen FeddemaCommented:
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
 
REIUSAAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
Helen FeddemaCommented:
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
 
Jeffrey CoachmanCommented:
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
 
REIUSAAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now