Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44

    Need to copy a lot of data from old to new database

    Hey guys;
    I just installed MS Access 2019 (365 sub) and am in the process of creating a new database for my music library.


    The old db was created in Access 2010 (accdb), but had become a bit confusing and possibly corrupt, so I created a bran-new one in 2019.
    Now, I would like to copy data from the old to the new database.
    Currently, I have set up tables in the new db, with relationships as I want them.

    I'm thinking there's got to be a method, using VBA (which I am familiar enough with to do some coding) to accomplish what I want.
    Basically, I need to query the old db table (songs) and copy the songs into the correct album in the new db.
    Perhaps if I copy the table from the old db to the new, and build an update query? But from what I read, an update query cannot add new records; only change existing ones.
    Like I said; I'm somewhat familiar with VBA, but don't have enough experience with it to do something like this.

    I am uploading both the databases.
    If anyone can help, I would greatly appreciate it, but I don't want anyone to do the work for me. I want to learn VBA, and this could be a good opportunity.

    Link to .zip file containing both databases. The file has been scanned by ESET Internet Security and is clean.
    https://drive.google.com/open?id=1Xj...WW-dgmuAv03dL_


    Thanks for your help

    FW

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Downloaded zip folder. Get error that folder is empty.

    What makes you think db could be corrupt?

    Did you try just opening in 2019 and do a Publish/SaveAs?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    I should have attached the file here, but instead uploaded it to Google Drive, like I do for photos.
    Let me try it again.

    database.zip
    This should have the two accdb files. Problem last time was that the db was open, so nothing was archived.

    Perhaps my old db is not really corrupt; I never get a warning that it is, but there are some strange things going on.
    for example; in the music_2019.accdb file, open the table 'TBL_Artists'. Click any of the + next to a record, and you are asked to enter parameter value 'Album_Artist'.
    The problem is that there is no field with that name. There was at one time, but I removed it.
    If you look at relationships, there are none showing. So I don't understand why there is even a + for each record. I thought you need a relationship for that to happen.
    This is why I came to think that the database is corrupted. Still, I do database repair, and nothing is done.

    Now that I go back into the old db, I realize why I wanted to build a new one: I used several lookup fields with tables, and have since learned that using lookup fields this way causes issues. For one, a lookup field cannot be queried.

    At this point, I think I need to study Access further to gain a better understanding of what I am doing, and how to accomplish what I want to do.
    This is a personal system, so I have no deadlines or whatever I would have if it were for corporate.

    Can anyone suggest a good (inexpensive) source for learning Access 2019 (365)?
    I have the Access 2010 Bible, but now using Access 2019, so I want something up to date.

    Thanks for your help
    FW

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Tbl_Artists SubdatasheetName property has Table.TBL_SONGS and ChildLink property shows Album_Title. This is why the + and popup prompt occur.

    I never set Subdatasheet in tables. I do as little formatting in tables as possible. That includes hiding fields.


    Glad you learned about not building lookup fields in table. If you used Lookup Wizard, can be difficult to eliminate the relationship (doesn't show in Relationships window). Can try just changing to textbox in table design and see if that corrects.

    You have some rather long value lists for a couple of lookup fields. Might want to create tables as sources for combobox lists.

    Copying tables will just carry over all formatting.

    Can copy/paste data or run INSERT SELECT action SQL. But if either is not done properly, the autonumber PK/FK links can get messed up.




    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    with regards your 2020 db, a few comments

    1. better to give your ID fields relevant names - AlbumID for example. This will avoid confusion when writing queries as to which ID you are referring to. Personally I use PK and FK suffixes (Primary Key and Foreign/Family Key) rather than ID so I also know which 'end' of a relationship a table is. But that is a personal choice
    2. Your relationships should be between your ID's (or PK-FK as I prefer), not names. a) your queries will be faster but more importantly b) if you mistype or change a name, it will no longer link to the other tables. This can be solved as you have by using cascading updates, but that just provides an unnecessary step which has the potential for failing and c) with your current setup you cannot have duplicate names which do exist, at least for albums - see this link for some examples https://forums.stevehoffman.tv/threa...ection.539479/. Just because it hasn't happened yet, doesn't mean it won't happen in the future.
    3. Your foreign fields are not indexed (e.g. artist_name in tbl_songs) which will have a performance hit
    4. using subdatasheets also affects performance, better not to use them.


    If your data remains small, then performance will probably not be an issue, but if you are trying to develop good practice then you might want to consider adopting these suggestions. Because you use forms and reports, it is not necessary to store the actual value in tables, store the FK

  6. #6
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    Thanks for the help guys; Now what I really need is a good source from which to learn Access, so I don't continue making mistakes like the ones I have already made.
    Should I just stick with the Access 2010 Bible (as most of the info is still pertinent in 2019), or upgrade my textbooks with the software?
    Can you recommend a good text for both Access itself, and perhaps another (or same) for VBA? I have always liked the 'Bible' series, and buy them on Kindle, so not so expensive.

    Edit: The Property Sheet is new to Access 2019, isn't it. I don't recall seeing it in Access 2010.
    Uh, never mind. It was there; just not so obvious.

  7. #7
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    I think that, thanks to your help, guys, I am beginning to understand this a bit better.
    I have made some changes to my tables (in music_2020).
    1. In TBL_Artists, I have changed the PK to the Artist_Name field, since it is unique. I have deleted the original auto-number PK.
    2. In TBL_Albums, I have changed the PK to a composite PK, using both Album_Title and Artist_Name fields.
    3. In TBL_Songs, I have deleted the Artist_Name field, as it is not required, nor useful, since this information is stored in the Artists field. I should have understood this when I began building this db, as I have been reading about normalization.

    I created a test query to search for songs by name. The query takes Song_Title from TBL_Songs, Artist_Name from TBL_Artists, and Album_Title from TBL_Album.

    Perhaps, once I have built the db I need, I can import the table from the old db, and run an update query to copy the thousands of songs from the old into the new. But I'll take this one step at a time, and not be afraid to experiment - after saving a backup of my working database of course.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend that you review post #5 by Ajax where he offers very solid advice.

    As for a source of info re Database Planning and Design (and more), here is a link to a variety of articles.
    The tutorials from RogersAccessLibrary are excellent for experiencing the "process" of designing a database, but you have to work through 1 or 2 (30-45 minutes each) and you will learn.
    Good luck.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    still think using names as a PK is a bad idea, you should use an autonumber PK. OK so you have a composite index for albums, but what about artists? Here is a list of duplicate artists names

    https://rateyourmusic.com/list/nonam...act-same-name/

    and with regard songs - ok you have an id field so you can have duplicate song names
    In TBL_Songs, I have deleted the Artist_Name field, as it is not required, nor useful, since this information is stored in the Artists field
    The query takes Song_Title from TBL_Songs, Artist_Name from TBL_Artists, and Album_Title from TBL_Album.

    So how do you handle composite albums with multiple artists? Or the same song by different artists (not forgetting there are many duplicate song titles for completely different songs, not covers)

    You seem intent on using as few fields as possible to 'save space' when in fact your way will take more space because of the field sizes and indexing

    long numbers take 4 bytes
    text takes 2 bytes per character plus 2 bytes

    so using an example of a parent record with 10 child records

    with long - you have 4 bytes in the parent record plus 40 bytes in the child records (44 bytes) and the same again for indexing - a total of 88 bytes
    with text (assuming a name is 10 chars long) - will take 22 bytes in the parent record plus 220 in the child records (242 bytes) and the same again for indexing - a total of 484 bytes

    so very simplistically, your query will take up to 5.5 times longer to run using text rather than long. It won't be that noticeable for small numbers of records but will for larger numbers.

  10. #10
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    I think I'm getting the hang of this, but still have one perplexing problem. That is the 'various artists / compilations' albums. Here, I have many artists contributing to a single album.
    The way I have the database set up, my heirarchy for tables is:
    Artists > Albums > Songs

    This works fine for single-artist albums, but not for the compilations. Those albums have one artist, which is 'various artists...', and thus, do not appear when I expand one of the artist's records in the Artists table.
    I could link the Artists table to the Songs table, but then I lose the heriarchy I believe is appropriate for this system.
    I am currently working on trying to establish a many to many relationshib between Artists and Albums. I'm not sure if this is the best method, or I should add a field in the Songs table for 'song_artist', which could be different from album_artist.
    I could then run queries to select the data I want.

    In the end, I hope I will have a better understanding of MS Access, as that is my ultimate goal for this project. After all, I could have left Access alone, and worked with my music library in Music Bee or a similar app.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend that you work from your business rules to create a data model. Then with some test data and test scenarios (mockups of what you need out of the database) with pencil and paper work through the scenarios. Adjust the model as necessary until it does what you need--you now have a blueprint for your physical database. See my "stump the model" for more info.

    It is much more difficult to restructure your tables and relationships in a physical database. Designing as you go (ad hoc) is a long, circuitous approach. Again, I suggest the info in post #8.
    Good luck with your project.

  12. #12
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    Thanks. In my case, there are no 'business rules', as this is strictly a home project for personal use. That said, I want to educate myself on Access, so that perhaps in the future I will be able to work with business scenarios.
    I have always tended to design my databases 'by the seat of my pants', which I understand is not the correct method. All of the tutorials and books I have read say the same thing you are telling me here; use pen and paper - or better, pencil and paper with a good eraser. So that is what I am going to do. I haven't gone so far into entering data into the db I have built so far that I cannot leave it and start fresh, chalking up my old work to education. Since it's not a business project, I have absolutely no time constraints.

    On a side-note;
    I am currently working in Access 2019, with a trial sub to Office 365 Home ed. I have Office 2010 Professional installed as well, but like the look and feel of the newer version, so I will most likely continue the sub to 365 after the trial (I have about 27 days remaining), but switch to the Personal edition, as I don't need it on multiple computers.
    I realize that it is likely that, once I get my database designed in Access 2019, I may not be able to go back to 2010, should I choose not to take the 365 sub. But so far, I can still open the .accdb file created by 2019 in Access 2010. Of course, there may be functionality issues if I attempt to do that, but it's something to consider.
    All that said, I really do like the look and feel of the whole Office 365 (2019) compared to the 2010 version, so in all likelihood I will continue the sub to 365 (personal edition), but may consider purchasing it outright, as I normally don't upgrade my software every year. After two years, the purchase, rather than the sub to 365 would make sense.
    But I still have plenty of time to make that decision.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    there are no 'business rules', as this is strictly a home project for personal use.
    business rules is just a term used to focus on the relationships. Think instead in terms of relationships

    one artist can have many songs
    one song can have many artists
    one album can have many songs by many artists

    etc

  14. #14
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    Quote Originally Posted by Ajax View Post
    business rules is just a term used to focus on the relationships. Think instead in terms of relationships

    one artist can have many songs
    one song can have many artists
    one album can have many songs by many artists

    etc
    So, a many to many relationship?

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    for a many to many relationship you need an extra joining table e.g.

    one artist can have many songs
    one song can have many artists
    one artist can have many albums
    one album can have many songs

    so you have

    tblArtists
    ArtistPK autonumber
    ArtistName text

    tblSongs
    SongPK autonumber
    SongTitle text

    tblAlbums
    AlbumPK autonumber
    AlbumName text

    and to represent the join

    tblAlbumArtistSongs
    AlbumArtistSongPK autonumber
    AlbumFK long (indexed duplicates OK)
    ArtistFK long (indexed duplicates OK)
    SongFK long (indexed duplicates OK)

    This is a basic setup, depends on what other data you want to store as to other fields you might have, which table to put them in or perhaps you need another table or two - e.g. you may want to store

    who wrote the song (as opposed to who performed it)
    when it was written
    when an album was published
    what version of the song (live/recorded)
    where performed/recorded
    what groups an artist was in and when
    etc



Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 18
    Last Post: 08-09-2018, 06:45 AM
  2. A quick way to copy all tables from a database (data only)
    By earlcools in forum Import/Export Data
    Replies: 1
    Last Post: 10-13-2017, 02:48 AM
  3. Replies: 4
    Last Post: 05-25-2017, 06:07 AM
  4. Replies: 1
    Last Post: 05-16-2016, 05:58 AM
  5. Copy data from table in another database
    By jcc285 in forum Import/Export Data
    Replies: 8
    Last Post: 04-07-2016, 05:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums