Copy data from an Access database into Excel

Hi all

Last week I start building a page about Excel-Access.
I want to add more pages soon about Excel-Access but I start with this one
“Copy data from an Access database into Excel”

Most examples on the internet are not so easy to work with for a normal user like me.
I go try to change that on this webpage.

In example workbook 2 I use data validation cells to fill in the criteria and in example
workbook 3 also the Calendar control to fill in the dates.

I add a new workbook where you save 100 or more criteria.
Very easy to get the result you want this way

Suggestions good or bad are welcome

You can find it here
http://www.rondebruin.nl/accessexcel.htm

Ron de Bruin
http://www.rondebruin.nl

Posted in Uncategorized

9 thoughts on “Copy data from an Access database into Excel

  1. Ron,

    A good start is to avoid expressions such as vbg as the average Excel-users (who You target) may have difficulties to understand the abbreviation.

    Dennis

  2. looks nice Ron, good work :-).

    It’s unimportant, but i think maybe i might haved changed “MySQL”, to something like “MySQLStatement”, just to be clear.

    Also, what are your (or others) expriance with using the calender control? I suggested to a work mate that he avoided using it as it may cause problems on other peoples PC’s? – it the census that most mechinces will have the contol?

    also, also what is vbg?
    Vertical Banded Gastroplasty;?
    very bad girl?

    i really hope it the latter ;-)

  3. Hi Ron,

    You are using ADO to connect to an Access database. Nothing wrong with that but you might like to point it out on your webpage to avoid confusion with DAO.

    Also, as Ross already mentioned, I wouldn’t use ‘MySQL’ for the string variable that holds your SQL statement. Not so much because it’s a statement but because MySQL is the name of a famous database. See http://www.mysql.com/ for details. You might want to avoid confusion there.

    Whenever I construct a SQL statement I simply use sSQL. De preceding s is simply for String, which is exactly what it is.

    Regards,

    Rembo
    (a fellow Dutchie :))

  4. Yeah the calender control is not, as far as i know, a stanard MSForms control. Nore is she to be found in the ms windows common controls (mscomctl.com).
    Not sure about it’s relationship to Access or it’s linceing, but it could be a gothca.

  5. Your example is whay! to complicated….

    What happened to good old “SQL.Request”.
    The in excel cell SQL. No VBA required…
    Just patch in the ODBC addin and your off.
    Big data or just one cell it’s up to you.
    It is a great entry level Access to Excel link.

    Alternatively if you want a bit of control most users can just about handle a quick Querytable.

    ala…

    Sub GetMeSomeData()
    ‘ Returns data from the database on a clean new worksheet.
    Dim qt As QueryTable
    Dim sht As Worksheet
    Set sht = ActiveWorkbook.Worksheets.Add
    Set qt = sht.QueryTables.Add(Connection:=”ODBC;DSN=MS Access Database;DBQ=C:Documents and SettingsJanMy DocumentsDatabase Solution.mdb;”, Destination:=Range(“C1?))
    qt.CommandText = “SELECT * FROM Contacts”
    qt.Name = “Query from MS Access Database”
    qt.Refresh
    End Sub

    Problem solved no ?

    Also while I appreciate that the ADO is quick (arguably not so quick) there is always old fashioned DAO which is easier for the beginner to read.

    Jan

  6. Hi all,

    SQL.REQUEST
    http://office.microsoft.com/en-us/assistance/HP052092671033.aspx

    One of the real big issues is the fact that exist several ways to “skin a cat”.

    By choosing only one approach will eliminate much of the confusing the average users experience.

    As for the question about DAO or ADO I believe the following thread at ExcelKB’s forum should be useful to take part of.
    http://www.excelkb.com/instantforum41/Topic74-14-1.aspx

    Kind regards,
    Dennis


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *