ODBC Connection

[j_Southern]

I want to use code to connect Excel to an SQL database. I have successfully made this connection using MSQuery and have copied the SQL statement and the connection string to use in the code.
Const CERMCONN for the connection and
BuildSQL for the SQL String

Please Login or Register  to view this content.

As you can see I have simplified the SQL String as far as I can.
I get an error at the line Set qtTAPEO = Sheets(SheetName).QueryTable.Add(CERMCONN, rngTape, strSQL)
My question is how should I proceed to find if it is the SQL string or the Connection line that is giving the error
John

[j_Southern]

As there has been no immediate response to my ODBC link question, perhaps I can ask a more general one. I have used the connection string I found in the connection property window after I had successfully used MSquery . I used the SQL string I found in the SQL report in MSQuery. Should I have expected success when copying over to my VBA code, or are there some differences I should know about?
John

[j_Southern]

I have overcome the first problem by using a record set rather than a query table. I am still in difficulties though because I have some date variables which I need to put into the SQL string, and they are not working.
I am using the code shown me by xlnitwit which works with MSQuery but it seems not with a recordset.
This code works unless I add one or both lines marked ****

Please Login or Register  to view this content.

Can anyone show me where I have gone wrong
John

[j_Southern]

I have been looking at my problem from a different direction. I can manually prepare the query in MSQuery and use code to refresh it. This works but is clumsy as I have to have parameters for the dates.
However in the connections/properties window is a button marked parameters which lets you choose a cell to give its value, and this would be an excellent solution for me. When I try it however I get an error message telling me I have the wrong date format.
I have tried every format I can think of always with the same result. What format should I use?

Please someone help before I go stark staring bonkers!
John

[xlnitwit]

Hi,

I believe that for a querytable the connection string ought to begin with "ODBC;" which might explain the problems you had originally.

I would suggest you enter fixed dates in your SQL string rather than using parameters, in order to determine what format works. It should then be a fairly simple matter to convert to a parameter- it may simply be a question of entering a formatted text string, rather than an actual date, into the parameter cell.

[j_Southern]

xlnitwit

I have been entering fixed dates in the way you suggest and have found that MSQuery likes dates formatted yyyy/mm/dd. When I use a cell with such a formatted date I get an error.
I have not thought about putting in a string though so thanks for that I shall go away and try it.
John