Help with a simple database

[Guest_imported]

I have a simple 5 table Access database which I am using. I am adding all the tables into the program through the MFCWizard. When I try to add a record to the database, I get a "Recordset is read-only" error. I've heard that I need to make a query of some sort in Access, or use other dialogs to bypass this error. Can anyone help with this dilemma? Thank you for your help.

 

[tlbroadbent]

A couple of things you might check.

1- Does the table have a primary key?
2- Are you opening the recordset with the correct options for updatability?
Terry

http://members.home.net/tlbroadbent/index.htm

 

[Guest_imported]

All of the tables have primary keys. The MFCWizard handled the reading of the fields and such. The code I got from my instructor works, but has the error when I try to add or delete a record.

If anyone knows how to create a query table in Access, and have the application use it to point to the database, please let me know.

Thank you for your time.

 

[Guest_imported]

I've put together a query with all the fields from the 5 tables. I got a huge query table now with no records in it. The original 5 tables had records in them. So I added a giant row of info into the query fields but to no avail. The field won't display.

I attempt to run the application and show a field in a text box, but I get the following error.

" is not a valid name. Please ensure that it does not have any invalid characters or punctuation and that it is not too long.

I am in a very tight bind here. If anyone can put together a mock database with 3 tables or so and create a query table. And if you can also create an application using the wizard and try displaying the fields.

I am very desperate for help. I would greatly appreciate any assistance anyone can offer me. Thank you very much for your time.

Gene Como

 

[tlbroadbent]

Gene,

How are you attempting to add records? What program do you use? Have you tried adding records to each table individually in Access? Can you post your "huge query" here so it can be analyzed?

I'd like to help but still don't know enough to do much more.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[Guest_imported]

I am attempting to add and delete records using Visual C++. The database interface is set, but I'm having a hard time getting the query together. I will send you the information. Thanks for your help.

Gene Como

 

[tlbroadbent]

Gene,

The problem is the query you’ve created. The query is read-only because it has multiple occurrences of the same column names. Four of the tables have MemberID so MemberID appears four times in the query result. ISBN and title appear twice. Access can't create updateable results under those conditions.

When you create a query, you cannot Select * from all the tables in the query unless all of the columns are uniquely named. You must select by column and where a name is duplicated, it must be aliased or left off the result list. In most cases, we would leave the duplicate column off the list. There is usually no reason to list it if it is the key field that establishes the relationship between the tables.

You normally would not update all the tables at the same time. You would update Loaned when an item was checked out or returned. You would update Patron when new members were added, addresses changed, or membership was renewed. These are just a few examples but you can see that updates only involve one or a few tables at most - never all the tables.

No rows show in the result your huge query because all of the tables are included and are related by an inner join. For any rows to be returned every join condition must be met. A patron must have checked out an item, owe a fine, donated an item and the loaned item must be listed by title in the items table. These exact conditions do not occur in your data.

It makes sense to create a query joining the Loaned table to the Patron table and the Item table. But you should only select specific columns in such a join - not all columns in the three tables. You could also determine relationships between other tables and create queries accordingly.

I recommend reading in Access Help about joins - specifically inner joins and outer joins for now. Read about working with relationships so you'll come to understand how and why to create joined queries.

Remember that all queries don't have to include multiple tables joined together. We often query one table for various reasons. For example, one might query the Patron table for name and address info to create mailing labels.

I also recommend obtaining a book about Access. There are several that would cover these basic issues and help you learn more quickly.

I hope this is helpful. Post any additional questions. Perhaps others in the forum have some helpful ideas. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Gene,

One more thought - Your original question was better suited to an Access forum rather than an ODBC forum. Perhaps, the reason you didn't get very quick response is that the real Access wizards are in other forums. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Guest_imported]

Thank you.