I have the code shown at the bottom of this post in a program which moves
records to a history database and then deletes them from the 'live'
database.
The databases are SQL Server 2000. If I connect using ODBC, the code
works
perfectly. This is the ODBC connection string:
gsConnectionstringChecks = "driver={SQL Server};" _
& "server=" & gsServer & ";uid=" & gsSQLLoginName & ";pwd=" _
& gsSQLPassword & ";database=ChecksSQL;Initial Catalog=ChecksSQL;"
If I connect using OLE, I get the following error: "Error -2147217906:
Bookmark is invalid." This error occurs at the end of the process (last
record I think). The error is occurring on the grsChecksChecks.Movenext
statement.
The OLE connection string is as follows:
gsConnectionstringChecks = "provider=sqloledb;" _
& "server=" & gsServer & ";uid=" & gsSQLLoginName & ";pwd=" _
& gsSQLPassword & ";database=ChecksSQL;Initial Catalog=ChecksSQL;"
Anyone have any idea why this is happening? As stated, ODBC works fine,
but
I prefer using the SQLOLEDB provider.
Any help would be greatly appreciated.
CODE SNIPPET:
gsSelectionStatement = "SELECT * FROM [checks] WHERE [company] = " _
& giCompany & " and [check_date] <= '" & DateValue(vDate) & "'"
If iBank <> 0 Then
gsSelectionStatement = gsSelectionStatement & " and [bank] = " & iBank
End If
If optNo Then
gsSelectionStatement = gsSelectionStatement & " and [reconciled] = -1"
End If
grsChecksChecks.Open gsSelectionStatement, gcnnChecks, adOpenKeyset, _
adLockOptimistic
If Not grsChecksChecks.EOF Then
Do Until grsChecksChecks.EOF
ChecksShowProgress lCurRec, lTotalRecs
gsSelectionStatement = "SELECT * FROM [checks] WHERE [company] = " _
& grsChecksChecks("company") & " and [bank] = " _
& grsChecksChecks("bank") & " and [check_num] = " _
& grsChecksChecks("check_num") & " and [void] = " _
& grsChecksChecks("void")
gr****storyChecks.Open gsSelectionStatement, gcnnHistory,
adOpenKeyset, _
adLockOptimistic
If Not gr****storyChecks.EOF Then
'check with same key already exists in history file
'add check number to list of dup checks
If iBank = 0 Then
lstDupChecks.AddItem grsChecksChecks("bank") & " - " _
& grsChecksChecks("check_num")
Else
lstDupChecks.AddItem grsChecksChecks("check_num")
End If
lDups = lDups + 1 'increment dup checks counter
Else
'add this check to history database and delete it from check
database
gr****storyChecks.AddNew
gr****storyChecks("company") = grsChecksChecks("company")
gr****storyChecks("bank") = grsChecksChecks("bank")
gr****storyChecks("check_num") = grsChecksChecks("check_num")
If Not IsNull(grsChecksChecks("type")) Then
gr****storyChecks("type") = grsChecksChecks("type")
Else
gr****storyChecks("type") = " "
End If
If Not IsNull(grsChecksChecks("manual_check")) Then
gr****storyChecks("manual_check") =
grsChecksChecks("manual_check")
Else
gr****storyChecks("manual_check") = 0
End If
If Not IsNull(grsChecksChecks("vendor")) Then
gr****storyChecks("vendor") = grsChecksChecks("vendor")
Else
gr****storyChecks("vendor") = 0
End If
If Not IsNull(grsChecksChecks("vendor_name")) Then
gr****storyChecks("vendor_name") = grsChecksChecks("vendor_name")
Else
gr****storyChecks("vendor_name") = " "
End If
If IsDate(grsChecksChecks("check_date")) Then
gr****storyChecks("check_date") = grsChecksChecks("check_date")
End If
If Not IsNull(grsChecksChecks("amount")) Then
gr****storyChecks("amount") = grsChecksChecks("amount")
Else
gr****storyChecks("amount") = 0
End If
If Not IsNull(grsChecksChecks("amount_for_1099")) Then
gr****storyChecks("amount_for_1099") = _
grsChecksChecks("amount_for_1099")
Else
gr****storyChecks("amount_for_1099") = 0
End If
If Not IsNull(grsChecksChecks("reconciled")) Then
gr****storyChecks("reconciled") = grsChecksChecks("reconciled")
Else
gr****storyChecks("reconciled") = 0
End If
If IsDate(grsChecksChecks("date_reconciled")) Then
gr****storyChecks("date_reconciled") = _
grsChecksChecks("date_reconciled")
End If
gr****storyChecks("void") = grsChecksChecks("void")
If Not IsNull(grsChecksChecks("gl_ctl_num")) Then
gr****storyChecks("gl_ctl_num") = grsChecksChecks("gl_ctl_num")
Else
gr****storyChecks("gl_ctl_num") = 0
End If
gr****storyChecks.Update
lNum = lNum + 1
gsSelectionStatement = "SELECT * FROM [invoices] WHERE [company] =
" _
& grsChecksChecks("company") & " and [bank] = " _
& grsChecksChecks("bank") & " and [check_num] = " _
& grsChecksChecks("check_num") & " and [void] = " _
& grsChecksChecks("void")
grsChecksInvoices.Open gsSelectionStatement, gcnnChecks, _
adOpenKeyset, adLockOptimistic
If Not grsChecksInvoices.EOF Then
Do Until grsChecksInvoices.EOF
gr****storyInvoices.AddNew
gr****storyInvoices("company") = grsChecksInvoices("company")
gr****storyInvoices("bank") = grsChecksInvoices("bank")
gr****storyInvoices("check_num") =
grsChecksInvoices("check_num")
gr****storyInvoices("void") = grsChecksInvoices("void")
If IsDate(grsChecksInvoices("invoice_entry_date")) Then
gr****storyInvoices("invoice_entry_date") = _
grsChecksInvoices("invoice_entry_date")
End If
gr****storyInvoices("invoice_entry_time") = _
grsChecksInvoices("invoice_entry_time")
If Not IsNull(grsChecksInvoices("invoice_num")) Then
gr****storyInvoices("invoice_num") = _
grsChecksInvoices("invoice_num")
Else
gr****storyInvoices("invoice_num") = " "
End If
If Not IsNull(grsChecksInvoices("invoice_amount")) Then
gr****storyInvoices("invoice_amount") = _
grsChecksInvoices("invoice_amount")
Else
gr****storyInvoices("invoice_amount") = 0
End If
gr****storyInvoices.Update
grsChecksInvoices.Delete
grsChecksInvoices.MoveNext
Loop
End If
grsChecksInvoices.Close
grsChecksChecks.Delete
End If
gr****storyChecks.Close
grsChecksChecks.MoveNext '<<< ERROR OCCURS HERE'
Loop
End If
|
