Type Mismatch when running query on SQL Database
Hi,
I'm trying to run a query from Excel against an SQL Database. I get a Type Mismatch error when it is executing the following line of code. Sheets(SQLDestinationSheet).Range(SQLDestinationCe ll).QueryTable.CommandText = Array(FullSQL) Now this works fine for the rest of the program except for my last query and I suspect it is because the SQL statement that I store in the variable FullSQL is greater than 255 characters long. Anybody come across this and know a way around it? Ta. |
Type Mismatch when running query on SQL Database
have you tried splitting that string? this is an ultra crude way of doing it... Replace/Split need xl2k+ dim vaSql, sFullSQL$ if len(sFullSQL) <= 255 then vaSQL = array(sFullSQL) else sFullSql = replace(sfullSql," ",chr$(22)) vaSql = split(sfullsql,chr$(22)) end if ... .QueryTable.commandtext = vaSql -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Neily wrote : Hi, I'm trying to run a query from Excel against an SQL Database. I get a Type Mismatch error when it is executing the following line of code. Sheets(SQLDestinationSheet).Range(SQLDestinationCe ll).QueryTable.Comma ndText = Array(FullSQL) Now this works fine for the rest of the program except for my last query and I suspect it is because the SQL statement that I store in the variable FullSQL is greater than 255 characters long. Anybody come across this and know a way around it? Ta. |
Type Mismatch when running query on SQL Database
Neily
Bill Manville and I were just discussing this. See this thread http://groups-beta.google.com/group/...16603c1071b148 I don't know all the details, but you can split your strings into an array and it will work. See Bill's functions for helping you do this here http://www.dicks-blog.com/archives/2...per-functions/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Neily wrote: Hi, I'm trying to run a query from Excel against an SQL Database. I get a Type Mismatch error when it is executing the following line of code. Sheets(SQLDestinationSheet).Range(SQLDestinationCe ll).QueryTable.CommandText = Array(FullSQL) Now this works fine for the rest of the program except for my last query and I suspect it is because the SQL statement that I store in the variable FullSQL is greater than 255 characters long. Anybody come across this and know a way around it? Ta. |
All times are GMT +1. The time now is 10:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com