I’m Notes Admin in my company trying to do a bit of development.I’m converting a VB application that uses a Access database into a Notes application.
I need to copy the existing data in the Access database to the Notes database.
I think an agent might be able to do this If anyone has an agent that is written to do this I’ll be realy greatfull
Subject: Agent to Copy Data from a Access database to a Notes Database
If you want a LotusScript agent then try something like this!
Sub Initialize
'This routine takes an MS ACCESS database and creates a Notes
'Database for each table in that database that is not a Systems table
'ie all tables not starting with “MSys”
'To create the NEW Notes Databases you must have “BlankTemplate.ntf” in the root directory
'of the server you are creating these databases. BlankTemplate.ntf, can be created by Creating a new
'database with no template but naming it “BlanlTempate.ntf”. If you create databases without using a template
'you will be unable to open them as a default view is not presnt in the design elements.
'The Naming of the NEW Notes databases are as follows:
’ “AccImp” + AccessTableName + " Access Table.nsf"
'Each time the routine is run…any existing databases with the above name will be
'overwritten.
'John Kirkby
'February 2002
Msgbox("<<<< GetAccessData Agent Started >>>>")
Dim session As New notessession
Dim db As notesdatabase
Dim dbnew As NotesDatabase
Set db = session.currentdatabase
Dim mytext As String
Dim mycount As Integer
Dim ItemA As NotesItem
'========================================================
'To use a different Notes Template to create the new databases, change
' "BlankTemplate.ntf" to another valid Notes Template
Dim template As New NotesDatabase( db.server,"BlankTemplate.ntf" )
'========================================================
'Set up MS Access DB location and Object
mytext = "H:\Address Book1.mdb"
'========================================================
Set Acc = CreateObject("Access.Application.8")
Acc.Opencurrentdatabase(mytext)
Set dbs = Acc.CurrentDB()
numOfTables = dbs.TableDefs.Count
'Loop through all the Tables, opening each one and creating a db for each new one.
count1 = 0
Do While count1 <> numOfTables
tdfname = dbs.TableDefs(count1).Name
'Now ignore all the system tables starting with "MSys"
If Left(tdfname,4) <> "MSys" Then ' this is not a systems table
Set tdf = dbs.Opentable(tdfname)
'Create each database, then loop through all the records
tdfdbname = "AccImp" + tdfname + ".nsf"
Set dbnew = session.GetDatabase( db.server, tdfdbname)
'Check to see if exists or is open. If so delete it
If dbnew.IsOpen Then
'If It exists...Delete It and create again
Call dbnew.Remove
Else
Call dbnew.open( db.server, tdfdbname)
If dbnew.IsOpen Then
'If It exists...Delete It and create again
Call dbnew.Remove
End If
End If
'Having got rid of any old copies, now create a new db
Set dbnew = template.CreateFromTemplate( db.server, tdfdbname, True )
dbnew.Title = tdfname + " Access Table"
'Loop through Each Record
totalrecords = tdf.RecordCount
Do While totalrecords > 0
totalrecords = totalrecords - 1
Print "Record Number " + Str(totalrecords)
Print ""
'Create New Notes Document
Set doc = New NotesDocument( dbnew )
doc.form = "AccessForm"
doc.RecordNo = Str(totalrecords)
'Loop through each field
mycount = tdf.fields.count
Do While mycount > 0
mycount = mycount - 1
Set fld = tdf.Fields( mycount )
Print fld.Name
Print fld.Value
'Write field name and values to new Notes doc
Set ItemA = doc.AppendItemValue( fld.Name, fld.value )
Loop
tdf.MoveNext
Call doc.Save( False, True )
Loop
End If
count1 = count1 + 1
Loop
'Closes down MS Access
Print( "Finished")
Acc.Quit
Msgbox("<<<< GetAccessData Agent Finished >>>>")
End Sub
Subject: Agent to Copy Data from a Access database to a Notes Database
Use the Lotus Enterprise Integrator (LEI) – read about it here. http://www.lotus.com/products/product4.nsf/wdocs/leihomepage Create a replication activity to migrate data from Access (using the ODBC or OLE DB connector) to Notes. Schedule it to run daily at the appropriate times. Use Order metaconnectors on both sides to make sure the Notes data are sorted (this shouldn’t be necessary but apparently is in current versions; we’re investigating).
You could of course program the same operation using the LC LSX and make it a couple of scheduled agents. LEI just makes it easier. Refer to What’s wrong with my code?? for a description of the basic replication algorithm.