Highlighted
Absent Member.
Absent Member.
551 views

[archive] MS Access

[Migrated content. Thread originally posted on 30 April 2008]

Hello,
Sorry to monopolize the board. I was wondering if there are any sample programs that move data from vision files to an Access table.

Thanks
0 Likes
4 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] MS Access

The best solution is probably to use COM:

IDENTIFICATION DIVISION.
PROGRAM-ID. ExModifyADO.
*====================
*
* Copyright (c) 1996-2006 by Acucorp, Inc. Users of ACUCOBOL
* may freely modify and redistribute this program.
*
* The purpose of this application is to illustrate use of the MSADO
* technology for database access
*
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
COPY "MSADO.def".
.
DATA DIVISION.
WORKING-STORAGE SECTION.

77 hConnection HANDLE OF Connection.
77 hRecordset HANDLE OF Recordset.
77 ConnStr PIC X(256).
77 WS-ConnStat PIC S9(9).
PROCEDURE DIVISION.
Main section.
Main-001.
*Create an instance of connection
CREATE Connection OF ADODB
HANDLE IN hConnection.
*Create an instance of recordset
Create Recordset OF ADODB
HANDLE IN hRecordset.
*Set up driver, database, path for connection
STRING "Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=apiv2db.mdb;"
"DefaultDir=F:\APIV2\DocExamples\MSADO;"
"ReadOnly=False"
DELIMITED BY SIZE INTO ConnStr.
*Connect open) database
MODIFY hConnection @Open(ConnStr).
INQUIRE hConnection State IN WS-ConnStat.
*Did we open alright?
IF WS-ConnStat NOT = adStateOpen
GO TO MAIN-900
END-IF.
*Connect the recordset to the table we want to work with
*Include keys, optimistic locking, use existing connection
MODIFY hRecordset @Open("Contacts", hConnection,
adOpenKeySet, adLockOptimistic, adCmdTable).
*Add a record, add values to fields, commit
MODIFY hRecordset
AddNew()
fields::Item("FirstName")::value = "Barbara"
fields::Item("LastName")::value = "Streisand"
Update().
' Delete the new record because this is a demonstration
MODIFY hConnection Execute(
"DELETE FROM Contacts WHERE LastName = 'Streisand'")
. .

ACCEPT OMITTED.
*Close recordset
MODIFY hRecordset @Close().
*Close connection
MODIFY hConnection @Close().
MAIN-900.
*Finish up.
DESTROY hRecordset.
DESTROY hConnection.
GOBACK.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] MS Access

MODIFY hConnection Execute(
"DELETE FROM Contacts WHERE LastName = 'Streisand'")

How would you change the above lines to use a variable instead of a hard-coded value to delete the records?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] MS Access

MODIFY hConnection Execute(
"DELETE FROM Contacts WHERE LastName = 'Streisand'")

How would you change the above lines to use a variable instead of a hard-coded value to delete the records?


WS-SQL-VARIABLE PIC X(100) VALUE SPACES.
...

STRING "DELETE FROM Contacts WHERE LastName ='", something, "'" INTO WS-SQL-VARIABLE.
MODIFY hConnection Execute(WS-SQL-VARIABLE).
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] MS Access

One more clue, you may want to use the phrase DELIMITED BY SPACES on the variable something to avoid getting spaces in there.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.