• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Table problems (Oracle linked table)

Hi Experts

I have a oracle table with 3 primary keys, that together are not unique. One of the primary keys is a empty column that i want to update with a unique number. Is that possible?
0
DCRAPACCESS
Asked:
DCRAPACCESS
  • 4
  • 3
1 Solution
 
OP_ZaharinCommented:
You can't create a primary key if the combination of the three field is not unique. You should update the empty field with unique number (or running number) first. Only after that you create the primary key.
0
 
DCRAPACCESSAuthor Commented:
Hi op zaharin

it looks like this might work?

Dim rs As DAO.Recordset
Dim inti As Integer
Set rs = CurrentDb.OpenRecordset("SELECT id FROM xxx", dbOpenDynaset)
inti = 0
With rs
    Do Until .EOF
        .Edit
        inti = inti + 1
        !ID = inti
        .Update
        .Bookmark = .LastModified
    .MoveNext
    Loop
End With

but this was only tested on a access table and not the oracle table.
0
 
OP_ZaharinCommented:
OR during the insert statement into the table, include the unique/running number to that "empty field".

if u are using Oracle, you can use Oracle sequence. Create a trigger for that table so that everytime a record is inserted to that table, the trigger will create a unique running number (using sequence).
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
OP_ZaharinCommented:
Your code will do it for now.

But what if after the primary key have been created and you want to insert a new record to the table? So the next step is for you to modify by adding the unique number calculation into the insert statement for new records.
0
 
DCRAPACCESSAuthor Commented:
Long story, but i can't insert new rows or put a sequence on the table. I'm only allowed to update the fields from access or delete rows. Please don't ask why (bureaucracy at my work).
0
 
OP_ZaharinCommented:
Hi DCRAPACCESS,
Have u able to update the field and create the primary key?
0
 
DCRAPACCESSAuthor Commented:
Sorry for the late answer. But it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now