your - vba run sql query from excel




VBA SQL Query Table Error (2)

Looks like your connection string has double quotes in it. This could potentially be due to some parsing by the website

you should probably set sConn using "double double" quotes, as in:

sConn = "ODBC;DSN=Aus1S002;UID=""somevaliduser"";PWD=""avalidpassword"";SRVR=""Thecorrectserver"";DB=blue"

I am trying to pull data from an ACD call data system, Nortel Contact Center 6.0 to be exact, and if you use that particular system what I am trying to capture is the daily call by call data. However when I use this code

(sCW is a common word string that equals eCallByCallStat and sDate is

dDate = Format(Month(deffDate) & "/" & iStartDay & "/" & Year(deffDate), "mm/dd/yyyy")

sDate = Format(dDate, "yyyymmdd") )

sSql = ""
        sConn = "ODBC;DSN=Aus1S002;UID=somevaliduser;PWD=avalidpassword;SRVR=Thecorrectserver;DB=blue"
        sSql = "SELECT " & sCW & sDate & ".Timestamp, "
        sSql = sSql & sCW & sDate & ".CallEvent, "
        sSql = sSql & sCW & sDate & ".CallEventName, "
        sSql = sSql & sCW & sDate & ".CallID, "
        sSql = sSql & sCW & sDate & ".TelsetLoginID, "
        sSql = sSql & sCW & sDate & ".AssociatedData, "
        sSql = sSql & sCW & sDate & ".Destination, "
        sSql = sSql & sCW & sDate & ".EventData, "
        sSql = sSql & sCW & sDate & ".Source, "
        sSql = sSql & sCW & sDate & ".Time " & vbCrLf
        sSql = sSql & "FROM blue.dbo.eCallByCallStat" & sDate & " " & sCW & sDate & vbCrLf
        sSql = sSql & " ORDER BY " & sCW & sDate & ".Timestamp"


        Set oQT = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"), Sql:=sSql)

        oQT.Refresh BackgroundQuery:=False

        Do While oQT.Refreshing = True
        Loop"

When I run this I get an odd error message at oQT.Refresh BackgroundQuery:=False

Oddly enough it worked for a month or so then just died


@ loopo I actually added the "" to the connection string and actually have the user name and password hard coded into the query with out quotes, I have since removed them for clarity in the posting


The error I recieve is

Run-time error '-2147417848(80010108)': Method 'Refresh" of Object "_QueryTable' Failed


Thanks for your input Kevin. The Database is never in a state where no one is accessing it, it is a Call Handling system that is on 24 x 7 and always connected to is clients. At least that is my understanding. If I do this manually through Excel I never get an error, or have any issues only when I am doing this via a macro does it give me issues which lead me to think that it was my code causing the issue.

I am connecting to the database via ODBC as recommended by the manuafacturer, but I wonder if they ever envisioned this sort of thing.

I will see if I can leverage this into a .NET project and see if that helps.


Seems like an error with the query itself...

If you can step through your code and post the contents of sSql, it would probably help troubleshoot...

When you go through it, be sure quotes are getting escaped properly.





vba