xlodbc returning no rows & no errors

B

Boultonsj

Have added in xlodbc.xla in Excel SR1 and inserted following code in to
activate worksheet

Private Sub Worksheet_Activate()
Dim Chan As Variant
Chan = SQLOpen("DRIVER={Microsoft ODBC for
Oracle};UID=username;PWD=password;SERVER=servername;")
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLExecQuery Chan, _
"SELECT CUST_PERSONAL_DETAILS.CUSTP_CUST_SEQNO,
CUST_PERSONAL_DETAILS.CUSTP_NI_CODE FROM SUMMIT.CUST_PERSONAL_DETAILS WHERE
(CUST_PERSONAL_DETAILS.CUSTP_NI_CODE=[Enter the NI Code])"
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLRetrieve Chan, ActiveSheet.Range("B3"), , , True
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLClose (Chan)
End Sub
Sub XLODBCErrHandler()

Dim ErrMsgs As Variant
Dim ErrCode As Variant

' Call SQLError to return error values to the variant
' ErrMsgs.
ErrMsgs = SQLError()

' Display each item in the ErrMsgs variant array.
For Each ErrCode In ErrMsgs
MsgBox ErrCode
Next

End Sub

My problem is that for some NI codes it returns no data but I know the rows
exist on the oracle database. The error handling throws up no errors. I'm an
oracle dba playing at using Excel for the first time and would appreciate any
help you can give.
 
A

Al

I'm not familiar with Oracle SQL syntax, but from my
experience, you might want to check that you are entering
the Correct TYPE for the NI code. For instance, try
enclosing the NI code in single quotes.

-----Original Message-----
Have added in xlodbc.xla in Excel SR1 and inserted following code in to
activate worksheet

Private Sub Worksheet_Activate()
Dim Chan As Variant
Chan = SQLOpen("DRIVER={Microsoft ODBC for
Oracle};UID=username;PWD=password;SERVER=servername;")
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLExecQuery Chan, _
"SELECT CUST_PERSONAL_DETAILS.CUSTP_CUST_SEQNO,
CUST_PERSONAL_DETAILS.CUSTP_NI_CODE FROM
Click to expand...
SUMMIT.CUST_PERSONAL_DETAILS WHERE
(CUST_PERSONAL_DETAILS.CUSTP_NI_CODE=[Enter the NI Code])"
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLRetrieve Chan, ActiveSheet.Range("B3"), , , True
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLClose (Chan)
End Sub
Sub XLODBCErrHandler()

Dim ErrMsgs As Variant
Dim ErrCode As Variant

' Call SQLError to return error values to the variant
' ErrMsgs.
ErrMsgs = SQLError()

' Display each item in the ErrMsgs variant array.
For Each ErrCode In ErrMsgs
MsgBox ErrCode
Next

End Sub

My problem is that for some NI codes it returns no data but I know the rows
exist on the oracle database. The error handling throws up no errors. I'm an
oracle dba playing at using Excel for the first time and would appreciate any
help you can give.
Click to expand...
 
B

Boultonsj

Used single & double quotes but still failed. For those NI codes that work
using quotes caused them to fail. Thanks anyway.

Steve

Al said:
I'm not familiar with Oracle SQL syntax, but from my
experience, you might want to check that you are entering
the Correct TYPE for the NI code. For instance, try
enclosing the NI code in single quotes.

-----Original Message-----
Have added in xlodbc.xla in Excel SR1 and inserted following code in to
activate worksheet

Private Sub Worksheet_Activate()
Dim Chan As Variant
Chan = SQLOpen("DRIVER={Microsoft ODBC for
Oracle};UID=username;PWD=password;SERVER=servername;")
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLExecQuery Chan, _
"SELECT CUST_PERSONAL_DETAILS.CUSTP_CUST_SEQNO,
CUST_PERSONAL_DETAILS.CUSTP_NI_CODE FROM
Click to expand...
SUMMIT.CUST_PERSONAL_DETAILS WHERE
(CUST_PERSONAL_DETAILS.CUSTP_NI_CODE=[Enter the NI Code])"
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLRetrieve Chan, ActiveSheet.Range("B3"), , , True
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
SQLClose (Chan)
End Sub
Sub XLODBCErrHandler()

Dim ErrMsgs As Variant
Dim ErrCode As Variant

' Call SQLError to return error values to the variant
' ErrMsgs.
ErrMsgs = SQLError()

' Display each item in the ErrMsgs variant array.
For Each ErrCode In ErrMsgs
MsgBox ErrCode
Next

End Sub

My problem is that for some NI codes it returns no data but I know the rows
exist on the oracle database. The error handling throws up no errors. I'm an
oracle dba playing at using Excel for the first time and would appreciate any
help you can give.
Click to expand...
Click to expand...
 
J

Jamie Collins

Boultonsj said:
Used single & double quotes but still failed. For those NI codes that work
using quotes caused them to fail. Thanks anyway.
Click to expand...

What is the data type? Is there a pattern to the codes that fail e.g.
leading zeros, more than 16 digits, etc? Do they work if you hard code
them in the sql text?

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using SQL against Excel tables 1
Excel and DDE CPS Plus serial data acquisition problem 0
Convert list to UPPER, lower & Proper cases. 21
update db, no errors , no changes 6
Problem with worksheet functions and errors 2
Randomizer Modification Help....... 5
Pass Defined Name to a Command Button 3
For...Next not copying text from Worksheet to Worksheet, but no Er 1

Top