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. Hi Dennis

   I delete it.

   Thanks

3. 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 ;-)

4. 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 :))

5. Hi all

   Ross: very bad girl?
   I always say that to my wife

   If you not have Access you not have the control on your system
   There is a link on this page where you can download it. http://www.rondebruin.nl/calendar.htm

   Rembo : Good point about MySQL
   I will make it clear that i use ADO

   Thanks

6. 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.

7. 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

8. I dont think the Addin you refer to ships with excel any longer, but it can still be down load from MS.com come where?

9. 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.