How to insert rows in a table without key field?? 2

[forosemilio]

Hi, I would appreciate your help.
When I create a table within an adp, I am not enabled to insert rows unless I include a primary key field on it (I mean, when I open the table, doesn't appear a blank final row, unless I include a primary key field). How can I solve this? In fact I can insert rows through SQL "Insert..", but is this the only way?

Thanks

 

[cmmrfrds]

How are you doing this - from where?
In fact I can insert rows through SQL "Insert..",

 

[ttyppi]

I have the same problem: when a table is open,
there is no empty line for adding new lines.

And I cannot update existing lines (imported from text file).

Can anybody help?

I suspect that there is nothing one can do to this,
except programming own forms or something ...

 

[cmmrfrds]

Make sure the table has an unique key for updating.

 

[ttyppi]

Yes, but that is just what I don't want to do.

But I am sure it is the only way to get MSaccess working.

In similar cases I usually make a special id-field for keyvalues, which is not shown to the user. But this time the customer does not want any programs or forms, just the tables. She is going to use it manually, using only easy MSAccess features.

 

[cmmrfrds]

An autonumber field is not updateable by the user, just tell her to ignore the field.

 

[jgoodman00]

This behaviour is by design. A table without a PK will not be editable in an ADP. You can however insert/update/delete records by executing SQL statements.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[ttyppi]

Thank you jgoodman00!

(Those are just the facts I was afraid to hear )

So I added an autonumber field and made the column so narrow, it won't disturb the poor user
----------------------------------------
The above worked on some tables, but there were other tables,
where personID-column could be used as a key.
Some rows did not have a unique personID thou...
but I could fix that.

Still, it was a real burden to handle and fix the preloaded data:

1. I loaded a few thousand (6000) rows in a table
(from a CSV-file)

2. I tried to edit one column (personID) in adp to make its values unique:
no luck, because there was no keyfield yet.

3. I took the data into excel, autofilled a new column (with numbers 1 to some 6000)

4. I added one more column (temp_key1) to the table and
loaded the data back from excel

5. I made the temp_key1 column primarykey
(this could not be done when the column was empty)

6. Now I could update the table in ADP: I removed the duplicates from personID-column (some by hand, some by update-SQLcommands)

7. I removed the temp_key1-column and made personID-column primarykey

Just wondering if there could have been an easier way to do this (in less than half a dozen steps), anybody?

 

[cmmrfrds]

In SQL Server an "Identity Column" works like an autonumber column in Access. Just put this type of column on your sql server tables and insert the other data this column is not filled by the user during an insert. I have never found a reason NOT to put an identity column on ALL tables. This becomes the primary key an is used for all maintenance on the tables.