Microsoft Query - Background Refresh Error 1

[TheEntertainer]

I've recorded and mangled the following VBA code and it works fine in Excel 97 querying a Oracle 7 database:

Sub recon()
Dim StartDate
Dim EndDate
Dim UserID As String
Dim PassW As String
Dim sACCOUNT As String
sACCOUNT = InputBox("Enter Account&quot
sBNKCOD = InputBox("Enter Bank Code&quot
UserID = InputBox("Enter User ID&quot
PassW = InputBox("Enter Password&quot

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=" & UserID & ";PWD=" & PassW & ";SERVER=PMIS;", _
Destination:=Range("A1&quot)
'With ActiveSheet.QueryTables.Add(Connection:= _
'"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=" & UserID & ";PWD=" & PassW & ";SERVER=PMIS;", _
Destination:=Range("A1&quot)

.Sql = Array( _
"SELECT" & Chr(13) & "" & Chr(10) & " CLAIM.""ACC_NUM"", CLAIM.""CLM_NUM"", CLAIM.""OCC_DTE""," & Chr(13) & "" & Chr(10) & " PAYMENT.""BNK_COD"", PAYMENT.""PMT_TYP"", PAYMENT.""CHK_NUM"", PAYMENT.""ISS_DTE"" , PAYMENT.""PAID"", null " & Chr(13) & "" & Chr(10) & "FROM" & Chr(13) & "" & Chr(10) & " ""PYROWNER"".""CLAIM"" CLA" _
, _
"IM," & Chr(13) & "" & Chr(10) & " ""PYROWNER"".""PAYMENT"" PAYMENT" & Chr(13) & "" & Chr(10) & "WHERE" & Chr(13) & "" & Chr(10) & " CLAIM.""UNQ_ID"" = PAYMENT.""UNQ_ID"" AND" & Chr(13) & "" & Chr(10) & " CLAIM.""ACC_NUM"" ='" & sACCOUNT & "' AND" & Chr(13) & "" & Chr(10) & " PAYMENT.""BNK_COD"" = '" & sBNKCOD & "' " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "SELECT" & Chr(13) & "" & Chr(10) & " null ,null ,to_dat" _
, _
"e(null) ," & Chr(13) & "" & Chr(10) & " BANK.""BNK_COD"",BANKTRAN.""TRN_TYP"", to_number(null) , BANKTRAN.""TRN_DTE"" , BANKTRAN.""TRN_AMT"", BANKTRAN.""COM_TXT""" & Chr(13) & "" & Chr(10) & "FROM" & Chr(13) & "" & Chr(10) & " ""PYROWNER"".""BANK"" BANK," & Chr(13) & "" & Chr(10) & " ""PYROWNER"".""BANKTRAN"" BANKTRAN" & Chr(13) & "" & Chr(10) & "" _
, _
"WHERE" & Chr(13) & "" & Chr(10) & " BANK.""UNQ_ID"" = BANKTRAN.""UNQ_ID"" AND" & Chr(13) & "" & Chr(10) & " BANK.""BNK_COD"" = '" & sBNKCOD & "'" _
)

Dim text1 As String

text1 = ActiveSheet.QueryTables(1).Sql
MsgBox text1
.FieldNames = True
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Name = "Reconciliation"
.Refresh BackgroundQuery = False

End With

End Sub

However if I run it in Excel 2000 it generates an error:
Run-Time Error 1004 - General ODBC Error. Debugging identifies the line:

.Refresh BackgroundQuery = False

I know that the .sql line changes in Excel 2000 to .commandtext, but even changing this doesn't make a difference. I've tried recording directly in 2000 and if you try refreshing by right click on a cell within the range generated by the query, it refreshes, but if you try to run the code generated it,errors at the same line (although slightly different syntax in 2000)

.Refresh BackgroundQuery:=False

Any ideas would be appreciated.

 

[xlbo]

It errors on that line 'cos that's the line that actually executes the query

What happens quite often to me is that the code generated adds an extra comma to the end of the SQL array which causes the error - took me about 2 hours of trying to edit the SQL before I realised it was that

The other thing to watch is the "Destination" field which can cause an error if the active sheet is not the one with the query - I generally use sheets("Sheetname&quot.range("A1&quot just to be on the safe side

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[combo]

Try to separately create SQL string analyzing recorded string, remove "Array" keywords from it, if possible - open data in MS query and view sql.

When you have sql and connection - the rest is simple.
This is a code that was working for me:

[tt]Set wbkOutData=Worksheets(1)
Set qtOutData = wksOutData.QueryTables.Add(Connection:=strCStr, Destination:=wksOutData.Range("A1&quot, Sql:=sSQL)
With qtOutData
.CommandText = strSQL
.Name = "OutputData"
.Refresh
End With[/tt]

combo

 

[TheEntertainer]

Thanks for your response xlbo.

I think(!) that it creating the right sql because
1) I throw up an msgbox containing the SQL and it looks right
2) This same SQL works fine in Excel 97
3) If you refresh the query by right-clicking on a cell within the query range it works.

Can I check is the query linked to a sheet. Can I not just run the VBA and it work on whatever sheet is active? Not that it matters as I've tried both and it fails at the same point.

I've tried changing the destination as suggested and that didn't seem to work.

At this rate I may have to throw myself into the River Sheaf or Don? no one round here knows what it called.

Thanks

 

[TheEntertainer]

Hello,

I've got a bit further in trapping the error using the ODBC Error Object.

When on put a on error resume next before the refresh statement and the following code:

If Application.ODBCErrors.Count > 0 Then
Set er = Application.ODBCErrors(1)
MsgBox er.ErrorString & er.SqlState
Else.....

Its says:

[Microsoft][ODBC Driver Manager] DRIVER keyword syntax error IM012

Any ideas what this is might mean?

Thanks

 

[xlbo]

Interesting - seems like it is interpreting DRIVER as a variable name.....I have compared it to the querytable code I use and it gives me DSN instead of DRIVER.....worth changing and see if it works ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TheEntertainer]

Xlbo,

Thanks for having another look. In your response you say that it might be worth changing. How would I change it? If that is not to stupid a question.

Thanks

 

[xlbo]

simply replace the text DRIVER with the text DSN....
Gotta be worth a go....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TheEntertainer]

Xlbo,

Thanks a lot Mate.

That has gotten me much further, it atleast is refreshing the data. It doesn't seem to like the connection string into which I pass my UID and PWD, so it throws up the dialogue box for "Microsoft ODBC for Oracle Connection" into which I've got to enter my UID and PWD. It refreshes and brings the data back.

Once it gets to the line :

If Application.ODBCErrors.Count > 0 Then
Set er = Application.ODBCErrors(1)
MsgBox er.ErrorString & er.SqlState
Else.....

It says:

The following error occurred:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified : IM002

I'm not overly concerned because as long as I can fire up some login box, either a inputbox or the microsoft one. I'll be happy.

Thanks