cancel
Showing results for 
Search instead for 
Did you mean: 

Lost Data Connection

SOLVED
Highlighted
Adventurer

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 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. It will fail on the oQt.Refresh BackgroundQuery:=False call and show the dialog box looking for the "PostgreSQL35W" DSN. Both systems are running psqlODBC version 10.01.0000 32 bit Unicode.

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Adventurer

Re: Lost Data Connection

This problem has been resolved. It appears that our workstation installation of Symantec End Point Protection 14.10.1 MP1 was forcing the workstation to drop the connection. Once removed all programs funtioned as expected.

1 REPLY
Adventurer

Re: Lost Data Connection

This problem has been resolved. It appears that our workstation installation of Symantec End Point Protection 14.10.1 MP1 was forcing the workstation to drop the connection. Once removed all programs funtioned as expected.