How to Copy from Excel Into Database with Pkey

[doyle60]

We copied data from an Excel spreadsheet (created by using Access)
into a FoxPro database. The table had a Pkey in it so we put the next
number into the spreadsheet and incremented it. The data copied in
just fine.

But now we can't add anymore records in the normal form view because
it seems to not know what the next Pkey should be. So if the last
Pkey were 23, and we added 24 to 55, it still thinks to assign 24 to
the next (though we used it).

I'm not familiar with FoxPro and explained this with Access terms.

What do we need to do to reset the Pkey?

Is this something only the designer of the database would know? Or is
this something standard? I'm unfamiliar with Pkeys. It seems like
they are some sort of autonumber.

If there is a better group to post this question, please let me know.
Thanks,

Matt

 

[Tim Ferguson]

Is this something only the designer of the database would know? Or is
this something standard? I'm unfamiliar with Pkeys. It seems like
they are some sort of autonumber.

It is something that whoever is administering the Fox database should know.
A Primary Key is simply a method of identifying a row in a table -- it can
be a number, or a text string, or a combination of several fields. It does
have to have a value, and that value has to be distinct from every other
row.

An autonumber is often used as a convenient Primary Key because its value
always available and it's always unique within the table.

I don't know what you have to do to get Fox to provide a similar facility,
but it is likely that it can be done. Therefore, you need to ask your Fox
person. It may just be that you'll have to program it into your forms.

Hope that helps


Tim F

 

[doyle60]

Thanks. It's just odd that the field is called Pkey and it was
something we thought would be an autonumber. That is, we left it
blank and thought it would be filled in automatically, like Access
does. But it didn't go in. We then number the data sequentially in
Excel using the next number and the table in Fox took it.

Seemed to work well until we tried to add a new record the normal way,
on the form.

So would it be usual in Fox to have a Pkey that isn't auto?

Perhaps this was developed from a version that didn't have an
autonumber, assuming that Fox does have an autonumber.

Thanks,

Matt

 

[Arvin Meyer [MVP]]

You cannot reuse an autonumber. You can build your own autonumber using code
and increment it then restart anywhere you wish. A primary Key however, must
be unique and cannot be duplicated. Here's some code to create your own
autonumber using a seed in the default table:


Public Function GetNextNum() As Long
' © 1998 - Arvin Meyer - Permission granted for reuse if copyright intact
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblDefaults", dbOpenDynaset)

With rst
rst.MoveFirst
rst.Edit
rst!PONumber = rst!PONumber + 1
rst.Update
End With

GetNextNum = rst!PONumber

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
Resume Exit_Here

End Function

 

[Tim Ferguson]

So would it be usual in Fox to have a Pkey that isn't auto?

Autonumbers-versus-natural-keys is an ongoing and, at times, violently
flaming debate. As I indicated earlier, I would be surprised if VFP doesn't
have some kind of inbuilt facility for doing them, but you'd be better off
looking in a group dedicated to that platform. Try one of the
microsoft.public.fox.vfp.* hierarchy for more information on 1960's-based
file managers.

Best of luck

Tim F

 

[doyle60]

Thanks all. We found the table holding table names and last used pkey
for each. I figured there would be something like that. What a
drag. Love that autonumber!

Matt