"steve" <noemail> wrote in message
news:u8FEkYNgGHA.3900@TK2MSFTNGP05.phx.gbl...
> We're beginning a major rewrite of a VB6 application that currently uses
> ADO
> and an Access database.  In this application, we routinely query  multiple
> table through JOINs and place the result in a single connected record set
> that is often bound to a grid for display and editing by the user.
> Changes
> to the database are implemented by the record set's Update command.
>
> We're looking to rewrite using VB.NET in VS2005 and either SQL Express or
> Access (haven't decided yet).  We can seem to get a handle on how to
> acheive
> the same sort of data handling in ADO.NET 2.0 as we used in ADO, that is,
> query into multiple table combined into a single entity that can be bound
> to
> a grid for display and editing by the user, and with a simple Update
> command
> updating the database tables.
>
> There are plenty of examples of this using single tables, but how do we go
> about combining data from multiple db tables via query JOINs, and then
> binding the result to a single grid or view for display and editing, and
> then updating the tables?
>
> Can someone point us to samples or examples illustrating the best way to
> do
> this?  TIA!
>
>

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:eOzZ9dNgGHA.4304@TK2MSFTNGP05.phx.gbl...
> The good news is that in ADO.NET you don't have to use a JOIN to fetch,
> bind and update data from multiple tables.  The ADO.NET DataSet lets you
> use multiple related tables.  In general this much better than binding to
> join tables.
>
> However, you can use a JOIN in ADO.NET.  The specifics depend on the
> back-end.  In SQL Server (Express) you would create a VIEW in the database
> and use it just like a table.  So long as your updates affect only one
> base table, it all just works.  If your updates target multiple tables you
> can define INSTEAD OF triggers on the view to route the changes to the
> appropriate base tables.
>
>
> David
>
>

"steve" <noemail> wrote in message
news:Okps4cOgGHA.4864@TK2MSFTNGP05.phx.gbl...
> David,
>
> Thanks for the reply.  We're too early in the process to know if we're
> going to use SQL Express or Access or to know if and what relationships
> will be defined in the db.
>
> However, it's not clear to me why using multiple related tables in ADO.NET
> datasets is better than binding to joined tables for updates.  Could you
> elaborate or steer me to a reference?
>
> Steve
>
> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
> message news:eOzZ9dNgGHA.4304@TK2MSFTNGP05.phx.gbl...
>> The good news is that in ADO.NET you don't have to use a JOIN to fetch,
>> bind and update data from multiple tables.  The ADO.NET DataSet lets you
>> use multiple related tables.  In general this much better than binding to
>> join tables.
>>
>> However, you can use a JOIN in ADO.NET.  The specifics depend on the
>> back-end.  In SQL Server (Express) you would create a VIEW in the
>> database and use it just like a table.  So long as your updates affect
>> only one base table, it all just works.  If your updates target multiple
>> tables you can define INSTEAD OF triggers on the view to route the
>> changes to the appropriate base tables.
>>
>>
>> David
>>
>>
>
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:epW9koQgGHA.3916@TK2MSFTNGP04.phx.gbl...
> Steve, I'm giving a day-long workshop on ADO.NET 2.0 architectures and
> best practices at the VSLive (Vegas) conference. I think this could go a
> long way to answering your questions. In addition, my APress book ("ADO
> and ADO.NET Examples and Best Practices") was designed specifically for
> you. It walks through the issues faced  by developers with the same
> challenges--migrating from VB6 to VB.NET. While it does not discuss the
> 2.0 version of ADO.NET (that book is in edit), the core discussion will
> help a lot and it's written with VB developers in mind. I would also be
> happy to spend a few minutes on the phone to help get you started out on
> the right foot. IMHO, it would be a mistake to stick with JET/Access at
> this point in the technology. While JET can seem like an easy-to-use
> engine, MS is doing whatever it can to back away from this dated engine.
> SQL Express is far more mature (it's based on the SQL Server binaries that
> have been around for a long time). It also might be overkill so I'm
> encouraging developers to consider SQL Everywhere as a lightweight
> alternative. I discuss all of these issues in my workshop.
>
> hth
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
> "steve" <noemail> wrote in message
> news:Okps4cOgGHA.4864@TK2MSFTNGP05.phx.gbl...
>> David,
>>
>> Thanks for the reply.  We're too early in the process to know if we're
>> going to use SQL Express or Access or to know if and what relationships
>> will be defined in the db.
>>
>> However, it's not clear to me why using multiple related tables in
>> ADO.NET datasets is better than binding to joined tables for updates.
>> Could you elaborate or steer me to a reference?
>>
>> Steve
>>
>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
>> message news:eOzZ9dNgGHA.4304@TK2MSFTNGP05.phx.gbl...
>>> The good news is that in ADO.NET you don't have to use a JOIN to fetch,
>>> bind and update data from multiple tables.  The ADO.NET DataSet lets you
>>> use multiple related tables.  In general this much better than binding
>>> to join tables.
>>>
>>> However, you can use a JOIN in ADO.NET.  The specifics depend on the
>>> back-end.  In SQL Server (Express) you would create a VIEW in the
>>> database and use it just like a table.  So long as your updates affect
>>> only one base table, it all just works.  If your updates target multiple
>>> tables you can define INSTEAD OF triggers on the view to route the
>>> changes to the appropriate base tables.
>>>
>>>
>>> David
>>>
>>>
>>
>>
>
>

"steve" <noemail> wrote in message
news:eiAi5fAhGHA.4776@TK2MSFTNGP05.phx.gbl...
> Bill,
>
> Thanks for info. Unfortunately, I won't be attending VSLive.  Any chance
> of workshop appearing on-line?
> Any idea when the 2.0 version of the book will be published?  It looks
> like it could be very helpful.  And thanks for offering your help.  When
> we know more about what we don't know, I might take you up on your offer.
> Contact you first via email?
>
> Thanks again.
>
> Steve
>
> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
> news:epW9koQgGHA.3916@TK2MSFTNGP04.phx.gbl...
>> Steve, I'm giving a day-long workshop on ADO.NET 2.0 architectures and
>> best practices at the VSLive (Vegas) conference. I think this could go a
>> long way to answering your questions. In addition, my APress book ("ADO
>> and ADO.NET Examples and Best Practices") was designed specifically for
>> you. It walks through the issues faced  by developers with the same
>> challenges--migrating from VB6 to VB.NET. While it does not discuss the
>> 2.0 version of ADO.NET (that book is in edit), the core discussion will
>> help a lot and it's written with VB developers in mind. I would also be
>> happy to spend a few minutes on the phone to help get you started out on
>> the right foot. IMHO, it would be a mistake to stick with JET/Access at
>> this point in the technology. While JET can seem like an easy-to-use
>> engine, MS is doing whatever it can to back away from this dated engine.
>> SQL Express is far more mature (it's based on the SQL Server binaries
>> that have been around for a long time). It also might be overkill so I'm
>> encouraging developers to consider SQL Everywhere as a lightweight
>> alternative. I discuss all of these issues in my workshop.
>>
>> hth
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>> "steve" <noemail> wrote in message
>> news:Okps4cOgGHA.4864@TK2MSFTNGP05.phx.gbl...
>>> David,
>>>
>>> Thanks for the reply.  We're too early in the process to know if we're
>>> going to use SQL Express or Access or to know if and what relationships
>>> will be defined in the db.
>>>
>>> However, it's not clear to me why using multiple related tables in
>>> ADO.NET datasets is better than binding to joined tables for updates.
>>> Could you elaborate or steer me to a reference?
>>>
>>> Steve
>>>
>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
>>> message news:eOzZ9dNgGHA.4304@TK2MSFTNGP05.phx.gbl...
>>>> The good news is that in ADO.NET you don't have to use a JOIN to fetch,
>>>> bind and update data from multiple tables.  The ADO.NET DataSet lets
>>>> you use multiple related tables.  In general this much better than
>>>> binding to join tables.
>>>>
>>>> However, you can use a JOIN in ADO.NET.  The specifics depend on the
>>>> back-end.  In SQL Server (Express) you would create a VIEW in the
>>>> database and use it just like a table.  So long as your updates affect
>>>> only one base table, it all just works.  If your updates target
>>>> multiple tables you can define INSTEAD OF triggers on the view to route
>>>> the changes to the appropriate base tables.
>>>>
>>>>
>>>> David
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"steve" <noemail> wrote in message news:eVf3HtLhGHA.3916@TK2MSFTNGP04.phx.gbl...
> I've been following some of the discussions regarding Access and SQL/x and
> SQL/e and such.  Many of our customers have small networks (< 10
> workstations), with one workstation used as a file server.  No domain
> server, etc.  Our users share our application's Acces database through a
> shared network folder.  They don't want and can't afford SQL Server.
>
> We like Access because of the ease of installation, small footprint (even
> including MDAC and Jet).  We don't like Access because of it seems prone to
> corruption, kind of  slow, and it's not quite as easy to deal with from .NET
> apps as it is from VB6, and most importantly, we can't get a good handle on
> how much longer it's going to be around.
>
> SQL/e sound perfect for us -  except I've seen that it won't support sharing
> the database file.  SQL/x would work for us, I think, but presents
> installation and administration issues for our customers.  Migration to SQL
> Server is not in our future, so this is not a factor.
>
> Any advice?
>
>

  "steve" <noemail> wrote in message news:eVf3HtLhGHA.3916@TK2MSFTNGP04.phx.gbl...
  > I've been following some of the discussions regarding Access and SQL/x and
  > SQL/e and such.  Many of our customers have small networks (< 10
  > workstations), with one workstation used as a file server.  No domain
  > server, etc.  Our users share our application's Acces database through a
  > shared network folder.  They don't want and can't afford SQL Server.
  >
  > We like Access because of the ease of installation, small footprint (even
  > including MDAC and Jet).  We don't like Access because of it seems prone to
  > corruption, kind of  slow, and it's not quite as easy to deal with from .NET
  > apps as it is from VB6, and most importantly, we can't get a good handle on
  > how much longer it's going to be around.
  >
  > SQL/e sound perfect for us -  except I've seen that it won't support sharing
  > the database file.  SQL/x would work for us, I think, but presents
  > installation and administration issues for our customers.  Migration to SQL
  > Server is not in our future, so this is not a factor.
  >
  > Any advice?
  >
  >