Excel 2010 vba loses system DSN 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 it's connection to the DSN. It may take 5000 calls one time and then 1500 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.
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