From LotusNotes to MDB

Hello !

I got a problem to write script for migrating data from lotusnotes to MDB file. my exsample script is as you may see below:

My problem is that if my table in MDB file has a additional field and this field's datatype is AutoNumber. when I try to run, it does not add new record to MDB file. But once I remove this Autonumber field, my script can add new record to MDB file.

Can anyone help to advise why it's so ?

 Dim result As New ODBCResultSet
     Dim con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim temp As String
     Set qry.Connection = con
     Set result.query = qry
     qry.sql = "SELECT * FROM BOM"
     Call result.Setvalue("PDRNo", "TEST3")
     Set result.query = Nothing
Who is Participating?
mbonaciConnect With a Mentor Commented:
Hi mpitak,
that sounds like normal behaviour to me.

The autonumber in relational database gets incremented in front end, when you go to new row.
That means that when you add new row using back end script the database gets confused.

If it's a one time transfer disable autonumber and import your data, then enable autonumber.

If it's to be done regularily disable autonum and assign it using your script.
To get current number of rows use (if I remember right) "SELECT COUNT * FROM BOM"

Hope this helps,
mpitakAuthor Commented:

Thanks for you advise. However, it will be troublesome to disable as this MDB file is also used by other VB.NET program. So this autonumber is used by that VB program as well.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.