+ Reply to Thread
Results 1 to 5 of 5

How to: Extract specific data from an Access Database

  1. #1
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    246

    How to: Extract specific data from an Access Database

    I have a Access Database client on my desktop: ODI Inspections Database v114 that connects to our portal internet.

    When I open it up (we still use Access 2002, which will soon change to Access 2010), I have to go to Window, Unhide, go to Tables, then open up tblStructure.

    Once open, I need to filter Column B (Log Number) for G2. This will bring up 80 rows (80 widgets needing inspections).

    Once it is filtered out, I copy that row, and the next row to the right (Checklist Num).
    I paste them to Sheet: Widget List, where once there, I sort in numerical order 1-80 based on the Checklist Num column (Column B).

    THEN.... once that is done can I copy those 80 widgets in Column A into their right corresponding templates in the other Tabs I have.

    How can I automate this? I've never macro'ed to another program before.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,309

    Re: How to: Extract specific data from an Access Database

    You have several options available to you.
    1. Create a query with the criteria you wish and then use VBA (employing DoCmd.TransferSpreadsheet) to export or manually export the query to Excel. This is a fairly straightforward and easy solution.
    2. Create a query in Access and link to it via Excel. Data, Get External Data, Access. For this you will have to have the same criteria for the query to work and export properly
    3. Look at this link to set the criteria in Excel and then query Access from Excel. http://datapigtechnologies.com/blog/...ry-from-excel/

    Post back with specific questions once you have decided which direction you wish to go.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    246

    Re: How to: Extract specific data from an Access Database

    This is waaaay over my head. I had figured that I could just run the coding in an excel macro and not have to touch anything in Access.

    I really don't want to touch Access because the Analysts don't want me to. If I make one wrong move, I will perhaps get in big trouble.
    Last edited by JPWRana; 10-28-2013 at 03:37 PM. Reason: More euphamistic.

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,309

    Re: How to: Extract specific data from an Access Database

    Suggest you have the Access Gurus at your location do this for you. It will take no more than 30 minutes for them to implement any one of the concepts I have outlined. Or try to do this one step yourself.

    Open Excel, click on Data, Get External data. Find your Access db. Click to open it. Select the table you want from the listing provided. Import it. Do your filtering in Excel using the Filtering built into Excel.
    Last edited by alansidman; 10-28-2013 at 03:37 PM.

  5. #5
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    246

    Re: How to: Extract specific data from an Access Database

    I'll see what they say.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Opening a Specific Table In Access Database Using VBA
    By MBeedle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-18-2013, 04:23 PM
  2. Replies: 2
    Last Post: 07-05-2013, 09:37 AM
  3. Extract specific column data from specific sheet from multiple files in a folder
    By piggyfox in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-31-2013, 11:51 AM
  4. [SOLVED] Extract Data from an Access Database in to Excel
    By John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2006, 08:20 AM
  5. importing data from access-database access file
    By amrezzat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2005, 04:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1