Error 3151 ODBC - connection to "sql serverpgsrv2 failed"
aceware
The following piece of code is executed each time while outer looping
through approx 1000 records in another table:
strSQL = "SELECT tblRefFeeSiloV3.Fee FROM tblRefFeeSiloV3 "& _
"WHERE (tblRefFeeSiloV3.SeasonId= "& lngSeasonId& ") "& _
" AND (tblRefFeeSiloV3.Tier= "& lngTierId& ") "& _
" AND (tblRefFeeSiloV3.GrainHandlerId= "& lngGrainHandlerId &
") "& _
" AND (tblRefFeeSiloV3.CommodityTypeId= "& lngCommodityTypeId
& ") "& _
" AND ((tblRefFeeSiloV3.GradeGrouping2Id Is Null) or
(tblRefFeeSiloV3.GradeGrouping2Id = "& lngGradeGrouping2Id& "))"& _
" AND (tblRefFeeSiloV3.FeeTypeId = "& lngFeeTypeId& ")"
Set rs = CurrentDb().OpenRecordset(strSQL, dbReadOnly)
The openrecordset line fails with the above error message at record 359
(after slowing down significantly for the previous 50 records)
The value of strSQL in the openrecordset is:
SELECT tblRefFeeSiloV3.Fee FROM tblRefFeeSiloV3
WHERE (tblRefFeeSiloV3.SeasonId= 4)
AND (tblRefFeeSiloV3.Tier= 1)
AND (tblRefFeeSiloV3.GrainHandlerId= 1)
AND (tblRefFeeSiloV3.CommodityTypeId= 1)
AND ((tblRefFeeSiloV3.GradeGrouping2Id Is Null) or
(tblRefFeeSiloV3.GradeGrouping2Id = 0)) AND (tblRefFeeSiloV3.FeeTypeId =15)
There are only 78 records in tblRefFeesSileV3
The code runs quickly and flawlessly for physical workstations connected to
the network .
------------------------------------------------------------------------------------------
The error occurs for me coming in via a remote desktop connection to a
terminal server and running the database on the terminal server:
Terminal Server :Server 2003 R2 Std edition
The terminal server is running as a virtual machine under VMware - Vsphere
4.1.0 build 258902 client, VMware ESX 4.1.0 build 260247 server.
The version of the SQL driver that I am running on the terminal server is
SQL Server Driver 2000.86.3959.00
I am doing a DSNless connection in my own reattachment code so there is no
DSN that I can tweak the ODBC timeout in.
I have done another project for related clients where the client and server
where on different virtual machines and these types of error wreaked havoc
on the project to the point where they took it to another contractor who
recoded it to SQL stored procedures with a web front end.
I don't want to lose another project to the same flakiness in virtual
machines so PLEASE, PLEASE can someone help me.
I have spent several hours looking through Google but can't find an answer
that fits my situations.
I'm afraid that the IT support people are no help - they just say things
like "What do you expect - Access is too resource hungry and is pushing the
virtual machine too hard"
Many thanks in advance
Tony Epton