Exporting an ODBC table in MS Access

Posted on 2011-04-19
Last Modified: 2012-05-11
I have a database of customers on my web server. I can connect to it with Access and view all the customers (about 75,000). I would like to download this to a LOCAL database with Access. The purpose is to produce a mass mailing.

1) I would like to be able to export all the data to a new table that is stored LOCALLY on my computer. I can see how to easily export to Excel, but when I try to export to Access, all it does is sets up a new linked ODBC table. I'm sure there is something very simple to do this, but I'm not seeing it.

2) I would like to do the same with with a query that displays only the fields that I need. This is another option. I already have the query set up. But I can't figure out the export.

I am using Access 2007.

Thanks for your help.

Question by:gregholl
    LVL 6

    Accepted Solution

    I would connect from an empty access db to the ODBC table. You can then import the table, so you should be able to export to excel from there.

    Choose the "External data" tab -> Click "More" -> Click ODBC-database.

    LVL 1

    Author Comment

    Thanks for your help. Here are some screen shots for posterity

    1: Make a new empty db
    2: Under IMPORT, select "MORE" and then "ODBC"
    3: Enter ODBC information
    LVL 1

    Author Closing Comment

    LVL 26

    Expert Comment

    It may sound dumb--but copy and paste.
    Pasting gives you options
    optionsThis dialog is from Access 2003, but I doubt 2007 is much different.
    Make it a local table.

    Is it more complex than this?
    Or, you just have never tried to copy and paste a table and seen the dialog?
    LVL 1

    Author Comment

    We're talking 75,000+ records here. Way too much for the clipboard to handle
    LVL 26

    Expert Comment

    Your call, of course
    Here's 145000 row table of a SSEE 2005 linked backend
     originalGoing in local in 43 seconds
     tempWorks fine.
    <We're talking 75,000+ records here. Way too much for the clipboard to handle >

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now