Examples for Exporting Access table or query to EXCEL Workbook Files Part 1

Create and Export a
Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Generic code to generate “on the fly” a query that uses one or more controls on
an open form as parameters, and then export that query to an EXCEL file. This
example concatenates the parameter values into the generated SQL statement and
then saves the query so that it can be exported. The query then is deleted after
the export is completed.

 

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

‘ Replace NameOfTableOrQuery with the real name of the table or query,
‘ replace NameOfForm with the real name of the form, and replace
‘ ADateControlOnForm and AnotherDateControlOnForm with the real names
strSQL = “SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery ” & _   
“WHERE NameOfTableOrQuery.FieldName >= ” & _     
Format(Forms!NameOfForm!ADateControlOnForm.Value,”\#mm\/dd\/yyyy\#”) & _     
” And NameOfTableOrQuery.FieldName <=” & _     
Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,”\#mm\/dd\/yyyy\#”) &
“‘;”

‘ of the controls on that form

strQDF = “_TempQuery_”
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

‘ Replace C:\MyFolderName\MyFileName.xls with the real path and
filename for the
‘ EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF,”C:\MyFolderName\MyFileName.xls”

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

By Ken Snell 

Secrets and Surprises in Geo APIs Summer Learning Series

The second session of the Geo track at Google I/O was “Secrets of the Surprises of the Google Geo APIs.” Going into I/O we wanted to have a session that could serve as a foundation for later sessions and one where we can feature some of the latest launches leading up to Google I/O. We covered features of the Maps API v3, the Earth API, Fusion Tables, the Maps API Web Services and the Static Maps API in this one session.

What we hope that you take away from this talk is that there is more to the Maps API than just putting markers on a map – we’ve been actively adding many cool features that can really enhance your mapping application and create a better experience for your users.

Here’s a small subset of the features discussed in the talk:

  • With an ever-growing list of cities with 45° imagery available you’re now able to give your users a new perspective of what a place looks like.
    Don’t forget that there are several easy to enable layers available to quickly add content to your map.
  • Developers have frequently requested the ability for API applications users to drag direction routes around the map in a similar manner to Google Maps, so as you might have guessed, enabling draggable directions in the Maps API is now as simple as a one line code change.
  • A favorite feature of the Maps API is the ability to style the base Google map. With the power to change not only the features shown on the map but the color scheme of those features you have tremendous ability to theme your map to fit a look or brand or remove features that are relevant to your users.
  • Street View in the Maps API v3 is implemented entirely in HTML and Javascript so it’s usable on mobile devices with capable browsers without the use of a plugin. This also gives you the power to provide your own custom panoramic imagery and link it to our standard Street View imagery. Don’t let the experience end at the front door – take your users indoors!
  • We’ve added a Distance Matrix service that lets you quickly and easily get travel time and distance between many origins and destinations in one call.

    • In addition to providing driving, walking and bicycling directions the directions service will even optimize the route, determining the best order to visit several destinations.

We talked about a lot more – including AdSense integration, the Places API, useful open source libraries, Fusion Tables and historical imagery, trees and 3D buildings in the Earth API – check out the video or review the slides to learn more!

Artistic 3D Visualizations

Today I want to highlight the work of the Senseable Lab at MIT, from a brief review of their work I’d say they seem to specialise in the area of real time 3D visualization and sensor input.

Beautiful Design Ideas: From an artistic ‘this is a work of art’ point of view their ideas are novel, fun and highly engaging, see this TED talk for examples
I really relate to the water building, I hang out on the South Bank in London and there’s a similar water sculpture there that is hugely popular (clip). Also, I’d LOVE to have some of those helicopter pixels in my lectures to illustrate geography concepts like earthquake waves to students.
Artistic 3D Visualizations of Singapore: This year the Sensable team have produced a project collecting real time data from Singapore and visualizing it. Here are some examples as a clip:

 

Looking from the angle of information communication there’s lots to like:
  • Engaging animations. The graphics draw the viewer in to find out more, they’re certainly engaging and artistically beautiful. I’m sure their exhibition at Singapore Art Museum was a sucess.
  • Elegant Time lines: They show time as a playhead moving against a timeline or against a bar chart illustrating relevant data. These elegant graphics are minimalist and communicate effectively without making the animation too busy visually. In a lot of ways they remind me of Tufte’s sparklines.
  • 3D Data Visualized Well? I’ve previously praised their technique in the of visualising 3D data using altitude, color and opacity at the same time as a way of getting over the problems of 3D thematic maps.
Beautiful but Ineffective? However, I worry that beyond looking attractive, these visualisations fail to communicate the data effectively. Two example issues that occurred to me:
  • Double 3D = Busy: In the heat vs energy consumption visualisation I think trying to show 2 sets of 3D data at once with the top layer of data partly obscuring the bottom layer doesn’t work well.
  • Where’s the Rain?: In the rainfall taxi visualisation by having the rain plot in 3D above the ground its difficult to relate where its actually falling on the ground.

I raise these issues without any evidence that they are actually problems, the only way of doing that is to conduct users tests. On the research page of the Singapore project Sensable discuss technical innovations and I admit in a real time visualisation project these are significant and important. However, there is no mention of user tests, given the amount of time and money that has gone into producing these animations wouldn’t it be a good idea to find out if they actually work?