Editing the database in Access

If you need help and cannot find it in the FAQ area of this forum feel free to ask the question and someone will reply back.

Moderators: webmaster, Christian, Kuok-Tim, ChrisClark, Forum Support

Editing the database in Access

Postby cjh1984 » Fri Oct 24, 2003 4:16 pm

I was just wondering, is it possible to open the Zionworx database files in Microsoft Access or any other database software? I have tried to open them in access but it asks me for a database index file. Any help with this would be great.

Catchya all lata

Chris
cjh1984
Junior
 
Posts: 2
Joined: Fri Oct 24, 2003 4:13 pm
Location: Warrington

Postby intuitas » Sat Oct 25, 2003 6:24 am

Hi Chris,

You will probably need to import/export the Zionworx database to Access.

The following instructions are for Aceess 97/2000 - the 2002/XP/2003 versions should be similar.

1. Save a copy of the Zionworx database file songs.dbf somewhere safe.

2. Create a new MS Access database db1 will do unless you have already saved that for some other purpose.

3. When the database opens go to File/Get External Data/Import

4. With the dialog box open navigate to the Zionworx directory(folder). [Mine is in Program Files/Zionworx]

5. You will not see a file to import until you change the file format at the bottom of the dialog box {Files of Type:) - use dBase 111.
When you have changed this you will see songs.dbf as the file to import.
Double click on this to start the import.

6. You should now have a table called SONGS.

7. Create a new form in Design Mode using this table
Drag fields one by one to the form - I suggest the non-lyrics fields one above the other on the left and the lyrics field expanded to occupy virtually all of the right side. Save it as frmSongs or some such. (Play around with the sizes when you have opened the form view and can see how much of each field is displayed.

With this simple set up you should be able to add/edit/delete songs. Be careful about delete (see below).

8. When you have finished your changes highlight the table in the database window and select File/Export.
If you have only added or edited songs then save as songs.dbf (ie. songs and select dbase 111 format).

As an alternative you can copy the structure of the SONGS table to a new table and use another form to add/edit new songs. Export the new table to a new dBase 111 database then use the Import option in Zionworx to add songs to the existing database.

Hope this helps.

Paul
intuitas
Senior
 
Posts: 60
Joined: Wed Oct 01, 2003 4:02 pm
Location: Tasmania Australia

Postby Williamting » Sat Oct 25, 2003 11:54 pm

Paul,

On my PC, I can always open the songs.dbf directly. However, the icon for songs.dbf has a thick arrow (linked table). It also creates a mdb file. However, I don't need to export/import.

I am using Access 2000.

I normally use this facility to do mass update (update query). Example, to prefix Edd's SA database with an SA in front and also to create an "Alternative Title" without the song number.

God bless,
William
Last edited by Williamting on Mon Oct 27, 2003 12:57 am, edited 1 time in total.
Williamting
Most Senior
 
Posts: 146
Joined: Sat Sep 13, 2003 5:11 am
Location: Miri, Malaysia

Postby intuitas » Sun Oct 26, 2003 2:30 am

Thanks William :D ,

Its true that you can open the dbf file directly if you have your file open dialog set to All Files, Data Files or dBase 111 Files etc. or if you associate .dbf files with MSAccess. When you do so you may find (as I have done) that the recordset initially created is not updatable (I must investigate why this happened).
Rather than have this happen to those with less MSAccess experience the safest approach seemed to be the import/export option. To some extent it may depend on whether or not you are moving records (songs) from one machine to another We use a desktop machine permanently located in the church for presentation but other machines for editing the database.

Elsewhere I suggested that someone (maybe me) might develop a 'database companion' which would double as a standalone editor (avoid MSAccess, Excel or any other proprietory software) for the dBase 111 format file and would import suitable recordsets in other formats.

Also elsewhere Christian has talked about incorporating Impress (the Open Office.org equivalent of PowerPoint) which would then make the whole package independent of MS Application Software (which would be terrific)!

Blessings,

Paul
intuitas
Senior
 
Posts: 60
Joined: Wed Oct 01, 2003 4:02 pm
Location: Tasmania Australia

Can't Get This To Work

Postby NACarter » Thu Apr 22, 2004 2:54 pm

Hi,

I have been trying to get this method to work but am having trouble. I have created an Access 2000 database in the correct format, and exported in dbase III format. In ZionWorx I have tried to import the database. ZionWorx shows all the songs in my file correctly and then responds with "imported successfully". But when returning to the main ZionWorx window I find lots of blank songs - i.e. songs with no title and no contents.

Any help would be appreciated.

Nick
NACarter
Junior
 
Posts: 21
Joined: Mon Apr 19, 2004 1:22 pm
Location: Romsey, UK

Postby intuitas » Sat May 08, 2004 1:01 pm

Nick,

Did you import the .dbf file into Access in the first place or did you create an Access database from scratch.

I would recommend the former.

Rename your existing Access database (so you can still find the songs).
Restore the original songs.dbf file (as it came with Zionworx, or create a new blank one but add at least one song to it in ZionWorx).
Return to Access and import the songs.dbf file. This will create a table with the correct structure.
Import the data you have already created from the previous Access database into a second table.
Create an append query to transfer data into the Songs table Access created when you did the Songs.dbf import.
Finally export the Songs table only back to dbase format

Sorry if this is long winded- it is based on a hunch that the structure of the table you have exported is not quite right. If you allow Access to import the structure you want to re-export and only add data to it then it is less likely to give you trouble.

Thereafter you should be able to add more data and re-export, or import again if you have made changes through ZionWorx that you want to keep.

Regards,

Paul

PS - If this is confusing rather than helpful, let me know paul2@intuitas.com and I'll see if I can send you a Word document with a better layout and description.
intuitas
Senior
 
Posts: 60
Joined: Wed Oct 01, 2003 4:02 pm
Location: Tasmania Australia

Thanks

Postby NACarter » Mon May 10, 2004 10:46 am

Thanks Paul. I had already been given these instructions by William, and got it working successfully. As you suggested I guess there must have been something wrong with my exported file that was corrected by importing the original.

Nick
NACarter
Junior
 
Posts: 21
Joined: Mon Apr 19, 2004 1:22 pm
Location: Romsey, UK

Postby ed » Sat Feb 19, 2005 12:42 pm

i know its late, but, another way to do it is to use Works database. although you do have to change the file format, but its not a huge problem. Just thought id say this incase anyone doesnt have access (me!!)
ed
Junior
 
Posts: 20
Joined: Sun Feb 13, 2005 4:37 pm
Location: Bradninch


Return to Zionworx V2.0 Support

Who is online

Users browsing this forum: No registered users and 2 guests

cron