4

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.

Improve this question
1
  • ditto, what is the error number and message you get? Commented Oct 30, 2008 at 17:09

6 Answers 6

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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"
Improve this answer

Comments

1

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
Improve this answer

Comments

1

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.

Improve this answer

Comments

0

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
Improve this answer

1 Comment

The table exists, all of those columns exist, etc., yes? Can you post the exact error message? Thanks.
0

I was having this same issue when trying to refresh a Query.

For some reason that I don't know. When refering to a QueryTable object the refresh only works the first time you run the vba code. If you run it again the runtime error will prompt Run-time error '-2147217842(80040e4e): Method 'Refresh' of object '_QueryTable' failed occurs

This is an example of a Query refresh that fails. Ws.ListObjects("TableName").QueryTable.Refresh BackgroundQuery:=False

Here is the solution found. ThisWorkbook.Connections("ConnectionName").Refresh

If someone knows the reason why the refresh method of the QueryTable object fails. please let us know.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.