Creating Records in MS Access ?

[dev1]

Hi there,

I have created an ODBC Dataserver schema to read records from a MS Access database.

I use the create <table> command in progress to create records. However i have encountered a number of probs using this method.

1) The Autonumber is not incremented in Access, i need to input a number.
2) It seems all values get set to '?'. When assigning '?' to logical fields, i get error messages: <field> is mandatory but has an unknown value (?).
I have checked the table in the access db, these fields are not marked as mandatory.

Does anybody have any ideas how to overcome these probs?

Winnt 4.0. Progress 9.1d.

 

[dev1]

fixed

Probs sorted.

 

[Simon Sweetman]

A quick note on what the problem was and how you fixed it may help others who have the same problem and find your posting via a search :awink:

Probs sorted.

 

[ccotter3]

Hi there,

I have created an ODBC Dataserver schema to read records from a MS Access database.

I use the create <table> command in progress to create records. However i have encountered a number of probs using this method.

1) The Autonumber is not incremented in Access, i need to input a number.
2) It seems all values get set to '?'. When assigning '?' to logical fields, i get error messages: <field> is mandatory but has an unknown value (?).
I have checked the table in the access db, these fields are not marked as mandatory.

Does anybody have any ideas how to overcome these probs?

Winnt 4.0. Progress 9.1d.

You might find it easier to use ADO from Progress to read/update your Access database. I have attached a sample program that is a modified version of a similar piece of code from the Progress KB. I have used this technique to populate Access Db's directly from Progress database tables within 4GL code. If you would like some more info or more example code feel free to email me a ccotter3@yahoo.com

 

[dev1]

Sorted...

The first problem was with autonumber in MS Access. When using create statement in Progress the Primary Key field was not assigned the next sequence number in the Access db.

I was getting the last record of the table, assuming it would be arranged in PrimaryKey order, and thus the highest value of the key field would be returned.

pseudo-code was:
find last msa_cust no-lock.
assign vi-primkeyval = msa_cust.id.

assign vi-primkeyval = vi-primkeyval + 1.
create <table-name>.
assign values.

However, this did not work as the last record did not contain the highest value. So i would get a duplicate key and error messages from access.

I have changed the code to: /*see Progress help - FOR statement*/
FOR LAST msa_cust BY msa_cust.id:
assign vi-primkeyval = msa_cust.id.
END.

assign vi-primkeyval = vi-primkeyval + 1.
create <table-name>.
assign values.

This gave me the highest value of that particular field, i then increment by 1, and i know i won't get duplicate keys.

As for the logical values, i had to assign them default values of yes/no, according to business logic. This is a workaround, rather than the solution.

A quick note on what the problem was and how you fixed it may help others who have the same problem and find your posting via a search :awink:

 

[dev1]

Easier?

CCotter<Quote>
You might find it easier to use ADO from Progress to read/update your Access database.

I think its way harder to use ADO, simply because i haven't used much of it. The way i am accessing the MS Access db, i just use 'normal' progress statements, i can develop applications without learning a 'new' language and if another progress developer has to amend my code, its much easier for them to read the code, than the ADO stuff.

<quote>
I have attached a sample program ..
Thanks !!