Copying Records - Better Way

[bigal.nz]

Hello,

I have been battling this for a few days now, and there has to be a better way.

I want a button on a form to copy a record from Table 1 DB1 to Table2 DB2. The tables structure is identical.

There is a Auto Primary Key ID on both Table1 and 2.

Both Table 1 and 2 have a large number of fields (about 50)(yes I appreciate this may be bad design).

I can not simply go "INSERT INTO Table2 SELECT * FROM Table1 WHERE <Criteria> because I end up with a primary key in the recordset and when I try to insert it into Table2 I could get a key violation.

I tried to loop over the fields while they are in the recordset and remove the "ID" field - but it doesnt appear you can do that.

I also tried to construct a SQL String but all the escaping and double quotes and # for dates is going to make this a very unpleasant task.

Is there a better approach?

Thanks

Al

 

[Galaxiom]

Drop the AutoNumber from table2 and add a Long Integer field as ID so you can use the PK from table1. Then you can simply insert the whole record.

 

[bigal.nz]

Hello my fellow Antipodean,

I don't think that is an option - data comes into those tables from a variety of external places (not just this copy query) so it needs to keep track of its own records.

Cheers

Al

 

[The_Doc_Man]

The destination of the copy cannot have an autonumber PK. It's that simple.

Here is the question... is there anything about DB2 such that it would have to independently add records? Because if it does, then you have painted yourself into somewhat of a corner. In this case, DB2 could STILL create keys that would conflict with the records transferred from DB1.

Here is one answer... if the table in DB2 is linked, you can use the query builder to do an INSERT INTO type of query using the design grid. Bring the two tables into the graphic area above the grid, then drag-n-drop all the fields you need, one field at a time. There would be no quotes involved if it is a field-to-field transfer. BUT if DB2's table is auto-numbered, leave out DB1's PK from this list. There is a way to see the SQL of the query just to see if it makes sense.

If you are storing things that are NOT in DB1's table, because perhaps you are imposing some constants on those fields, you have again painted yourself into the "tedious" corner because that is pretty much what you would have to do... put in the quoted items from DB1's table using awkward syntax.

 

[bigal.nz]

What about this approach?

It creates a 'source' recordset containing the record to be copied with the ID field.

It then creates another blank 'destination' record set. Loops over the source recordset field names and adds them to to the blank recordset (minus ID).

It then loops over the record set a second time to add the data to the destination recordset.

I cant quite get it to go at the moment as :
(a) I need to detect the string name for the ADODB datatype name on the fly; and
(b) I am getting an error on the line:

rsTarget.Fields(Field.Name).Value = rsSource.Fields(Field.Name).Value

Run-time error '-2147217887 (8004e21)':
Non-nullable column cannot be updated to null.

This error comes up after about the 5th loop of the second next - and probably to do with a incorrect data type?

I need to know what Access data types map to ADODB data types?

Thoughts?

Al

Private Sub btn_transfer_Click()
Dim conn As New ADODB.Connection
Dim Field As ADODB.Field
Dim rsSource As New ADODB.Recordset
Dim rsTarget As New ADODB.Recordset

Dim db As Database
Set db = CurrentDb

rsSource.Open ("SELECT * FROM SAM WHERE EventNo='" & EventNo & "';"), CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

For Each Field In rsSource.Fields
    If Field.Name <> "ID" Then
            rsTarget.Fields.Append Field.Name, adVarChar, 200
    End If
Next Field
rsTarget.Open

rsTarget.AddNew
For Each Field In rsSource.Fields
    If Field.Name <> "ID" Then
        rsTarget.Fields(Field.Name).Value = rsSource.Fields(Field.Name).Value
    End If
Next Field
rsTarget.Update
Debug.Print ("The End")

End Sub

 

[The_Doc_Man]

Simpler comments first. Ditch the ".Value" because that is the default property for anything that actually HAS a .Value property. (Shortens typing.)

Next, the error probably means that despite any constraints that might be in place, one of your source fields is null when the destination is set to require a value. (.Required=TRUE)

Then, your question about mapping ADO data types to Access or vice-versa:

https://www.w3schools.com/asp/ado_datatypes.asp

 

[bigal.nz]

Thanks docman.

I still cant quite get it right. adFldIsNullable should take care of empty fields in the recordset.

I can simplify the code to add the field and the value in one go:

For Each Field In rsSource.Fields
Debug.Print (Field.Type)
    If Field.Name <> "ID" Then
            rsTarget.Fields.Append Field.Name, adVarChar, 20, adFldIsNullable, Field.Value
    End If
Next Field
rsTarget.Open

But the line really causing the issue is :

rsTarget.Fields.Append Field.Name, adVarChar, 1000, adFldIsNullable, Field.Value

I seem to have to explicitly define adVarChar (as opposed to Field.Type which returns 200 in the case of a string, (there are also some dates and booleans in the recordset - I may need to write a procedure to workout the type and return the correct string?)

But anyway as it stands that line returns a error:

Runtime error 3001
Arguments are of the wrong type, out of acceptable range, or in conflict with one another

Help appreciated.

Thanks

Al

 

[bigal.nz]

Ok, I have managed to successfully copy the ADODB recordset object into rsTarget which I now need to figure out how to insert into my target table?

Code below so far for anyone who can help please?

Private Sub btn_transfer_Click()
Dim conn As New ADODB.Connection
Dim Field As ADODB.Field
Dim rsSource As New ADODB.Recordset
Dim rsTarget As New ADODB.Recordset
Dim FieldType As DataTypeEnum
Dim FieldSize As Long

Dim db As Database
Set db = CurrentDb


rsSource.Open ("SELECT * FROM SAM WHERE EventNo='" & EventNo & "';"), CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

For Each Field In rsSource.Fields
Debug.Print (Field.Type)
    If Field.Name <> "ID" Then
        FieldType = Field.Type
        FieldSize = Field.DefinedSize
        rsTarget.Fields.Append Field.Name, FieldType, FieldSize, adFldIsNullable
    End If
Next Field

rsTarget.Open
rsTarget.AddNew
For Each Field In rsSource.Fields
    If Field.Name <> "ID" Then
        rsTarget.Fields(Field.Name).Value = rsSource.Fields(Field.Name).Value
    End If
Next Field
rsTarget.ActiveConnection = CurrentProject.Connection
rsTarget.Update

' How to insert rsTarget here?

End Sub

Thanks

Al

 

[gemma-the-husky]

I think this is really about data structure, and real world keys. One issue is why you need to have a copy of the data in both tables.

if data is getting into table B, from other sources as well as table A, then you really need to think about your setup.

If the only unique identifier in table A is the autonmber, then this HAS to be unchanged in table B. What is the data. Do you really not have "real data" that comprises a unique key, independent of the autonumber?

 

[bigal.nz]

Hi,

I really should have called this moving a record since it is being moved not copied - my bad.

In terms of the data structure yes maybe some bad calls were made there, but Table A and B both now have quite a bit of data in them which uses the auto number.

In terms of what I have suggested above, now I have created the recordset, I presume up near my Dim statements I need to establish a connection to the correct database/table? Table B on Database B is currently a linked table in Database A.

Do you think this will work?

 

[bigal.nz]

Ok,

New approach using DAO. I am stuck on how to get the value for the corresponding field from the form while looping over the recordset?

Private Sub btn_transfer_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM SAM_NTH WHERE False")

For Each Field In rs.Fields
    If Field.Name <> "ID" Then
        Debug.Print (Field.Name)
        With rs
            .AddNew
            .Fields(Field.Name) =
        End With
    End If
Next Field

With rs
    .Update
    .Close
End With

MsgBox ("The End")

End Sub

Anyone?

 

[The_Doc_Man]

New approach using DAO. I am stuck on how to get the value for the corresponding field from the form while looping over the recordset?

I haven't asked this before, but ... don't you already KNOW the names of the fields that correspond to each other? Why the hell do you need to look up ANYTHING?

Build the INSERT INTO query like described before. Leave it in a string but leave off the end of the query. It might resemble, in part,

INSERT INTO TableB (FieldA, FieldB, FieldG, FieldD, FieldE...)
SELECT FAlpha, FBeta, FGamma, FDelta, FEpsilon... FROM TableA

Now when you are ready to select the records in question you take this partial string and concatenate it with a WHERE clause. Then create a DAO database pointer so that you can use the .Execute SQL method.

Dim DAODB as DAO.Database

...

Let DAODB = CurrentDB

...  {now build the string including a trailing semicolon}

DAODB.Execute TheSQLString, dbFailOnError

Don't forget to have an error trapper in the routine that does this because the Fail-On-Error parameter will cause a failed query to roll back changes. You will need to know if the query failed.

Afterwards, you can figure out what happened by testing DAODB.RecordsAffected, which will tell you how many records were actually inserted.

 

[bigal.nz]

This works:

Private Sub btn_transfer_Click()

Dim db As DAO.Database
Dim rsTarget As DAO.Recordset
Dim rsSource As DAO.Recordset

Set db = CurrentDb
Set rsSource = db.OpenRecordset("SELECT * From SOURCE WHERE SalesNo='" & SalesNo & "';")
Set rsTarget = db.OpenRecordset("SELECT * FROM DESTINATION WHERE False")

' Loop over every field (Except ID) in Target and add the corresponding value from Source recordset.

rsTarget.AddNew
For Each Field In rsTarget.Fields
    If Field.Name <> "ID" Then
        rsTarget(Field.Name).Value = rsSource.Fields(Field.Name)
    End If
Next Field
rsTarget.Update

End Sub

It doesnt copy the auto primary key and the DAO recordset object for the DESTINATION table automatically gives the Target recordset the correct ID.

By doing it this way I can change my table structure and not worry about changing the code - it copies everything but ID.