Hi Im have real trouble figuring out how to do this purely in vba. If any body could help I would owe you big time. If anybody could give me some code on how to do parts of or all of the below. I thank you.
I have 3 Tables
tblArtist it has 2 fields Artist_id and Artist_Name
tblGenre it has 3 fields Genre_id,Category_id and Genre_Name
tblVideo has 8 fields Video_id,Artist_id, Genre_id ,category_id,filename,Song_name,year and libraryno
I will have a txt file with artists and songs in it, left is artists and right of the - is songs e.g
# BASEMENT JAXX FT. SAM SPARRO – Feelings Gone
# THE SATURDAYS – Forever Is Over
# BASSHUNTER – Every Morning
1 I would like to be able to read the txt file and get the text after the – then search the video table song_name column for a duplicate. If a duplicate is found it moves onto the next line.
2 If no duplicate is found I need it to check before the - is not in the artist table, artist column. If it is not I want it to add it to the artist table without the # with the artist_id being the highest in the artist_id column +1 and goto step 3. If it is in the list then I want it to go to step 3 without adding it.
3 Now I need it to accumulate data to add to the video table.
4 The video_id would be the highest video_id +1 in the video table.
5 The Artist_id would be the artist_id corresponding to the Artist_Name Before the –
6 Then a popup would be shown showing me line 1 in the text file asking me to pick an item from the genre table. The genre_name I pick the corresponding genre_id will be used as the genre_id in the video table.
7 The category_id is the category_id in the genre table corresponding to the genre_id I just picked.
8 The filename is the highest number in the video table filename column +1 (the filename is eg vdt300565.mpg then +1 is vdt300566.mpg.
9 The Song_name is the text after the – on line 1 of the text file.
10 The year is the current year.
11 And the libraryno is is the filename without the “.mpg”
12 Then add all that data to the video table then do all the same to line 2 etc until the whole txt file has been read.
I thank you people so much if you can help
Many Thanks