why does my sql server randomly bomb out on .Update? but the transactions go through

the access database has some vb code that allows transactions on the sql server (7.0). when moving records to a new database it occasionally bombs on the .Update line in the below code. the updates do go through correctly and there is very low traffic on the sql server at all times -- the sql server is in our subnet. why? the code is old and has never given us any trouble. its so unpredicatble but seemingly harmless that i'm not too worried, but i would love to fix it. thanks in advance, chris

here's a code snippet -- it bombs on .update

With rsDest
       
            .AddNew
       
            ![HonorsCode] = rsSource![HonorsCode]
            ![SSN] = rsSource![SSN]
            ![Last_Name] = rsSource![Last_Name]
            ![MidInitial] = rsSource![MidInitial]
            ![First_Name] = rsSource![First_Name]
            ![Address] = rsSource![Address]
            ![City] = rsSource![City]
            ![State] = rsSource![State]
            ![Zipcode] = rsSource![Zipcode]
            ![Country] = "US"
            ![Phone] = rsSource![Phone]
            ![E_mail] = rsSource![E_mail]
            ![Birthdate] = rsSource![Birthdate]
            ![Ethnicity] = ""
            ![EthnicCode] = rsSource![EthnicCode]
            ![Sex] = rsSource![Sex]
            ![College_Code] = rsSource![College_Code]
            ![Curriculum_Code] = rsSource![Curriculum1_Code]
            ![Year_Term_Graduated] = yearTermValue
            ![CorrectAddress] = -1
            ![Graduate_School_1] = ""
            ![Graduate_Degree_1] = ""
            ![Grad_Field_1] = ""
            ![Year_Grad_Degree_1] = ""
            ![Graduate_School_2] = ""
            ![Graduate_Degree_2] = ""
            ![Grad_Field_2] = ""
            ![Year_Grad_Degree_2] = ""
            ![Current_Employer] = ""
            ![Profession] = ""
            ![Work_Phone] = ""
            ![Maiden_Name] = ""
            ![Spouse] = ""
            ![Number_of_Children] = 0
            ![Last_Update] = Null
            ![Last_Address] = ""
            ![Notes] = ""
            ![Donor] = ""
               
            .Update
        End With
oldhemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RDWaibelCommented:
have you added code to actually trap the error to find out what and why it is failing?

is rsDest an ADODB recordset?
oldhemAuthor Commented:
it is a ADODB recordset.

the error is so random that its tough to trap, and the updates seem to go through anyway. its more of a nuisance right now.
RDWaibelCommented:
it could be a timeout issue...
Also, when you open rsDest, are you setting the lock type?

try:
ON ERROR GOTO UpdateError
With rsDest
       
            .AddNew
       
            ![HonorsCode] = rsSource![HonorsCode]
            ![SSN] = rsSource![SSN]
            ![Last_Name] = rsSource![Last_Name]
            ![MidInitial] = rsSource![MidInitial]
            ![First_Name] = rsSource![First_Name]
            ![Address] = rsSource![Address]
            ![City] = rsSource![City]
            ![State] = rsSource![State]
            ![Zipcode] = rsSource![Zipcode]
            ![Country] = "US"
            ![Phone] = rsSource![Phone]
            ![E_mail] = rsSource![E_mail]
            ![Birthdate] = rsSource![Birthdate]
            ![Ethnicity] = ""
            ![EthnicCode] = rsSource![EthnicCode]
            ![Sex] = rsSource![Sex]
            ![College_Code] = rsSource![College_Code]
            ![Curriculum_Code] = rsSource![Curriculum1_Code]
            ![Year_Term_Graduated] = yearTermValue
            ![CorrectAddress] = -1
            ![Graduate_School_1] = ""
            ![Graduate_Degree_1] = ""
            ![Grad_Field_1] = ""
            ![Year_Grad_Degree_1] = ""
            ![Graduate_School_2] = ""
            ![Graduate_Degree_2] = ""
            ![Grad_Field_2] = ""
            ![Year_Grad_Degree_2] = ""
            ![Current_Employer] = ""
            ![Profession] = ""
            ![Work_Phone] = ""
            ![Maiden_Name] = ""
            ![Spouse] = ""
            ![Number_of_Children] = 0
            ![Last_Update] = Null
            ![Last_Address] = ""
            ![Notes] = ""
            ![Donor] = ""
               
            .Update
        End With

UpdateError:
  if err > 0 then
    msgbox err.description
  end if
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

oldhemAuthor Commented:
i figured it might be a timeout issue. is that as unpredictable as it sounds? and as benevolent?
oldhemAuthor Commented:
also, i'm not setting the lock type
RDWaibelCommented:
>>i  figured it might be a timeout issue. is that as unpredictable as it sounds? and as benevolent? <<

Yes, it is, most of the time! :D

if I could see the code used to connect to the server and to open the recordset, I may be able to solve the issue.
oldhemAuthor Commented:
Set rsSource = db.OpenRecordset("dbo_PurgatoryStudentsView")
Set rsDest = db.OpenRecordset("dbo_Graduates")
oldhemAuthor Commented:
Dim db As Database
Dim rsSource As Recordset
Dim rsDest As Recordset
Dim index As Integer

Set db = CurrentDb
RDWaibelCommented:
ok...more than likely a timeout on the linked table connection.

now, options:
1)  Use an ADODB Connection to the server to run the insert:
dim CN as new adodb.connection
With CN
  .Provider = "sqloledb"
  .Properties("Data Source").Value = "{YourServer}"
  .Properties("Initial Catalog").Value = "{DatabaseName}"
  .Properties("User ID").Value = "{UserID like sa}"
  .Properties("Password").Value = "{Password for user}"
  .CursorLocation = adUseClient
  .CommandTimeout = 3600
  .Open
end wtih
ssql = "Insert into Graduates VALUES ( '" & rsSource![HonorsCode] & "', '" &  rsSource![SSN] & "', '" & rsSource![Last_Name] & "', '" {etc for all fields} & ")"
cn.execute ssql
cn.close
 
/ROb
RDWaibelCommented:
Option 2:
still build this:
ssql = "Insert into Graduates VALUES ( '" & rsSource![HonorsCode] & "', '" &  rsSource![SSN] & "', '" & rsSource![Last_Name] & "', '" {etc for all fields} & ")"

then
db.execute ssql
rsdest.requery
RDWaibelCommented:
TYPO!

ssql = "Insert into dbo_Graduates VALUES ( '" & rsSource![HonorsCode] & "', '" &  rsSource![SSN] & "', '" & rsSource![Last_Name] & "', '" {etc for all fields} & ")"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.