Update Access Query Data from VB.Net Form

I'm using VB.Net and an access database.  I have a query already created inside access that is linked to a dataset.  I used the vb.net data wizard to create a new form.  I now have a form called DataForm1 that shows all the fields inside my access data query.  Everything works fine except I'm wondering if there is a way to get the update button to work.  In Access if you change a value in the query the data in the table changes as well.  I would like to mimic that on my vb.net form.  Any ideas on if this is possible?
brettawvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
It will depend on the nature of the Access query.  In Access itself, some select queries are updateable and some are not.  For example, queries which join tables and/or use any of the aggregate functions are not.  So far as I know, if the select query was updateable in Access itself, it should be updateable from a VB.NET application: otherwise not.

Roger
0
brettawvAuthor Commented:
It is updateable.  The problem seems to be when it creates a dataset.  The program no longer looks at the actual table or query it seems to be looking at the xml file that is created for the dataset.  Is there a way to set it up so that it doesn't look at the xml duplicate.  I want it to write directly to database file.
0
SanclerCommented:
I'm assuming, from what you've said, that this is VB.NET 2003.  Is that correct?  If it is, there is no reason, from what you've said so far, why the update shouldn't work.  The Data Form wizard should have set it all up to handle that in the first place.

In particular, I don't understand at the moment what you mean by "The program no longer looks at the actual table or query it seems to be looking at the xml file that is created for the dataset."  The xml file is just the definition of the dataset.  It doesn't normally include any data, nor is it updated in any way by any changes to the data.

Are you getting any error messages?  In particular, does it say anything about Update requiring a valid Update (or Delete) command?

Roger
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

brettawvAuthor Commented:
The error I get says "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."  I read in another post somewhere that when you run the app it creates a duplicate of your data in the bin folder.  I checked the bin folder and saw a file with an extension .pdb.  I deleted it and when I ran the app again the .pdb file reappeared so I assumed this was a copy being created of the actual data being loaded.  I may be all wrong on this issue but that was the assumption I was making.  I'm new to using the data form wizard to create my data connections.  I've always created my connections through my own code in the past.  Any help or guidance is greatly appreciated.
0
SanclerCommented:
I don't think that the problem has anything to do with the Data Form wizard.  It looks like your Access query doesn't have (or your App doesn't recognise) a primary key.  Update commands - as you'll know from coding your own - are always in the format UPDATE thisTable SET thisField = thisValue ... WHERE IDField = thisID.  The last bit is essential so that the specified changes are made to the right record.  Similarly with a Delete command.  If a wizard cannot identify what field (or combination of fields) uniquely identifies each record in any set of data it is referred to, it simply does not produce the necessary Update and Delete commands.

That's my analysis of the problem.  Does it make sense in the light of what's included in your query?  And, if so, is there enough information there for you to solve it?

If not, come back with some details of what the uniquely identifying field/s should be and I'll see if I can help you to "roll your own" commands to fill the gaps left by the wizard.

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brettawvAuthor Commented:
Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.