We added a new module to a custom VBA app recently. We are polling a mixture of data from Macola (ERP) tables and 20-odd custom tables within the Macola database. The application is for order monitoring, release & distribution monitoring, & active dispatch. We use custom grids (not Office) to display data to the user.
App & SQL servers are Win 2008 R2. Default instance of SQL. Workstations are Win7. MSSQL 2008 STD SP1. Edges are Cisco ASAs, switches are Cisco Catalysts. I have eliminated x86 vs x64 WS, SQL timeout setting has been manipulated up & down, security restrictions are not at issue, have installed SQL client (or not), have no free memory constraints that I am aware of, and the processors are never seriously challenged here.
The new app module is for the exclusive use of a single small department (3-4 users). This department is not a transaction-intensive area, rather they are in R/O or View mode most of the time. However, during peak processing periods they are receiving intermittent runtime errors with the very common -2147467259 connection error code, consistently at the 30 second mark. I have seen this code my share of times over the years, however, it has always been a go/no go situation, and seldom an intermittent scenario.
The progammer has been over this area of the code repeatedly to no avail. The VBA project size is ~60Mb, so it is not exactly a trivial piece of software.