[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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