Getting started with ODBC for Excel 2004

[Bryan]

I am trying to make an Excel Workbook application that connects to a
PostgreSQL database on my computer. It is for a personal finance
application. I will be the only user and the database is simple so I *think*
I should be able to do this with VBA for Excel 2004. I just want to connect
to the database, run SQL queries and generate reports from those queries.

According to Mactopia, I know that first I need to purchase an ODBC driver
for Excel. I have downloaded a demo version just for testing. I want to
make sure I can really connect to a database with Excel 2004 before spending
the money on the ODBC driver.

So the problem -- I do not know what the VBA code would look like to make
the ODBC connection. I have searched quite a bit but find everything is
Windows-centric (ADO). And when I asked for help in an Excel webforum I just
get answers like "buy a Windows PC and just use ADO" or "ODBC is ancient, why
would you want to do that".


Sub ConnectToDatabase ()
' What goes in here?
End Sub


Any help would be greatly appreciated!

 

[Jonathan Monroe]

I am trying to make an Excel Workbook application that connects to a
PostgreSQL database on my computer. It is for a personal finance
application. I will be the only user and the database is simple so I *think*
I should be able to do this with VBA for Excel 2004. I just want to connect
to the database, run SQL queries and generate reports from those queries.

If you just want to periodically run some queries (and the SQL itself
doesn't change), then the easiest thing to do is to use MS Query
(Data->Get External Data->New Database Query) to construct your SQL
query and return the results to your spreadsheet. Then save the
spreadsheet. Any time you want to update the results, you just right
click on a value from your results and select "Refresh Data". Excel
will re-run the query against your database.

If you want to go ahead and write the VBA code, then you'll use the
QueryTables class, and your routine will look something like this:

Sub ConnectToDatabase ()
Dim sql
sql = "select * from sometable"

Dim connString
connString = "ODBC;DSN=myDSN;UID=myUserID;PWD=myPassword"

Dim thisQT As QueryTable
Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connString,
Destination:=Range("a1"))

thisQT.BackgroundQuery = False
thisQT.sql = sql

thisQT.Refresh
End Sub

This works on either the Mac or Windows.

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
http://www.actualtechnologies.com
(e-mail address removed)

 

[Bryan]

Thank you for your help! It worked, I appreciate it very much.