ODBConnection object vs. QueryTable object

[Sidewinder72]

Before August is over I want to successfully create a link between my spreadsheet and a database. At the moment it shall have to be a MySQL database because I can't seem to get the ODBC driver for Access working; that may be a separate thread.

The only other time I've successfully used ODBC was in a previous job. I used CommBASIC, a dinky VB 3.0-derived scripting tool for manipulating a terminal emulator (Outside View) and it offered functions like SQLOpen(DSN="DinkyDSN", bla bla bla), SQLExecQuery, SQLRetrieve that would store your query results in a previously declared 2-dimensional array. Yay. (Before learning those functions I had been feeding everything into or from unacceptably primitive .csv files. Gross.)

I'm still pretty much lost in the wilderness as far as even understanding the basics of how to do this with VBA. It looks like the two-dimensional array that CommBASIC used finds its counterpart in VBA's QueryTable object; but I'm also given to understand that I shall have to create an ODBCConnection object. I also notice that the ODBCConnection and QueryTable objects have very much (if not entirely?) the same methods and properties available.

I can argue that Microsoft's own documentation doesn't even distinguish between the two objects!! In their Dev Center, on the page for the ODBCConnectionType.CommandText property (I'm not allowed to post a link) they say they're describing the .CommandText property of "A variable that represents an ODBCConnection object" and they give this here example:

Please Login or Register  to view this content.

...wherein the variable is in fact a member of QueryTables!!

Do I in fact need to declare an ODBCConnection object, or am I going to get away with the .Connection, .CommandType, .CommandText properties (and others) of a QueryTable?

[dflak]

Have you considered MS-Query?
http://www.utteraccess.com/wiki/MS_Query

Usually, no coding is required unless you develop a query so complicated that it cannot be displayed graphically. Then you can put the SQL in an excel table and use a couple of lines of VB Code to rewrite the command string on the fly.