Excel 2007 Show Columns Error
I have a small app that allows me to run SQL queries to grab data through a
DSN (MySQL ODBC Connector 3.51) and dump it into a spreadsheet in Excel. The app works great in Excel 2003. In Excel 2007, however, using SHOW COLUMNS FROM `table_name` or DESCRIBE `table_name` results in run-time error 1004: No columns that Microsoft Office Excel can use were returned from this query. This is the code. It runs through a user form. I type the SQL into TextBox1 and click CommandButton1. Private Sub CommandButton1_Click() Dim oQueryName As String oQueryName = Format(Date, "yyyy-mm-dd") & Chr(95) & Format(Time, "hh-mm-ss") With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= MyODBC", Destination:=Selection) .CommandText = RunQueryForm.TextBox1.Value .Name = oQueryName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False .EnableRefresh = True End With If Not CheckBox1.Value Then 'delete the query table. this is equivalent to unchecking "Save query definition." ActiveSheet.QueryTables.Item(oQueryName).Delete End If Unload Me End Sub So far there hasnt been a lot of information on Excel 2007, so any help you can offer is greatly appreciated. Thanks. |
Excel 2007 Show Columns Error
The problem was the MySQL ODBC Connector. I inadvertently installed the
latest version 3.51.15 which doesn't work. I uninstalled and backed up to version 3.51.12. Works perfectly now on both Excel 2003 and Excel 2007. Whew. I thought it was going to be some nasty Vista incompatibility. Amazingly, my entire application runs perfectly on Vista. Problem solved. |
All times are GMT +1. The time now is 01:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com