Duplicating a row (on purpose!)

[303factory]

Hi

I'm looking at how to duplicate an entire row of data in my table (apart from the autonumber primary key, I'd want this to assign a new value)

I've been trying the following code:

cmdstr = "INSERT INTO tblExhibitInfo SELECT * FROM tblExhibitInfo WHERE Exhibit =" & Chr(39) & strExhibit & Chr(39) 
gSQLdbase.Execute (cmdstr)

The problem here is that it will try and duplicate the primary key as well which obviously is not going to work. Is there a way to create a SELECT statement that is all fields except for the primary key?

I dont want to write out every field because 1) there are loads 2) I want it to work on any table 3) i want the code to work when we expand the number of fields in the table (happens quite frequently)

Is there perhaps another cunning way to duplicate a row?

Cheers

303

 

[namliam]

Instead of * list the specific fields, skipping the primary key...
You can do this easy with the designer.

 

[303factory]

Instead of * list the specific fields, skipping the primary key...
You can do this easy with the designer.

As detailed in my post this is not an option, I need something that can work on any table with any number of fields.

 

[namliam]

Well you can dynamicaly change the query by looping through the table ... that is assuming you know what column is your primary key.... (i.e. has a fixed name... ie. ID or something?)

Expanding the # of columns quite regularly sounds like the table is not exactly normalized