VBA SQL Query Table Error problem

Question

Gentlemen, 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

Answer 1

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.

Answer 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"

Answer 3

I start by deleting the contents of sSQL with sSql=""

after that, because the query is run in a for loop I build the query in each of the next lines, each line builds on the previous line, I made it that way so it would be easier to edit and understand by the next guy.

After running through the sSQL looks like this sSQL=SELECT eCallByCallStat20081001.Timestamp, eCallByCallStat20081001.CallEvent, eCallByCallStat20081001.CallEventName, eCallByCallStat20081001.CallID, eCallByCallStat20081001.TelsetLoginID, eCallByCallStat20081001.AssociatedData, eCallByCallStat20081001.Destination, eCallByCallStat20081001.EventData, eCallByCallStat20081001.Source, eCallByCallStat20081001.Time FROM blue.dbo.eCallByCallStat20081001 eCallByCallStat20081001 ORDER BY eCallByCallStat20081001.Timestamp

Answer 4

@ 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

Answer 5

What is the actual error message you're getting?

In the FROM clause, are you trying to SELECT from 2 different tables, with the same name in different namespaces? (In which case I think they should be separated by a comma rather than a space)

Or is there supposed to be another '.' instead of the space in the FROM clause? Or is it an alias?

Do you need to specify the table for every field? why not just do:

SELECT Timestamp, CallEvent, ... ,Time 
       FROM blue.dbo.eCallByCallStat" & sDate & " ORDER BY Timestamp

Answer 6

Ok now I feel rather dumb, ah because I didn't think of that? I will try that Monday and get back to you if you are following this post Loopo.

Oh yeah "BOO!"

Scared ya huh?

Thanks for your help...

Answer 7

The error I recieve is

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

Answer 8

Microsoft says this: -2147417848 (80010108) The object invoked has disconnected from its clients.

And now I am officially over my head.

I always find this very odd, as sometimes it works, sometimes it doesn't. Once it stops working it usually stays stopped for some indeterminate period of time. Then it will work again. I hate that sort of thing. So how would you recommend debugging this?

Thanks folks!

Craig

Answer 9

First off, if you're connecting to a non-generic database (SQL Server, Oracle, etc.), try using a database connection that's specific to it.

Secondly, since you said this error comes and goes, can you test whether it still happens when no one else is accessing the system? Perhaps it is an issue with certain rows being locked while your query is trying to read them...

Third, either switch to a different reporting method or find a different way to get the data. There are limits to this type of call within Excel. While, yes, it certainly does allow you to connect to databases and pull in data, you may find it falling short if you're working with large sets of data, complex queries, or finicky database connections.

Answer 10

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.