Primary Key Issue

[red_evolve]

Greetings.
The subject doesn't quite match the forum? :cheesy:
Well, I've to develop a system in VB. It's going to be a system used by some Management people. So, as I was designing the database, I was wondering, how could I assign the type of the primary key as?
The scenario goes like this:-

The Management people have the flexibility of adding new records into the database, so, the issue here is about the primary key. I would have a approximately 10 tables. If I use primary keys such as [A001, A002, A003] for table A, [B001, B002, B003] for table B and [C001, C002, C003] for table C, I know it is quite tough or even impossible for people to just key in the first name and last name to be stored in Table A and to have the new primary key be stored automatically following the first name and last name.

I could have asked them to key in the primary key along with the first name and last name, but I know that this is quite not-professional. If I use auto increment primary keys to solve the problem, can I use it for almost all the 10 tables? Will there be any clashes as of primary key is concerned?

Err, I hope you guys understand what my problem is. :confused:

[red_evolve]

Greetings.
Okay, I've got a little idea here.

Let's say my primary key set has this format -> C0001, C0002, C0003, etc.
Is there any split function available where I can split the letter 'C' and integers? Thereafter, in VB, when there is a 'Add new records" event, I would put:-

Dim iCount As Integer
'Get the last record in the table
'Use the split function
'Get the last record's PK's integer portion & store it as iCount
:
:
iCount = iCount + 1
'Format the iCount to become a 4 digit format
.Recordset.AddNew
.Recordset("com_code") = "C" & iCount
.Recordset.Update

Please help.
Thanks.

[bentkey]

What database are you using? In all my tables in all applications I always assign an automatically generated numerical primary key. Even if I don't figure I'll use it, sometime in the future I might. These keys can be used for relational links and should have nothing to do with your actual data. If you want to search on names, etc, create an index for that column. And unless you intend to have a large database you can use the same generator for all your tables, no one ever sees these columns anyway in most cases.

[Slade]

for most databases you can use a seed and have it increment by however much you want, what database program are you using?

[red_evolve]

Greetings.
I am using Access2000. It is going to be a small database, I guess.
I see. So, it is alright to use an auto increment for all the PK's in each table. I was just afraid that there might be some clashes or confusion.
Anyway, I came out with this function yesterday after posting.

Private Function formatPK(sCom As String, sTable As String) As String
    
    Dim sNewCom As String       ' The new com_code
    Dim iNewCom As Long
    Dim iLastCom As Integer
    Dim iLen As Integer
    Dim i As Integer
    
    sNewCom = Right(sCom, 4)    ' Get the digit portion of the PK
    iLastCom = Val(sNewCom)     ' Get the integer value of the portion
                        
    iNewCom = iLastCom + 1      ' Add 1 to get a new PK digit portion
    sNewCom = iNewCom
            
    iLen = Len(sNewCom)
            
    If iLen < 4 Then
        iLen = 4 - iLen
                
        For i = 1 To iLen
            sNewCom = "0" & sNewCom
        Next i
    End If
    
    If sTable = "competency" Then
        formatPK = "C" & sNewCom
    End If
    
End Function

[BinaryMayhem]

question: why dont you just set the table to autoincrement and scrap the letters. This way you can let MS access do the incrementing internaly and save yourself from writing a function!

[red_evolve]

Greetings.
Hmph...alright.
Err, one more thing.
Is it possible to connect 2 tables this way?

Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly

listCG.Clear
Do While Not myRS.EOF
    listCG.AddItem myRS!com_name
    sComIndex(iIndex) = myRS!comact.com_code
    iIndex = iIndex + 1
    myRS.MoveNext
Loop

I get an error on the red line but I'm not sure how to correct that.
Please help.

[BinaryMayhem]

yes, you can do it that way, or you can use JOIN.

SELECT * FROM comact INNER JOIN competency ON comact.com_code=competency.com_code;

there are lots of difrent types of joins, INNER, OUTER, LEFT,RIGHT and they all do diffrent things.. if your intrested I suggest you look them up. (im to lazzy to describe the diffrences right now! just saw spiderman 2... it was awesome!)

[red_evolve]

Greetings.
Thanks for the information.
Ok, now that I know it can be done, I still can't figure out why can't I do this:-

sComIndex(iIndex) = myRS!comact.com_code

The error pointed to this line of code.

just saw spiderman 2... it was awesome!

:( I'm a big spidey fan! Can only watch it during the weekend, work work work :(

[BinaryMayhem]

what is sComIndex(iIndex) (IE: variable type) a little more code might help.

[red_evolve]

Greetings.

Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly

listCG.Clear
Do While Not myRS.EOF
    listCG.AddItem myRS!com_name
    sComIndex(iIndex) = myRS!comact.com_code
    iIndex = iIndex + 1
    myRS.MoveNext
Loop

Err, I just wanna store all the joined records' com_code into sComIndex for further population later.
Usually, in a DBMS, we would do it this way:

SELECT comact.com_code
FROM comact, competency 
WHERE comact.com_code=competency.com_code

I thought it would be the same. Is it because of this: myRS!comact.com_code ? I deem it must be syntactically wrong but do not know what's the correct way.

[mnemtsas]

Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly

listCG.Clear
Do While Not myRS.EOF
    listCG.AddItem myRS!com_name
    sComIndex(iIndex) = myRS!comact.com_code

'
'Are you always going to have less than 10 records?  If not you'll go out of 'the index bounds.  Also refer to fields using :
'
'myRS.fields("com_code").value
'
'what you're using will not work as the fields are part of the fields collection
'not properties of the recordset.


    iIndex = iIndex + 1
    myRS.MoveNext
Loop

Also rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.

Like this:

listCG.AddItem myRS!com_name
    listCG.ItemData(listCG.newIndex)=myRS.fields("com_code").value

[red_evolve]

Greetings.
Hey, thanks for the advice.

Also rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.

What you meant by the above is to advise me to use an invisible list box rather than arrays? If I get you correctly here, I'd say that it's a good idea. I've never thought of that. Thanks!

[red_evolve]

Greetings.
Ummm...one more question on Primary Keys -> set to auto increment.
Let's say I have 1,2,3,4,5 in a table already.
When I delete a record, umm, let's say record with PK = 3, is it possible to code for the records below PK=3 be shifted up + the PK value be modified to -1 each?

[Slade]

I know what you're asking but I'm not sure you can because it's a unique identifier for that very record, it won't work like that. If there is a solution to this however please post as I'm interested :)

[RobUK]

The only way to do this is to NOT use autonumbering.

You could then write a function to do the renumbering, but why bother?

Your function would also have to take into account renumbering any foreign keys linking to this table from other tables..... in essence, nightmare, and completly unnessesary.

[mnemtsas]

In Access 2000 definitely no. Can I suggest if you want to use primary keys for something human useful (and judging by your questions you do) that you keep track of your own numbers. That way you have full control.

It's not hard, use the MAX() SQL operator to get the highest ID to pop the next record in. Perhaps keep a second table with the ID's of deleted records and check this table for unused ID's before using the MAX() operator to put one on top of the pile.

[red_evolve]

Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.

Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.

1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].

[mnemtsas]

Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.

Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.

1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].

I think you've got the idea, that's pretty much exactly what I was thinking of.

[red_evolve]

Greetings.
Alright, alright! Thanks a lot for the confirmation.
I'll try to work that out, in fact I deem there'll be no problem with coding that out.
One more question that I have to ask here, imagine I have a lookup table in of the columns in a table in Access.
As such in VB, I'd have a combo box. If I do not wish to add the items manually, say

cboRace.AddItem "Malay"
cboRace.AddItem "Chinese"
cboRace.AddItem "Indian"
cboRace.AddItem "Others"

Can I import the items from the access lookup table? If so, could you please give me some clue?

[mnemtsas]

Of course,

here's a snippet:

'*******************************************************************************
' fillProjectStageCombo(Function)
'
' PARAMETERS:
'
'
' RETURN VALUE:
'
'
' DESCRIPTION:
'
'*******************************************************************************
Public Function fillProjectStageCombo(cboTmp As ComboBox)
  Dim rstProjectStages As New ADODB.Recordset
  Dim cmdProjectStages As New ADODB.Command
  Const PROCEDURE_NAME = "fillProjectStageCombo"
  
  On Error GoTo errorHandler
  
  With cmdProjectStages
    .ActiveConnection = dabDatabase.conConnection
    .CommandText = "SELECT * FROM tblProjectStages"
    .CommandType = adCmdText
    .Parameters.Refresh
  End With

  
  cboTmp.Clear
  '
  'This is where I get the recordset, this is cut straight from one of my apps
  'but you can do the same using rstProjectStages.open cmdProjectStages
  '
  Set rstProjectStages = dabDatabase.returnRecordSet(cmdProjectStages)
  '
  'Now loop thru the RS filling the combo
  '
  If rstProjectStages.EOF <> True Then
    rstProjectStages.MoveFirst
    Do
      cboTmp.AddItem rstProjectStages![strStage]
      cboTmp.ItemData(cboTmp.NewIndex) = rstProjectStages![lngID]
      rstProjectStages.MoveNext
    loop until rstProjectStages.EOF
  End If
  Set rstProjectStages = Nothing
  Set cmdProjectStages = Nothing
  Exit Function
errorHandler:
  frmErrorHandler.errorForm MODULE_NAME, PROCEDURE_NAME
  Err.Clear
End Function

Hope that helps.

[red_evolve]

Greetings.
Thanks a lot.
I have a little doubt here.
Could you please explain these lines?

cboTmp.ItemData(cboTmp.NewIndex) = rstProjectStages![lngID]

I don't quite get what you wanna do there. Do you just wanna store the PK for tht itemdata for easy searching later?

cboTmp.AddItem rstProjectStages![strStage]

Just wanna double confirm:-
The above strStage is the lookup table you have in access right?

[mnemtsas]

Greetings.
Thanks a lot.
I have a little doubt here.
Could you please explain these lines?

cboTmp.ItemData(cboTmp.NewIndex) = rstProjectStages![lngID]

I don't quite get what you wanna do there. Do you just wanna store the PK for tht itemdata for easy searching later?

cboTmp.AddItem rstProjectStages![strStage]

Just wanna double confirm:-
The above strStage is the lookup table you have in access right?

yes to both questions.