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
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
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.