Import to zionworx via MS-Access query export?

[isotope]

We have an existing MS-Access database of songs we use. Is it possible to generate an Access query with the right fields in that can be exported for Zionworx to import en-masse rather than song by song?

Thanks for a great product -- we don't use it yet, but once we get the right hardware we will!

[Williamting]

Hi,

This is the method I use:
1. Make a copy (use File Explorer) of your Access DB and Zionworx (songs.dbf) and call them M and Z respectively;
2. Use Excel to open M (copy of your MsAccess DB)
3. Remove all the unwanted columns (see step below)
4. Align all the columns in Excel in the following order:
Title_1, Title_2; Lyrics; Writer; Copyright; Key
5. Open Z (copy of songs.dbf) using MsAccess
6. Open Zionworx songs table and remove all the songs (Ctrl-A->Delete)
7. Go to Excel, select and copy all the rows (Ctrl-A->Ctrl-C)
8. Go to MsAccess and paste (click on row 1 and Ctrl-V) into the table.
9. Save Z (Exist MsAccess)
10. Start Zionworx
11. Import (Database->Unlock Database; Database->Import)


If you have problem opening the DB in Excel, probably the db driver has not been installed.

Depending on the number of songs you have, pasting from Excel may fails. Solution is to cut & paste in smaller chunk.
(p/s Please don't use Excel to open both M & Z db and cut and paste across. Excel changes table properties and Zionworx will not be able to open the db.)


I have not tried to populate songs.dbf via query in MsAccess as I don't understand how the index file works. I do know that it will render your db quite useless if you have songs with same title.

Additional info you may like to know:
Title_1 - 100 characters,
Title_2 - 100 characters;
Lyrics - Memo;
Writer - - 100 characters;
Copyright - 100 characters;
Key - 20 characters

Pasting will failed if any songs in M exceed the above spec.

Christian, I hope you don't mind me putting the above info here.

[Christian]

Impressive stuff William - I never knew that was possible!

[isotope]

William:

Thanks for that set of tips it of how you do this via Excel. It gave me sufficient information to work out the best way for me to do this.

I've outlined this below in case anyone else finds it useful. You need an intermediate level of MS-ACCESS knowledge to do this, but it is not too hard.

On the Access Form I use to enter songs, I added a new command button, named it "cbZionWorx" and added the following code using the Code Builder as its [Event Procedure] which is executed when the command button is clicked:

Code: Select all

--snip
Private Sub cbZionWorx_Click()
    DoCmd.TransferDatabase acImport, "dBase III", "C:\Program Files\ZionWorx", acTable, "songs.dbf", "ZionWorxSongs", True
    DoCmd.OpenQuery "qZionWorx"
    DoCmd.TransferDatabase acExport, "dBase III", "C:\Program Files\ZionWorx", acTable, "ZionWorxSongs", "addsongs.dbf"
    DoCmd.DeleteObject acTable, "ZionWorxSongs"
End Sub
--endsnip

The code creates a temporary table called ZionWorxSongs which has the structure of the Zionworx song.dbf file. It runs the MS-Access append query "qZionWorx" to populate the ZionWorxSongs table. It exports the ZionWorxSongs table to a new (or replaced) dBaseIII file called addsongs.dbf. Finally it deletes the temporary table ZionWorxSongs.

Then I created the MS-Access append query. My Access database has a master table - called SNGLISTS. It has the following fields in it which are relevant to this export process: TITLE, WORDS, AUTHOR, MAIN_KEY, COPYRIGHT

I created the Append query (qZionWorx) in my Access database. In my case it was a little complex, but this shows what can be done. In some cases where I had no words I defaulted the text to "not yet entered", as ZionWorx seemed to not like it when there were no words on import. The SQL code behind the append query was as follows:

Code: Select all

INSERT INTO ZionWorxSongs ( TITLE_1, TITLE_2, LYRICS, WRITER, COPYRIGHT, [KEY], CATEGORY )
SELECT SNGLISTS.TITLE AS TITLE_1, "" AS TITLE_2, IIf(Len([snglists].[WORDS])>0,[snglists].[words],"not yet entered") AS LYRICS, IIf(Len([AUTHOR])>0,[AUTHOR],"TBC") & IIf(Len([MAIN_Key])>0,"  Key: " & [MAIN_Key],"") AS WRITER, IIf(Len([SNGLISTS].[COPYRIGHT])>0,[SNGLISTS].[COPYRIGHT],"tbc") AS COPYRIGHT, "" AS [KEY], "" AS CATEGORY
FROM SNGLISTS;

With these two steps done in MS-ACCESS, I can now click on the command button in my Access form. The temporary table structure is created from ZionWorx, it is populated by my append query, written out and then deleted.

I then launch ZionWorx, unlock the database, select the import option, select the addsongs.dbf file (stored in c:\Program Files\ZionWorx) and import what I need (or everything).

I also tried this overwriting the ZionWorx songs.dbf file by changing "addsongs.dbf" to "songs.dbf". It does work, this negates the need for the import withing ZionWorx.

The bottom line for us is that our own DB is the master database, and ZionWorx is the output handler.

NB: This was done with Access 2000. If it fails, it may be you need the dBase III drivers installed.

[Christian]

I guess I need to learn a bit more Access

Good job isotope... so are you actually using an Access front end to manage your songs database then? Clever stuff...I believe we have some DB gurus in our midst

[Williamting]

I was using Access DB to keep all my songs as it has other information which I felt was useful. Thanks for your information. I just realised that I am not alone.

God bless