How can I copy a file from a table to another table?

[ppetrol]

How can I copy a record from a table to another table?

I have 2 tables (TblAudio1 and TblAudio2)
TblAudio1 is the main table with all records.

TblAudio2 is an empty table that must be filled with a number of fields from TblAudio1

if I want to copy a number of fields from TblAudio1 to TblAudio2 with a button on form FrmAudio1 how would I have to do that?


The code currently looks like this:

Private Sub cmdKopie_Click()
            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
            With Me.RecordsetClone
           
           .FindFirst "ID = " & Forms!FrmAudio1!Id
        
                DoCmd.RunCommand acCmdSelectRecord
                DoCmd.RunCommand acCmdCopy
                DoCmd.GoToRecord , , acNewRec
                DoCmd.RunCommand acCmdPasteAppend
        
        Me.SRT = "A"
        Me.NUMMER = ""
        Me.TRACKNO = ""
            
End With
    Me.Refresh

End Sub

Thank you in advance for your help!

 

[plog]

with the same structure ofcourse

First, in a relational database data isn't supposed to move around. It's supposed to be related, or even flagged. Second, when you have two tables with the same structure, you are probably doing it wrong. When you do that, you are essentially storing data in the table name. Table names should be generic and the records they contian should have all the data necessary to differentiate them in a field.

You wouldn't have a table for Rock Music, a table for Country Music, a table for Classical Music; instead you would have a field in your generic Music table to denote the genre. That's what I think you need to do with your data. Instead of moving it, use a field to denote that it has a specific property. That's how you should designate different records, not by moving them to their own table.

 

[June7]

Agree with plog. DB design seems not optimized. Why is there a 'new' table?

Also, if you are embedding files in table, must be using OLEObject or Attachment type field. That requires special code. I don't use OLEObject. For Attachment field review https://sourcedaddy.com/ms-access/working-with-attachment-fields.html

Embedding files uses up Access 2GB size limit. Usually better to save files in external folder and store path in text field, especially if db will grow.

 

[ppetrol]

First, in a relational database data isn't supposed to move around. It's supposed to be related, or even flagged. Second, when you have two tables with the same structure, you are probably doing it wrong. When you do that, you are essentially storing data in the table name. Table names should be generic and the records they contian should have all the data necessary to differentiate them in a field.

You wouldn't have a table for Rock Music, a table for Country Music, a table for Classical Music; instead you would have a field in your generic Music table to denote the genre. That's what I think you need to do with your data. Instead of moving it, use a field to denote that it has a specific property. That's how you should designate different records, not by moving them to their own table.

dear Plog.
in table TblAudio1 all music is ever made.
to save a lot of typing
I want to extract the data for my private collection.
including the genre.
thank you for your sugestion but it's no use to me.

 

[plog]

Why not? Because your not familiar with how a database is to work? Or because you have a genuine reason?

Seems like my suggestion is exactly what you need. Create a new Yes/No field in TblAudio1 called [PrivateCollection] and mark it as Yes when it is in your private collection.

 

[The_Doc_Man]

ppetrol - plog's suggestion is perhaps lost on you because you don't understand it. When you have a bunch of X in a table and want to put a bunch of X in another table - AND THE TABLES WILL BE IDENTDICAL - then the question is, what makes them so different that they do not belong together? BUT here is the trick plog is suggesting. If this "something different" qualifies as a descriptive difference, make it another attribute of the single table that then holds ALL of your X items. But the ones you would have put in a different table get a mark in this extra attribute field that you DON'T apply to the X that would not have gone into the original table.

Storage-wise, the size of the two tables X and DifferentX would be smaller (by a VERY small amount) than the single combined table - but if you count the size of the overhead of a second table descriptor, that makes up a lot of the difference. THEN if you wanted to separate the X and "different X" from each other, that is why queries are your bestest friends forever. This is an example of aggressive normalization in action.

 

[ppetrol]

Hello grandfather congratulations,

I have to copy because the vinyl record gets a different number
Me.SRT = "A"
Me.NUMBER = ""
Me.TRACKNO = ""
as in the source table to look up in my record rack.
hopefully you can help me.
Greetings from the Netherlands

 

[June7]

So you want to store the same audio file in both tables?

 

[ppetrol]

no sorry
the record gets a new [ID] number
SRT
NUMBER
TRACKNO

thanks for your response, i hope you have a solution.

 

[June7]

No, I am lost. If you don't want to store the same audio file into other table, what do you want to do? Just save the first table Primary Key as a Foreign Key into second table?

 

[ppetrol]

artiest Titel SRT NUMMER TRACKNO Rel_dat TIPHIT COMPONIST LAND LABEL HOELANG
Toto Rosanna SIN 4384 1 24-4-1982 H NL 05:27

copy this record without
SRT
NUMBER
TRACKNO
I fill this manually because they do not match my private collection



artiest Titel SRT NUMMER TRACKNO Rel_dat TIPHIT COMPONIST LAND LABEL HOELANG
Toto Rosanna SIN 3 1 24-4-1982 H NL 05:27

I hope it's a little clearer now
Thank you for helping me

 

[jdraw]

ppetrol,

It seems readers are not following the intent of your post. I suggest you write a description of what you are trying to achieve in your native language - in clear and complete terms -then translate that to English and post that.

Readers do not know you nor your environment, so details and examples showing initial conditions and your final desired result should help clarify the requirement.

via Google translate:
Lezers kennen u noch uw omgeving, dus details en voorbeelden die de beginvoorwaarden en uw uiteindelijke gewenste resultaat tonen, moeten helpen om de vereiste te verduidelijken.

Good luck.

 

[ppetrol]

Re: How can I copy a record from a table to another table?

succeeded, thanks everyone.


Dim strSQL As String

strSQL = "INSERT INTO [TblAUDIO2] ([Artiest],[Titel],[TIPHIT],[Rel_dat],[COMPONIST],[LAND],[LABEL],[HOELANG]) SELECT [Artiest],[Titel],[TIPHIT],[Rel_dat],[COMPONIST],[LAND],[LABEL],[HOELANG] " & "FROM [TblAUDIO1] " & "Where [TblAUDIO1].[id]= " & Me![Id] & ""

CurrentDb.Execute strSQL, dbFailOnError

 

[jdraw]

Glad you have it resolved. Thanks fr posting the SQL.

 

[Galaxiom]

Re: How can I copy a record from a table to another table?

succeeded, thanks everyone.

Too bad you didn't learn anything about designing a database despite the efforts of contributors.