I am trying to convert code that I got off Sandbox. The code allows you to import data from an Access Table. I want to import data from another SQL table besides access. My table name is dbo.openworkor How do I account for the login and password of the table? Do I need to change constants or statements. Is it possible to run this from a scheduled agent instead of an action? Thanks in advance for your help.
Dim session As New NotesSession
Dim ws As New NotesUIWorkspace
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
REM Miscellaneous module variables
Dim msg As String
Dim FieldCount As Integer
Dim Status As Integer
REM MS Access Database declarations
Const adbFileName = "northwind" ' This is the DSN Name as registered in ODBC
Const adbTableName = "CUSTOMERS" ' This is a table in the northwind database
REM Notes Database declarations
Const ndbViewName = "By Company Name" ' This is an existing View in the target database
Const ndbFormName = "Customer" ' This is an existing Form in the target database
REM Instantiate the major Notes objects
Set db = session.CurrentDatabase
Set view = db.GetView( ndbViewName )
REM Connect to the MS Access Database. Throw an error if it fails
status = con.ConnectTo( adbFileName )
If Not con.IsConnected Then
Messagebox "Could not connect to " & adbFileName & " database -- Did you register the ODBC Data Source???",, "Error"
Exit Sub
End If
If con.GetError <> DBstsSUCCESS Then
Messagebox con.GetExtendedErrorMessage,, "Connection Error - " & con.GetError & " " & con.GetErrorMessage
Exit Sub
End If
Print "Connected to " & adbFileName & " database" ' Update the Notes Client Status Bar if we're okay
REM If we got this far, we must be connected, so just for grins, let’s show the user all the fields in this table.
REM Note: “fields” has not been declared, so it will be a Variant Array – that’s what we want
fields = con.ListFields( adbTableName )
msg = adbTableName & " contains the following fields:" & Chr(10)
For FieldCount = Lbound( fields ) To Ubound( fields )
msg = msg & Chr(10) & fields( FieldCount )
Next
REM Okay, let’s display the Field List to the User…
Messagebox msg,, "Fields from the " & adbFileName & " database "