VB code accessing Lotus/Domino and pastes into SQL fails at 244 records

Posted on 2009-05-15
Last Modified: 2013-12-20
The error I'm getting is:
"[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"

When I remove the two records that appear to be the problem it does fine.  When those two are still gone and I add a new record, it goes back to the error.  So then I removed the two bad ones, copy 2 that I saw had successfully inserted into SQL and pasted them in the Domino database and it still gives me that error.

That's making me think that it's just stopping at 244 rather than record data being bad?  I know that max query results by default in Notes are 250 and there is no max results type of wording in my connection string so that may be the issue but I'm not sure how to word that.

Any ideas would be great!
Private Sub GetLicensingNotes_Click()


Dim connNotes As ADODB.Connection

Dim connSQL As ADODB.Connection

Dim rsSQL As ADODB.Recordset

Dim rsNotes As ADODB.Recordset

Dim count, j As Integer

Dim tmpSQL As String


count = 0

j = 0


'connect to the CPAS SQL Database

Set connSQL = New ADODB.Connection

connSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CPAS;Data Source=VFRASER\SQLEXPRESS"



'connect to the Lotus Notes database

Set connNotes = New ADODB.Connection

connNotes.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DominoCPASLicense34;Mode=ReadWrite"



'set the SQL recordset & make sure it can be added to

Set rsSQL = New ADODB.Recordset

rsSQL.CursorLocation = adUseServer

rsSQL.Open "SELECT * FROM Licensing", connSQL, adOpenDynamic, adLockOptimistic


'get the Notes table so that it can be looped thru and added to the SQL table

Set rsNotes = New ADODB.Recordset

rsNotes.CursorLocation = adUseServer

Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")



While Not rsNotes.EOF 'I never could get this to recognize the EOF on my Notes so had to hard code a stop using count

'fill in the fields

count = count + 1 'count how many records have been processed

    With rsSQL


        !CompanyNo = rsNotes.Fields("CompanyNo").Value

        !CompanyName = rsNotes.Fields("Name").Value

        !Contact = rsNotes.Fields("Contact").Value

        !Address1 = rsNotes.Fields("Address1").Value

        !Address2 = rsNotes.Fields("Address2").Value

        !City = rsNotes.Fields("City").Value

        !State = rsNotes.Fields("State").Value

        !Zip = rsNotes.Fields("Zip").Value

        !WorkPhone = rsNotes.Fields("Phone").Value

        !FaxNumber = rsNotes.Fields("Fax").Value

        !EmailNo = rsNotes.Fields("EmailNo").Value

        !PrimaryWebsite = rsNotes.Fields("WebSite").Value

        !LicenseType = rsNotes.Fields("LicenseType").Value

        !StatusOfLicenseProfile = rsNotes.Fields("Status").Value

        !OfficeDaysHours = rsNotes.Fields("OfficeDaysHours").Value

        !LicenseLookup = rsNotes.Fields("YorN").Value

        !LicenseLookupURL = rsNotes.Fields("WebSite_1").Value

        !LicenseDuration = rsNotes.Fields("RenewalTime").Value

        !WorkWLicInProgress = rsNotes.Fields("LicenseProcess").Value

        !CostAllLicenses = rsNotes.Fields("Fees").Value

        !TempLicenseAvailable = rsNotes.Fields("TempLicense").Value

        !TimeToGetLicense = rsNotes.Fields("Time").Value

        !TimeToGetTempLicense = rsNotes.Fields("TempTime").Value

        !MethodsAcceptablePayment = rsNotes.Fields("Payment").Value

        !VerificationFees = rsNotes.Fields("Data1").Value

        !ApplicationAvailableOnWebsite = rsNotes.Fields("YorN2").Value

        !LicenseNecessary = rsNotes.Fields("NeededLicense").Value

        !DetailedInformation = rsNotes.Fields("DetailedInfo").Value

        !ApplicationAttachment = rsNotes.Fields("Comments").Value

        !RenewalForm = rsNotes.Fields("YorN_1").Value

        !TimeToGetRenewalLicense = rsNotes.Fields("Time_1").Value

        !RenewalFormLink = rsNotes.Fields("WebSite_1_1").Value

        !CostOfRenewalLicense = rsNotes.Fields("Fees_1").Value

        !MethodsAcceptablePaymentRen = rsNotes.Fields("Payment_1").Value

        !RenewalComments = rsNotes.Fields("comments_1").Value

        !PostingStatus = rsNotes.Fields("NSPub").Value

        !DescriptionUserSeesForMainWebsiteLink = rsNotes.Fields("txtMainWebsite").Value

        !MainWebsiteLink = rsNotes.Fields("lnkMainWebsite").Value

        !DescriptionUserSeesRenewalLink = rsNotes.Fields("txtWebsite2").Value

        !RenewalWebsiteLink = rsNotes.Fields("lnkWebsite2").Value



    End With



'I have to figure out why Lotus can't realize it is at the end of the recordset ...

If count < 244 Then

    rsNotes.MoveNext            'this gives me the error "[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"



    GoTo Done

End If






MsgBox "Licensing is now populated", vbOKOnly, "Process Complete"






Set rsSQL = Nothing

Set rsNotes = Nothing

Set connSQL = Nothing

Set connNotes = Nothing


End Sub

Open in new window

Question by:VanessaTamora
  • 4
  • 2

Author Comment

ID: 24400569
Playing with my Domino database, it turns out my VB always crashes two record short of the number that are actually in the database.  I removed a bunch of records so that I only had 106 records and it crashed on record 104 ... tested it with many variations and it always crashes 2 short of the end???
LVL 46

Accepted Solution

Sjef Bosman earned 500 total points
ID: 24401764
I couldn't find any reference to the max query results you mention. See this for some lilitations:

If you are right, you could try to get rows  by blocks of 200, for instance, using LIMIT offset,rowcount

If there are still 2 rows missing, you might not have removed the problem document yet. Can you make a list of the rows you get with NotesSQL, and compare the results with the documents as displayed in the Notes view? And then inspect the documents that do not appear in NotesSQL, what are the differences?

Author Comment

ID: 24406775
You are right about not removing the problem.  I selected one field that I knew was populated for every record properly and it selected and inserted without issue.  T

Worked: Set rsNotes = connNotes.Execute("SELECT CompanyNo FROM Licensing_Profile")
I'll just add fields back into the working code a group at a time until I find the culprit.  (I had also remarked out all the corresponding insert statements which I'll leave that way until I get the above line working with out crashing).

Doesn't work: Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")

To test it a little more, I copied all 246 documents so I had 492 documents and the altered "select" statement inserted perfectly.

Thanks!  I'm going to mark this as closed since it makes perfect sense and works like a charm with only 1 field thus proving out that the problem exists in one of the Notes fields.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Closing Comment

ID: 31582135
Thanks for all your help!

Author Comment

ID: 24406994
By the way, it was my Lotus field named "Time" ... I probably should have looked for a reserved word field right off the bat!
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24409476
Time... Maybe there was one document with a field "Time" that didn't have date/time contents?? Because, after all, it worked on the other 244 documents. Anyway, glad you found it!

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 35
How to set the row selection as it was prior leaving the datagrid in vb6 3 34
Replace Dates in query 14 11
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now