Append to non-access table with primary keys

[cammi]

I have 2 tables in Access97. One is an access table (je_entry) and the other is linked in Access via ODBC (sysadm_journal).

I want to append data to the sysadm_journal table from je_entry. However, je_entry does not have all the fields as sysadm_journal. And to add to that, all fields must be filled in the sysadm_journal. I know that I can just fill most of the fields with 0's, except that I want the transaction_id field (which is the primary key) in the sysadm_journal table to be filled when my appended records move in.

I figure that I must create another field in the table I am appending from and I would like to take the last number of the sysadm_journal table, increment it and fill those numbers in the je_entry table before moving into the sysadm_journal table.

I created a query that looks up the last number in the sysadm_journal.transaction_id field and places it into a new table.

I want to get that number and add it to my je_entry table, but don't know how to increment the numbers. Should I do that in the query that gets that last number? What is the syntax for this?

 

[THoey]

Really I think this all depends on the table you are appending the data to. If that table, in it's native environment, when a record is loaded into it, uses a sequence (Oracle's versiopn of an autonumber), you just adding one to the greatest number and loading the record could cause a problem when it tries to load another record in it's environment. I know that doesn't make much sense, so let show an example. Consider there are two applications, yours and this foreign application.

The foreign application is set up such that when a record is added to that table, it has an sequence type field that is the primary key. When a record is added, the application goes to this sequence and asks for the next number (it tracks the current value internally, without having to look at the table) and loads all the info into the table. Lets say the next number is 12345, that is then added to the data and is the PK for that record.

Your application comes along and wants to add a record. You look up the greatest PK and add one to it coming up with 12346 and load your data.

The foreign application goes to add another record. It asks the sequence for the next number and is given 12346. This would cause a PK violation. I think you need to find how that PK field is filled in the foreign application and do the same.

That probably doesn't make much more sense, but I hope it at least gives you some ideas. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[cammi]

I understand what you are saying. This is a SQLBase db and I am creating an access table to append from. So I have this append query that takes all the fields from the access table I created and tries to import them into the table I am appending to. The problem is I get this error about the foreign key... I know that all the fields in the table I am appending to are required, and that the table I am appending from doesn't contain all those fields, so I am going to have to also create those fields and fill them with 0 or something. The main issue is that I have to fill the transaction_id field with a valid number.

Since I am trying to do this all in Access (so that the end-users will be able to update it), I need to build this into the query that creates the table or somewhere like that.

Thanks for the reply!

 

[THoey]

Can you find out how the transaction_id is filled in for the other application? I am not familiar with SQLBase, but if they have something like the Oracle sequence that I mentioned, you should/might be able to grab the next value from it. Something like:

INSERT INTO sysadm_journal (transaction_id, ...)
VALUES (sequence_name.next_val,...)

Hope this helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.