ODBC Call Failed

Hi Everyone.
Please i need some help on the error because i am very badly stucked in my project.
I had access 97 project and now i want to upgrade the access97 database to sql server 2005. I had left the interface in access 97 till now. However most of the functions are working but recently iam getting too much ODBC Call Failed error.
My sql 2005 is being installed in windows 2003 server. I am using the linked table to link the database(ODBC). I am using office 97 in windows xp platform. when i enter one record in the table it works fine but when i try to enter the multiple records...first record is sucess but when it goes to second record it gives call failed error...
iam giving u the code sample where iam getting the error..

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset( _
"SELECT price,vat FROM Publication_Master " & _
"WHERE PubCode = '1'", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset( _
"SELECT TransactionDate, CustomerCode, PubCode " & _
"FROM StreetSaleRcpt " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransactionDate = #" & tmpDate & "#", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset( _
"SELECT TransDate, CustomerCode, PubCode " & _
"FROM Transactions " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransDate = #" & tmpDate & "# AND TransType = 'RE'", _
dbOpenDynaset, dbReadOnly)

Set rst4 = dbs.OpenRecordset("StreetSaleRcpt", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst4
.AddNew
![TransactionDate] = Me.txtDate
![PaymentDate] = Me.txtPaymentDate
![PeriodMonth] = Me.txtPeriod
![PeriodYear] = Me.txtYear
'![TransactionMonth] = Me.txtTransMonth
'![TransactionYear] = Me.txtTransYear
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![BatchNum] = Me.txtBatchNo
![Supply] = Me.txtSupply
![Returns] = Me.txtReturns
![ReceiptNo] = Me.txtRcptNo
![Amount] = format(Me.txtAmount, "0.00")
![Updated] = False
![RcptOnly] = False
.Update
End With
' rst.Close
rst4.Close
dbs.Close
Set dbs = CurrentDb


Set rst4 = dbs.OpenRecordset("Transactions", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst4
.AddNew
![TransDate] = Me.txtDate
![TransType] = "RE"
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![Period] = Me.txtPeriod
![Year] = Me.txtYear
![Copies] = Me.txtReturns
'![TotalAmount] = format(Me.txtAmount, "0.00")

![TransAmount] = format(Me.txtReturns * Price, "0.00")
If rst5![Vat] = True Then
![VatAmount] = format(Me.txtReturns * Vat, "0.00")
Else
![VatAmount] = 0
End If
![TotalAmount] = ![TransAmount] + ![VatAmount]
![Update] = False
![BatchNo] = Me.txtBatchNo
.Update
End With
rst4.Close
dbs.Close
Set dbs = CurrentDb

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset( _
"SELECT price,vat FROM Publication_Master " & _
"WHERE PubCode = '1'", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset( _
"SELECT TransactionDate, CustomerCode, PubCode " & _
"FROM StreetSaleRcpt " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransactionDate = #" & tmpDate & "#", _
dbOpenDynaset, dbReadOnly)

Set rst4 = dbs.OpenRecordset( _
"SELECT TransDate, CustomerCode, PubCode " & _
"FROM Transactions " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransDate = #" & tmpDate & "# AND TransType = 'RE'", _
dbOpenDynaset, dbReadOnly)
(****I get error over here************)
Set dbs = CurrentDb
'Set dbs = CurrentDb
Set rst6 = dbs.OpenRecordset("StreetSaleRcpt", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst6
.AddNew
![TransactionDate] = Me.txtDate2
![PaymentDate] = Me.txtPaymentDate
![PeriodMonth] = Me.txtPeriod
![PeriodYear] = Me.txtYear
'![TransactionMonth] = Me.txtTransMonth
'![TransactionYear] = Me.txtTransYear
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![BatchNum] = Me.txtBatchNo
![Supply] = Me.txtSupply2
![Returns] = Me.txtReturns2
![ReceiptNo] = Me.txtRcptNo
![Amount] = format(Me.txtAmount2, "0.00")
![Updated] = False
![RcptOnly] = False
.Update
End With
' rst.Close
rst6.Close
dbs.Close
Set dbs = CurrentDb
Set rst5 = dbs.OpenRecordset("SELECT vat FROM Customer_Master" & _
" WHERE customercode = '" & Me.cboCustCode & "'", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset("Transactions", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst4
.AddNew
![TransDate] = Me.txtDate2
![TransType] = "RE"
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![Period] = Me.txtPeriod
![Year] = Me.txtYear
![Copies] = Me.txtReturns2
'![TotalAmount] = format(Me.txtAmount, "0.00")

![TransAmount] = format(Me.txtReturns2 * Price, "0.00")
If rst5![Vat] = True Then
![VatAmount] = format(Me.txtReturns2 * Vat, "0.00")
Else
![VatAmount] = 0
End If
![TotalAmount] = ![TransAmount] + ![VatAmount]
![Update] = False
![BatchNo] = Me.txtBatchNo
.Update
End With
rst4.Close
Else
MsgBox "Customer was not supplied Fiji Times on " & tmpDate & " or returned all supply. No receipt entry required"
Me.txtSupply2.SetFocus
GoTo exit_sub
End If
End If



If trans1 = True And trans2 = True And trans3 = True Then

tmpDate = format(Me.txtDate3, "mm/dd/yyyy")

If Me.txtSupply3 - Me.txtReturns3 >= 0 Then
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset( _
"SELECT price,vat FROM Publication_Master " & _
"WHERE PubCode = '1'", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset( _
"SELECT TransactionDate, CustomerCode, PubCode " & _
"FROM StreetSaleRcpt " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransactionDate = #" & tmpDate & "#", _
dbOpenDynaset, dbReadOnly)

If Not (rst4.BOF = True And rst4.EOF = True) Then
MsgBox "Transaction already exists for " & tmpDate & " .Multiple entries not allowed for the same customer"
GoTo exit_sub
End If
rst4.Close

Set rst4 = dbs.OpenRecordset( _
"SELECT TransDate, CustomerCode, PubCode " & _
"FROM Transactions " & _
"WHERE CustomerCode = '" & Me![cboCustCode] & "' AND PubCode = '1' AND TransDate = #" & tmpDate & "# AND TransType = 'RE'", _
dbOpenDynaset, dbReadOnly)

If Not (rst4.BOF = True And rst4.EOF = True) Then
MsgBox "Transaction already exists. Multiple entries not allowed for the same customer"
GoTo exit_sub
End If
rst4.Close


With rst1
If (.RecordCount >= 1) Then
.MoveFirst
Price = ![Price]
Vat = ![Vat]
Else
MsgBox "Publication not Found.", vbCritical, "Error"
GoTo exit_sub
End If
End With
rst1.Close


Set rst4 = dbs.OpenRecordset("StreetSaleRcpt", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst4
.AddNew
![TransactionDate] = Me.txtDate3
![PaymentDate] = Me.txtPaymentDate
![PeriodMonth] = Me.txtPeriod
![PeriodYear] = Me.txtYear
'![TransactionMonth] = Me.txtTransMonth
'![TransactionYear] = Me.txtTransYear
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![BatchNum] = Me.txtBatchNo
![Supply] = Me.txtSupply3
![Returns] = Me.txtReturns3
![ReceiptNo] = Me.txtRcptNo
![Amount] = format(Me.txtAmount3, "0.00")
![Updated] = False
![RcptOnly] = False
.Update
End With
' rst.Close
rst4.Close

Set rst5 = dbs.OpenRecordset("SELECT vat FROM Customer_Master" & _
" WHERE customercode = '" & Me.cboCustCode & "'", _
dbOpenDynaset, dbReadOnly)


Set rst4 = dbs.OpenRecordset("Transactions", dbOpenDynaset, dbSeeChanges, dbPessimistic)
With rst4
.AddNew
![TransDate] = Me.txtDate3
![TransType] = "RE"
![CustomerCode] = Me.cboCustCode
![PubCode] = "1"
![Period] = Me.txtPeriod
![Year] = Me.txtYear
![Copies] = Me.txtReturns3
'![TotalAmount] = format(Me.txtAmount, "0.00")

![TransAmount] = format(Me.txtReturns3 * Price, "0.00")
If rst5![Vat] = True Then
![VatAmount] = format(Me.txtReturns3 * Vat, "0.00")
Else
![VatAmount] = 0
End If
![TotalAmount] = ![TransAmount] + ![VatAmount]
![Update] = False
![BatchNo] = Me.txtBatchNo
.Update
End With
rst4.Close
Else
MsgBox "Customer was not supplied Fiji Times on " & tmpDate & " or returned all supply. No receipt entry required"
Me.txtSupply3.SetFocus
GoTo exit_sub
End If
End If

any suggestion will be really appreciated........
Looking forward for your sugesstion..........

Thanks in advance

Rai