Excel VBA retrieval from SQLServer BI 2012

[sportsguy]

I have a VBA query which retrieves line item data from SQL server 2012 through ODBC link. However, I get the error message of Divide by zero, and the query halts. There is no divide by zero in the query, as I have run the query in SQL mgmt studio, and there are no divide by zero records. . .

I will post the code later as i don't have access to it while at home tonight.

does anyone have any idea for a work around? or why the error message?
is there a rounding issue or a decimal place issue?

or is the where clause cut off because i have exceeded a string length for the query?

thanks in advance

sportsguy

[Kenneth Hobs]

Welcome to the forum!

It should have shown the error in the standard SQL run. This can happen in Access as shown here: https://office.microsoft.com/en-us/access-help/avoiding-divide-by-zero-errors-in-access-HA001055073.aspx

[sportsguy]

Thanks Ken,
though I found the operator error this morning, prior to logging on. Faulty query design, but i will say that direct server queries certainly speeds up reporting to just access the server and bring in the line item details. . . . and as a contributor, here is the code:

Tab Code:


Private Sub RetrieveFPA_Click()
If [FINISHED_SUMMARY!D31] = 0 Then
MsgBox "Please Select Reporting Period the YELLOW BOX!", vbCritical, "MyCompanyName Financial Planning and Analysis"
Exit Sub
Else
Call Retrieve_Finished([FINISHED_SUMMARY!D31])
Call Retrieve_Active([FINISHED_SUMMARY!D31])
End If
End Sub



Module Code


Option Explicit
Public rngPeriod As Byte
Public rngYear As Integer
Public Const myConn As String = "Provider=SQLNCLI11;" & _
"SERVER=myservername;DATABASE=mydatabasename;Trusted_Connection=Yes;"
Public BeginPeriod As Long
Public EndPeriod As Long
Public strSQL As String
Public Cn As ADODB.Connection
Public Rs As ADODB.Recordset
Public Rw As Long
Public Col As Long
Public c As Long
Public MyField As Object
Public Destination As Range

Sub Retrieve_Finished(BeginPeriod As Long)
On Error GoTo Err_Retrieve_Finished
strSQL = "SELECT * FROM * WHERE * ORDER BY * "
Debug.Print strSQL
Application.Calculation = xlCalculationManual

'Clear Range prior to download
Sheets("FINISHED").Select
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Set Destination = [FINISHED!A8]

Set Rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Cn.Open myConn
Rs.Open strSQL, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Rw = Destination.Row
Col = Destination.Column
c = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, c) = MyField
c = c + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
c = Col
Loop

Rs.Close
Cn.Close

Set Rs = Nothing
Set Cn = Nothing
Application.Calculation = xlCalculationAutomatic
Application.CalculateFullRebuild
MsgBox "Finished Data Imported Retrieved!", vbExclamation, "MyCompanyName Financial Planning and Analysis"

Exit_Retrieve_Finished:
Exit Sub

Err_Retrieve_Finished:
Set Rs = Nothing
Debug.Print Err.Number & " - " & Err.Description
Resume Exit_Retrieve_Finished

End Sub


long time VBA code assembler from web snippets. . . I know what i want to do, but writing the code is the hard part. . .