How to Save Filtered Data to Another Table in Access

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Save Filtered Data to Another Table in Access
Save query data from one table to another using VBA. (Binary code high-tech abstract texture series image by artcalin from Fotolia.com)

Knowing how to save information from one table to another table can save you time when transferring data in a database. Microsoft Office Access is a relational database management system included in the Microsoft Office suite. Visual Basic for Applications (VBA) is a computer programming language developed by Microsoft. VBA can be used in Access to automate processes otherwise performed manually. In a few steps you can write VBA code to query data from one table and save it in another table.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Start Microsoft Office Access 2007 and select "Blank Database," then click "Create." Click "Database Tools," then select "Visual Basic."

  2. 2

    Click the "Insert" field and select "Module."

    Copy and paste the following code into your module:

    Private Sub createNewTable()

    Dim rst As Recordset

    Dim dB As Database

    Dim SQLstr As String

    SQLstr = "CREATE TABLE CustomerInfo (FirstName TEXT(25), LastName TEXT(25));"

    DoCmd.RunSQL (SQLstr)

    SQLstr = "INSERT INTO CustomerInfo ([FirstName], [LastName] ) "

    SQLstr = SQLstr & "VALUES ('John', 'Williams');"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (SQLstr)

    SQLstr = "INSERT INTO CustomerInfo ([FirstName], [LastName] ) "

    SQLstr = SQLstr & "VALUES ('Charles', 'Gonzalez');"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (SQLstr)

    SQLstr = "SELECT CustomerInfo.FirstName, "

    SQLstr = SQLstr & "CustomerInfo.LastName INTO CharlesInfo "

    SQLstr = SQLstr & "FROM CustomerInfo "

    SQLstr = SQLstr & "WHERE (((CustomerInfo.FirstName)='Charles'));"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (SQLstr)

    End Sub

  3. 3

    Press "F5" to run your subroutine. The code will create a new table and add two new records of data. A new table will be created where "FirstName" is equal to "Charles."

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.