Databases

I spent some time this morning with Tenzin, one of our PhD students, and Xuan Ling, a team member of ours who leaves us today, discussing the use of Access for Tenzin’s project. The last time I really paid much attention to databases was in 2000/2001 when my senior IPT class was learning about them. I learned SQL with MicroSQL (which I can’t find a reference to on Google) and while we did use Access to build a simple database it was only through the form and query point and click interface.

So this morning’s meeting was me scribbling out database design a la Visio Modeller (before Microsoft bought it) to try and work out what sort of tables would be needed and then trying to figure out how on earth to build said tables. Tenzin’s way ahead of me here with the database from his pilot questionnaire and he’s got some very cool subdatasheet stuff going on. If I knew how Access works it would have been a much more productive meeting this morning but it was still good to try to figure it out together.

I’ve had more success with getting R to talk to our big UPTECH database through ODBC. It’s the first time I’ve done anything like it and I’m managing, with some success, to get stuff into R. There are a few bizarre design choices in the database so it’s not as straightforward as it could be, but I can copy the text from the Access queries and get what I need. I’d much rather grab a chunk of data and process it in R than open Access, run some queries to get the data I want, save it as an Excel file, convert it to CSV and then load it into R.

Advertisements

2 thoughts on “Databases

  1. Matt

    Which R package do you use for that, RODBC? Do you need to be in Windows with the Access Jet ODBC drivers loaded, or are you able to read the .mdb file from Linux?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s