Database file access

[dwb]

Are the data files (DBF) files for PowerChurch dBase or another accessible format? I would like to have the ability to use an external reporting tool to gernerate data analysis. If not, does PowerChurch offer any ODBC drivers for the proprietary files?

[Jeff]

PowerChurch is written in Microsoft Visual Foxpro. FoxPro uses a derivitive of the dBase file structure and not directly compatible with a plain dBase driver. An ODBC driver for FoxPro tables is available here: http://msdn.microsoft.com/vfoxpro/downl ... fault.aspx

[Andrew Turner]

Here's a scary user for you. I'm an experienced developer of 20+ years at a large Christian ministry trying to add effeciency to the things we do at my church with Power Church. I fully understand the consequences of breaking data and would never call tech support for a problem I created. I sympathize with your nervousness over users like me. I feel your pain. I do believe in backups and testing. However, I need to be able to update our files using other tools I've developed to streamline data entry.

For example, I can successfully export names from ME.dbf to an Access work file and present the list to a user who then clicks the names of people that were at a given service. I can then successfully SQL append the correctly formatted results back into ATDATA.dbf. Works great! Saves hours. However, I am prevented from other simple tasks I need to improve. I don't seem to be permitted to do SQL updates or deletes. This is a great disappointment. Is this maybe tied to the version of MDAC I have? Or do you have it somehow locked? There are other projects we want to do and I don't want to have to build an app using a separate (and redundant) name and address structure.

If I sign a release form in blood can you tell me how this can be done? I don't want to buy/learn FoxPro (Access is free with Office) and I don't want to toss out PowerChurch and rebuild an entire package to gain 10% more functionality.

Please help a fellow developer help his church.
Thanks.

[Jeff]

What is the text of the error messages? We have not locked out updates to the data from external sources. While we can't support third party products, we will answer basic questions.

As a test I just tried linking from an Access 2000 database to the MA table in PowerChurch. I was able to see and update records. I had to setup an ODBC linked table in Access. One curious point was that I had to setup it up as "Machine Data Source" not a "File Data Source" to get this to work. (I don't know what the difference is.) Until I did that I was getting error -7778 in Access. I found the answer to this problem here: http://www.basis.com/support/kb/kb00509.html

I hope this helps, I'm sorry that we can't help too much with Access. But the fact is we don't have a need to use it. We have been working in VFP for 12 years now and before that Clipper. Any database work we do, we do in VFP including quick and dirty apps. There are many knowledge base articles on msdn.microsoft.com about getting to VFP data files from Access.

[NeilZ]

Here's a scary user for you.

For example, I can successfully export names from ME.dbf to an Access work file and present the list to a user who then clicks the names of people that were at a given service. I can then successfully SQL append the correctly formatted results back into ATDATA.dbf. Works great! Saves hours.

Please help a fellow developer help his church.
Thanks.

Just wondering ... why are you doing this when this function is already available in PC+ under the Mass Update Attendance module ?? This question from a someone who was (at one time) a IBM certified DB2 developer, and a 20 year programmer.

[Andrew Turner]

Just wondering ... why are you doing this when this function is already available in PC+ under the Mass Update Attendance module ?? This question from a someone who was (at one time) a IBM certified DB2 developer, and a 20 year programmer.

Incidently, I mentioned my experience only to point out that I wasn't a dangerous, over confident teenager. I didn't mean to sound like I was bragging. I'm just a very careful, thorough programmer type person.

Long story... Our pastor wanted to use a Welcome card approach similar to Rick Warren's Saddleback Church (See his book Purpose Driven Church). This card has name & address info on the front and check boxes for decisions made/info requested on the back. The cards were printed and passed out and information collected before any data people had a chance to think out what we'd do with them (hence the challenge of trying to borrow ideas without fully planning). These cards are filled out by EVERYONE, not just visitors EVERY week. Lots of data.

I wanted to try to use existing software as much as possible without writing a new "Welcome card system" from scratch. I decided (based on what I could tell from studying PowerChurch) to use the SKREF file to hold the codes for the check boxes and the ATDATA file to collect the card results because it could maintain the many to many relate between ME and SKREF with multiple hits on DATE. Some sample codes are:

skill_no desc
500 Sunday 10:30am Service
510 Wednesday 6:30pm Adult Service
511 Wednesday 6:30pm Youth Service
1000 Committing my life to Christ
1001 I want to be baptized
1002 Renewing commitment to Christ
1009 How to join this church family
1011 Adult Small group info
1012 Business/Professional Act info
1013 Single Activities info
1014 Single Parent Activities info
1015 Women's Activites info
1032 1st Time Guest
1033 2nd Time Guest
1040 Missions Info
1200 Pr: General
1201 Pr: Finances
1202 Pr: Marriage
1210 Pr: Family Issues
1211 Pr: Direction / Guidance

So for any given Sunday, a person can attend Sunday (500), request Singles info (1013) or request prayer for their marriage (1202). Obviously, 95% of the new data is a 500. The exceptions are keyed manually through normal screens. But keying the 200 attendance cards through normal screens takes a life time. I looked at the Membership / Attendance / Fast Data Entry but that doesn't seem to fit the bill. Did I miss something and re-invent the wheel? I must be able to specify EACH week who was there.

So I got the FoxPro ODBC driver and built a mini system in Access to speed it up. I dumped a query of the ME table into a work file and presented the list on screen. A column exists with a check box which when checked indicates "present". I then take all those with "present" and form 500-Sunday records for those persons on that date and append them to ATDATA. I love it when a plan comes together!

The Access SQL for the append is similar to this:
INSERT INTO atdata ( mail_no, pers_no, [date], skill_no, status )
SELECT 9 AS mail_no, 1 AS pers_no, #8/24/2004# AS [date], 500 AS skill_no, "P" AS status;

However, I'd like to be able to do other things like alternative screen presentation of the files for easier editing of some of the information but SQL update in Access doesn't work. I don't think it's a syntax issue because the Design View generated the code. For example, if I wanted to change an attendance status from "P" to "N" (and I never would really, but just to illustrate) in Access the statement would be like this:

UPDATE atdata SET atdata.status = "N"
WHERE (((atdata.mail_no)=9) AND ((atdata.pers_no)=1) AND ((atdata.date)=#8/1/2004#));

This bombs with "Operation must use an updateable table."

Similarly, if I wanted to delete the offending record, I'd use this:
DELETE atdata.*
FROM atdata
WHERE ((atdata.mail_no=9) AND (atdata.pers_no=1) AND (atdata.date=#8/24/2004#));
This bombs with "Could not delete from specified tables."

My ODBC connection is a User DSN named PowerChurchData doing a Free Table directory using MicroSoft Visual Foxpro Driver version 6.01.8629.01.

So, SELECTS and INSERTS work, but DELETES and UPDATES don't. The issue is WHY?

If you're still awake after all that, I'd appeciate hearing from you.
Thanks.
Andrew Turner

[NeilZ]

So for any given Sunday, a person can attend Sunday (500), request Singles info (1013) or request prayer for their marriage (1202). Obviously, 95% of the new data is a 500. The exceptions are keyed manually through normal screens. But keying the 200 attendance cards through normal screens takes a life time. I looked at the Membership / Attendance / Fast Data Entry but that doesn't seem to fit the bill. Did I miss something and re-invent the wheel? I must be able to specify EACH week who was there.


If you're still awake after all that, I'd appeciate hearing from you.
Thanks.
Andrew Turner

I will defer to the folks at PC+ on why you can't update the tables, however, I suspect it has something to do with table permissions.

But you have missed something on the the Attendance fast updating. We use something like the cards to track attendence of all visitors and members. Each week they are collected from pads in the pews, checked for visitors, then passed on to me for update.

By this time the visitors have been entered in to PC+ by the Evangelism committee volunteers, they have also added them to the Sunday worship activiity. I then open the Fast Update box, select the Sunday Worship activiity (in our case 101), select the date (usually the previous Sunday), then start typing the last name of the first person on the list.

The name appears, I hit the space bar to change the A to a B and then start entering the last name of the next person on the list. If its the person's spouse, they are usually the next name on the list, so you up or down arrow, then hit the space bar.

I can do over 400 names in less than 15 or 20 minutes.

[Andrew Turner]

But you have missed something on the the Attendance fast updating. We use something like the cards to track attendence of all visitors and members.

The name appears, I hit the space bar to change the A to a B and then start entering the last name of the next person on the list. If its the person's spouse, they are usually the next name on the list, so you up or down arrow, then hit the space bar.

I can do over 400 names in less than 15 or 20 minutes.

I just tried that (is it Membership / Attendance / Fast Data Entry?). I do 500, 09/05/2004, Present and it tells me that no one was found. What's that all about? I've got 500 names in there. Am I in the right screen? We are running version 8.5.

Thanks for your interest.

[NeilZ]

But you have missed something on the the Attendance fast updating. We use something like the cards to track attendence of all visitors and members.

The name appears, I hit the space bar to change the A to a B and then start entering the last name of the next person on the list. If its the person's spouse, they are usually the next name on the list, so you up or down arrow, then hit the space bar.

I can do over 400 names in less than 15 or 20 minutes.

I just tried that (is it Membership / Attendance / Fast Data Entry?). I do 500, 09/05/2004, Present and it tells me that no one was found. What's that all about? I've got 500 names in there. Am I in the right screen? We are running version 8.5.

Thanks for your interest.

Each member must have the activity 500 added through the activity function. This initial setup is time consuming, but then as new names are added, this becomes a matter of course. I'm not sure, but if they are not setup for that entry, I don't think you could even add attendence, which may have been causing your intiial problem.

Also, I would NOT use present as a default option under Fast Data Entry. When you use this function, the default should be absent, and you then switch the entry to P if they did attend.

[Andrew Turner]

Each member must have the activity 500 added through the activity function. This initial setup is time consuming, but then as new names are added, this becomes a matter of course. I'm not sure, but if they are not setup for that entry, I don't think you could even add attendence, which may have been causing your intiial problem.

Also, I would NOT use present as a default option under Fast Data Entry. When you use this function, the default should be absent, and you then switch the entry to P if they did attend.

Obviously, since I haven't tried this yet with a loaded activity list, I won't fully follow how this works. I can't get to the main screen yet. Sounds like doing this list your way is kinda preloaded as absent until I find the person and flip them to present. Then the whole list of presents and absents is sent to ATDATA. Is that how it works?

If so, yuck. Two problems here. One, if I don't add them to this activity list, they won't appear on screen to get marked as present. Why can't they present a list of all the people that are in ME? Is the activity list a sort of filter against fighting with too many names? Maybe that's a justifiable annoyance.

Two, I really don't want to create records for people that are absent, just present. Who cares about absent? Is there a reporting advantage that I'm missing? The problem we have with logging absences is that they may be here. Filling out a card is voluntary and I'd guess we get a 70% participation from those that come. But if we get a card, we really do KNOW they were here. I confess to not know this package very deeply.

Anyway, we've been talking about approaches to the attendance issue. I'm open to checking out your suggestions more thoroughly. What I'd still dearly love to find out is how to do SQL UPDATES and DELETES should I need that for other issues we are considering. Any ODBC Gurus lurking out there?

But thanks Neil, I AM grateful for your help.

[NeilZ]

Each member must have the activity 500 added through the activity function. This initial setup is time consuming, but then as new names are added, this becomes a matter of course. I'm not sure, but if they are not setup for that entry, I don't think you could even add attendence, which may have been causing your intiial problem.

Also, I would NOT use present as a default option under Fast Data Entry. When you use this function, the default should be absent, and you then switch the entry to P if they did attend.

Obviously, since I haven't tried this yet with a loaded activity list, I won't fully follow how this works. I can't get to the main screen yet. Sounds like doing this list your way is kinda preloaded as absent until I find the person and flip them to present. Then the whole list of presents and absents is sent to ATDATA. Is that how it works?

If so, yuck. Two problems here. One, if I don't add them to this activity list, they won't appear on screen to get marked as present. Why can't they present a list of all the people that are in ME? Is the activity list a sort of filter against fighting with too many names? Maybe that's a justifiable annoyance.

The reason you don't get a list of everyone is for the same reason you may not want a list of everyone who attends a bible study. You want a superset of the main database of personal profiles. For example, our database contains personal profiles of former members, who we don't want to see pulled up in the Worship Attendence.. When they are removed from the rolls, they are also removed from the activities.

We cannot delete them from the database as we need to maintain the data for end-of-year reporting to Presbytery and the GAC. They may also have contributions that we need to account for, and send an end-of-year statement. So removing them from the activity prevents them from appearing.

Two, I really don't want to create records for people that are absent, just present. Who cares about absent? Is there a reporting advantage that I'm missing? The problem we have with logging absences is that they may be here. Filling out a card is voluntary and I'd guess we get a 70% participation from those that come. But if we get a card, we really do KNOW they were here. I confess to not know this package very deeply.

I take it you don't track attendence of members ?? We do in order to insure that people are not dropping out of view and nobody is noticing. Tracking non-attendence of members is just as important as tracking visitors coming in the door. Once you get them in the front door as a member, you need to keep them going out the back door as former members.

Our deacons and Elders are given a list of people who have not been attending for over 3 or 4 months, and we then contact them to see if there is a family problem, if they are unhappy with something in the church or if they left the area without notifying the church office. The pastor is notified of issues he may not have been aware of. When we were a church of 125, this was not a issue as everyone knew everyone else, now that we're hitting 550; the pastor, the Elders and the Deacons do not know everyone personally.

Once a year we then do a review and move those who have not been attending to 'inactive' status, which is a step from removing them from the rolls. This may have more consequence in the Presbyterian Church as each year the church contributes a 'per capita' amount to support the Presbytery and GAC. Every member listed as active on our rolls, requires a contribution to be sent.

Anyway, we've been talking about approaches to the attendance issue. I'm open to checking out your suggestions more thoroughly. What I'd still dearly love to find out is how to do SQL UPDATES and DELETES should I need that for other issues we are considering. Any ODBC Gurus lurking out there?

But thanks Neil, I AM grateful for your help.

Again, I suspect that part of the update issue is that you don't have an activity record for each individual. In this case I believe you have a hierarchal database with the activity record as the master, and the attendence records tied to that record.

[Andrew Turner]

Neil, thanks for the explanation of how you do things. We ARE interested in closing the back door. Certainly we have more to learn about the package. Your example is helpful.

We've been through a difficult pastoral transition over the last three years. We are now recovering and growing again under a wonderful new pastor. Those of us that remain are trying to use the package but are not fully aware of how it works in every detail. How do you insure that the people that come fill out your card? That is the sticking point for us that causes us to not view "Absence" data as reliable. I promise to devote more energy to researching what is in there.

BUT, I still wish someone could help me crack this SQL issue. I can link a file in Access and open it to see it, but can't change the data that I see. I can double-click & open ATDATA in Access and see it, but I can't type an "N" in the status of a record there. Why?

Thanks.

[Jeff]

When you created the linked table in Access, it comes up with a dialog box asking you to select the unique record identifier, what fields did you select?

DBF files are unique that you don't necesarily have a primary key for each record. For AtData you would use Mail_No, Pers_No, Date, Skill_No as the unique record identifiers. This would work as a primary key for this table as long as you don't record attendance twice on the same day for the same activity.

I wonder if the updateable table issue has to do with access determining there is not a primary key. I don't know if access is smart enough to realize that you didn't select to correct primary key fields when linking the database, and then changes it into a read only table.

Have you tried updating other tables in PC+? Do you receive the same error when you do? The MA table has the easiest primary key, it is just the mail_no. The primary key for the ME table is a compound key of mail_no and pers_no.

As far as atendance in PC+, in order to use the Fast Data Entry screen, a person has to be a member of the activity. We look at the current membership of the activity and then add all of them to atdata at once with the status selected by the user. Keep in mind attendance is most often used with Sunday School classes. Records are added even when someone is absent. This allows us to produce reports like the consecutive absentee report to flag people that have missed a number of weeks in a row.

You can also enter attendance for some one who is not a member of the activity, 2 different ways:

In the Fast Data Entry Screen:
Click the Add Person Button
A dialog appears with a list from Personal Profiles. In the upper right corner, there is a check box that says "Permanently add to this activiy", by checking that box, the person selcted will be added to the activity and added to the attendance for that date as well. (ie Sk & AtData) If you do not check that option, their attendance will be recorded, but they will not be added to the activity roll. (ie only AtData)

In the Maintain Attendance screen, a person does not have to be a member of an activity to have attendance recorded for them. Where this is commonly done is in denominations where they record when a person takes communion. In this case they don't care about absences, they go to the Maintain Attendance screen, and Add the person as present when they took communion.

I hope this is of some use. I am using Access 2000, and have been updating the status field in AtData and the change is being made back in the real table. I'm not using SQL commands to do so, I am just going into the table and changing the field.

[NeilZ]

How do you insure that the people that come fill out your card? That is the sticking point for us that causes us to not view "Absence" data as reliable. I

Thanks.

You can't. However, we use a 'pew pad' or 'Friendship Pad' approach to getting attendance for members and visitors. Iin the pews, we have a vistor's packet that has other info, so we tend to differentiate between them.

However, the attendance is but one way of verifying active members, the other is if they are regular givers. I have one family who never signs the pad, but their envelope is always in the plate. We verify against both areas before we forward the member's name for pastoral followup !!

[Andrew Turner]

When you created the linked table in Access, it comes up with a dialog box asking you to select the unique record identifier, what fields did you select?

DBF files are unique that you don't necesarily have a primary key for each record. For AtData you would use Mail_No, Pers_No, Date, Skill_No as the unique record identifiers. This would work as a primary key for this table as long as you don't record attendance twice on the same day for the same activity.

Good thought, but I did select the right key fields when I linked them. It doesn't seem to matter. For example, on SKREF, the skill_no is obviously the key but after I create one with this SQL:

INSERT INTO skref ( skill_no, [desc] ) SELECT 550 as skill_no, "test skill" as [desc];

I can't then update the same record I created with any possible variant of this SQL:

UPDATE skref SET [skref].[desc] = "new test"
WHERE [skref].[skill_no] = 550;
Instead, I get: Operation must use an updatable query. (Error 3073)

And I can't delete it with ant version of this SQL:
DELETE skref.* from skref WHERE skill_no = 550;

Quite frustrating.