Running SQL from Excel 2

[UnsolvedCoding]

Hey all,

I have code that runs SQL from Excel but on several different locations when the recordset is opened the code errors out. It keeps telling me its not open. I have tried a couple of different formats (listed below) to get around this but it keeps coming up.

Set rsDB = New ADODB.Recordset
rsDB.CursorLocation = adUseClient
rsDB.Open sSQL, cnDB, 3, 1, 1


Set rsDB = New ADODB.Recordset
rsDB.ActiveConnection = cnDB
rsDB.CursorLocation = adUseClient
rsDB.Open sSQL, cnDB, 3, 1, 1

The other problem is I am having extreme difficulty in getting the recordset to go to a cell location. I have tried

ThisWorkbook.Sheets("Cover").Range("J" & i) = rsDB("REASON")
and
Range("J" & i) = rsDB("REASON")
and
Activecell.value = rsDB("REASON")
And
Activecell.offset(0,3).value = rsDB("REASON")

I'm stumped.

 

[gmmastros]

Are you calling stored procedures in the database? If you are, do you have "SET NOCOUNT ON" in the stored procedure? If not, you should.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

No stored procedures are being used.

 

[gmmastros]

I assume other queries are working, but some are failing. Can you show the SQL that is being called?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

sSQL = "select cb.id, cb.nbr, cb.amount, v.vend,"
sSQL = sSQL & "v.name, d.dept, r.reason"
sSQL = sSQL & "from JK_cb as cb"
sSQL = sSQL & "inner join JK_dept as d"
sSQL = sSQL & " on cb.dept= d.dept"
sSQL = sSQL & "inner join JK_vendor as v"
sSQL = sSQL & " on cb.vend = v.vend"
sSQL = sSQL & "inner join JK_reason as r"
sSQL = sSQL & " on cb.reason= r.reason"
sSQL = sSQL & "where cb_nbr = " & sClaimNumber

 

[Qik3Coder]

sSQL = sSQL & "where cb_nbr = " & sClaimNumber

sSQL = sSQL & "where [!]cb.[/!]cb_nbr = [!]isNull([/!]" & sClaimNumber [!] + ", 0)"[/!]

You should always use table aliases, and you may have had an issue with the sClaimNumber being filled in correctly.

I would try this by explicityl setting a value in lieu of the varible and see if your result comes back.
In addtion, does this statement work if you run it in a t-sql window?


Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

I will give it a shot. Yes it does run in Query Analyzer.

 

[UnsolvedCoding]

Still errors. Now I get this message

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.

 

[Qik3Coder]

Sorry, missed an ampersand after sClaimNumber

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[gmmastros]

I think I see the problem.

When building a query this way, you need to make sure there are spaces in the appropriate places, for example:

sSQL = sSQL & "from JK_cb as cb"
sSQL = sSQL & "inner join JK_dept as d"

You want the sql to look like this:

[tt][blue]
from JK_cb as cb
inner join JK_dept as d
[/blue][/tt]

Instead, it would look like this:

[tt][blue]
from JK_cb as cbinner join JK_dept as d
[/blue][/tt]

Note that there should be a space (or tab or carriage return/line feed) between the table alias cb and the keyword inner.

Try this:

sSQL = " select  cb.id, cb.nbr, cb.amount, v.vend,"
sSQL = sSQL & " v.name, d.dept, r.reason"
sSQL = sSQL & " from JK_cb as cb"
sSQL = sSQL & " inner join JK_dept as d"
sSQL = sSQL & "     on cb.dept= d.dept"
sSQL = sSQL & " inner join JK_vendor as v"
sSQL = sSQL & "     on cb.vend = v.vend"
sSQL = sSQL & " inner join JK_reason as r"
sSQL = sSQL & "     on cb.reason= r.reason"
sSQL = sSQL & " where cb_nbr = " & sClaimNumber

The only change I made here is... I added a space after the first quote. This is likely to end up with a couple unnecessary spaces, but it will likely make the query work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

Now it runs but doens't put anything into the cells.

The Claim number comes from a list in column L

sSQL = "select cb.id, cb.nbr, cb.amount, v.vend,"
sSQL = sSQL & "v.name, d.dept, r.reason"
sSQL = sSQL & "from JK_cb as cb"
sSQL = sSQL & "inner join JK_dept as d"
sSQL = sSQL & " on cb.dept= d.dept"
sSQL = sSQL & "inner join JK_vendor as v"
sSQL = sSQL & " on cb.vend = v.vend"
sSQL = sSQL & "inner join JK_reason as r"
sSQL = sSQL & " on cb.reason= r.reason"
sSQL = sSQL & "where cb.cb_nbr = isNull(" & sClaimNumber & ", 0)"

rsJKDB.Open sSQL, cnJKDB, 3, 1, 1

i = 1
While Not rsERDB.EOF
ThisWorkbook.Sheets("Cover").Range("G" & i) = rsJKDB("CB_ID")
ThisWorkbook.Sheets("Cover").Range("H" & i) = rsJKDB("Vend_Cd")
ThisWorkbook.Sheets("Cover").Range("I" & i) = rsJKDB("Vend_name")
ThisWorkbook.Sheets("Cover").Range("J" & i) = rsJKDB("reason_id")
ThisWorkbook.Sheets("Cover").Range("K" & i) = rsJKDB("Amount")
i = i + 1
rsERDB.MoveNext
Wend

 

[UnsolvedCoding]

When I make the change with the spaces I get this error

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.

 

[gmmastros]

Looks like you are looking on rsERDB but using rsJKDB.

i = 1
While Not [!]rsERDB[/!].EOF
  ThisWorkbook.Sheets("Cover").Range("G" & i) = [!]rsJKDB[/!]("CB_ID")
  ThisWorkbook.Sheets("Cover").Range("H" & i) = [!]rsJKDB[/!]("Vend_Cd")
  ThisWorkbook.Sheets("Cover").Range("I" & i) = [!]rsJKDB[/!]("Vend_name")
  ThisWorkbook.Sheets("Cover").Range("J" & i) = [!]rsJKDB[/!]("reason_id")
  ThisWorkbook.Sheets("Cover").Range("K" & i) = [!]rsJKDB[/!]("Amount")
  i = i + 1
  [!]rsERDB[/!].MoveNext
Wend

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

I fixed the typo and the code runs but without info populating cells.

 

[Qik3Coder]

GMM,
Totally missed the concat part with the keywords, been too long since I had to do that. I normally did a MsgBox or Debug.Print to get the text.

Unsolved,
You're probably running into an issue with the formatting of the number in Excel vs the number in the database.

What is the datatype of the cb_nbr in the database?
If it's a char(x) or varchar(x) then you will have to add single quoutes to your comparision. If it's a char, you may have a leading space problem and need to trim the cb.cb_nbr first.

sSQL = sSQL & " where [!]ltrim(rtrim([/!]cb.cb_nbr[!]))[/!] = isNull([!]ltrim(rtrim('[/!]" & sClaimNumber & "[!]'))[/!], [!]'[/!]0[!]'[/!])"

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

Here's the jist of whats going on with the complete code.

The user puts in a list of claim numbers and then the code needs to use the claim number list to get the relavent info populated in the correct cells. I also changed to rsERDB since its more reliable.

I have the problem of getting the code to run, loop and populate the cells. The ID number will never be null, the application used for data entry prohibits a null.

Public Sub GetSummary()

On Error GoTo SummaryErr

lLastRow = ThisWorkbook.Sheets("Cover").Range("L2").End(xlDown).Row

If lLastRow > 3000 Then lLastRow = 1

Range("L2").Select

Do

sClaimNumber = ActiveCell.Value

' Connect to SQL
SQL_Connection

Set rsERDB = New ADODB.Recordset
rsERDB.ActiveConnection = cnERDB
rsERDB.CursorLocation = adUseClient

sSQL = " select cb.cb_id, cb.cb_nbr, cb.amount, v.vend_cd, v.vend_name, d.dept_cd, r.reason_cd"
sSQL = sSQL & " from er_cb as cb"
sSQL = sSQL & " inner join er_dept as d on cb.dept_id = d.dept_id"
sSQL = sSQL & " inner join er_vendor as v on cb.vendor_id = v.vendor_id"
sSQL = sSQL & " inner join er_reason as r on cb.reason_id = r.reason_id"
sSQL = sSQL & " where cb.cb_nbr = '" & sClaimNumber & "'"

rsERDB.Open sSQL, cnERDB, 3, 1, 1

i = 1
While Not rsERDB.EOF
ThisWorkbook.Sheets("Cover").Range("G" & i) = rsERDB("CB_ID")
ThisWorkbook.Sheets("Cover").Range("H" & i) = rsERDB("Vend_Cd")
ThisWorkbook.Sheets("Cover").Range("I" & i) = rsERDB("Vend_name")
ThisWorkbook.Sheets("Cover").Range("J" & i) = rsERDB("reason_cd")
ThisWorkbook.Sheets("Cover").Range("K" & i) = rsERDB("Amount")
i = i + 1
rsERDB.MoveNext
Wend

Set rsERDB = Nothing
Set cnERDB = Nothing

ActiveCell.Offset(1, 0).Select

If IsEmpty(ActiveCell) Then Exit Do

Loop

'Adjust column width
Columns("G:V").EntireColumn.AutoFit


ThisWorkbook.Sheets("Cover").Range("A1").Select
MsgBox "Done getting summary"

Exit Sub

SummaryErr:

If Err.Description <> "" Then Debug.Print Err.Description

Set rsERDB = Nothing
Set cnERDB = Nothing

End Sub

 

[UnsolvedCoding]

Qik3Coder -

I tried your last suggestion and it didn't like it.

 

[UnsolvedCoding]

Ok, I got it to loop now, but it still isn't populating cells.

 

[Qik3Coder]

Doh,

First, you weren't isNulling the database value, but rather the value in the Excel sheet, in case the field was empty. This prevents the code from seizing on a syntax error when it tries to run with a value in the cell.

Is the [!]i[/!] in the While loop supposed to be the actual cell reference? because you always set it to 1 before starting.


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

It is supposed to be the cell reference. I put the i = 1 above the Do and it runs perfectly.

Both you guys rock!!!