Lost Data Connection

I have been successfully using the following PostgreSQL connection string in multiple Excel 2010 vba applications running on 64 bit Windows 7 systems. I have moved to a Window 10 system and my application will randomly lose its connection to the DSN. It may take 5000 calls one time and then 400 calls the next. I am using a Unicode driver. I wrote a sample program that makes the same call in a loop. The code will randomly fail on Window 10 and run flawlessly on Window 7. Both systems are running psqlODBC version 10.01.0000.

ODBCDSN = "ODBC;DSN=PostgreSQL35W;"

Sub GetWarehousingDollars()

Dim i, k As Integer Dim sConn As String Dim sSql As String Dim oQt As QueryTable Dim sLastJob As String Dim iJump As Long

Application.DisplayAlerts = True

iMaxParts = 5000

sConn = ODBCDSN

sConn = sConn &

"DATABASE=epace;SERVER=ppp.yonker12.com;PWD=test1234;PORT=5432;UID=epace_read;SSLmode=disable;ReadOnly=0;" Application.ScreenUpdating = True

Worksheets("Work").Activate Cells.Select Selection.ClearContents iJump = 1 For i = 1 To iMaxParts sLastJob = sPartUsage(i, 8) ' Last Job

sSql = ""

sSql = "SELECT job.ccmasterid,estimateactivity.cost, estimateactivity.markup,estimateactivity.hours,estimatequantity.quantityordered,estimate.lastjob,estimate.customerid,estimate.entrydate,jobpart.esmasterid,jobpart.ccjobpart, "

sSql = sSql & "jobpart.ccqtyordered,job.ccdatesetup,estimateactivity.unitlabel  "

sSql = sSql & "FROM  (  (  (  (  ( public.estimatequantity estimatequantity INNER JOIN public.estimatepart estimatepart ON "

sSql = sSql & "estimatequantity.estimatepartid=estimatepart.estimatepartid )  INNER JOIN public.estimate estimate ON estimatepart.estimate=estimate.estimateid ) "

sSql = sSql & "INNER JOIN public.estimatestatus estimatestatus ON estimate.status=estimatestatus.id )  INNER JOIN public.jobpart jobpart ON "

sSql = sSql & "estimate.estimatenumber=jobpart.esmasterid )  INNER JOIN public.job job ON jobpart.ccmasterid=job.ccmasterid )  INNER JOIN "

sSql = sSql & "public.estimateactivity estimateactivity ON estimatequantity.estimatequantityid = estimateactivity.estimatequantityid "

sSql = sSql & "WHERE ((job.ccmasterid = '37712')) AND ((estimatestatus.sequence = 7)) AND ((estimateactivity.activitycodeid = '70515')) AND "

sSql = sSql & " ((estimateactivity.unitlabel LIKE '%Warehous%')) AND      ((estimatequantity.quantityordered = jobpart.ccqtyordered)) "

sSql = sSql & "ORDER BY job.ccmasterid, estimatequantity.quantityordered"

' MsgBox sSql Set oQt = ActiveSheet.QueryTables.Add( _ Connection:=sConn, _ Destination:=Range("a" & iJump), _ Sql:=sSql)

oQt.Refresh BackgroundQuery:=False

iJump = iJump + 2

Next

End Sub

Steven D. Haley

VP IT and Process Development

shaley@yunker.com

Yunker Industries, Inc.│310 O’Connor Drive│Elkhorn, WI 53121│Direct 262-741-5523│Main 262-249-5220│www.yunker.com