Insert a whole datatable

I have two access database (with identical schema). One is the master
and the other is SHOULD be a subset of master data (Let call them
MasterDB and SubDB

The user enters data into the SubDB and I need to bring the new
changes to the MasterDB. Traditionally, I can get a the new changes
(into a dataTable). Loop through all the rows and insert them into the
MasterDB ONE AT A TIME.

My question: IS THERE A BETTER WAY?

For SQL Server, there is a SqlBulkCopy but I am dealing with MS
Access.

In my case, performance is not an issue, I just want write less
code.

Thanks

John

William
Fri Mar 09 12:44:33 CST 2007

I don't know that JET/Access supports replication, but that's the direction
I would take. This is supported (perhaps to the extent you need) with SQL
Server Compact Edition. Writing your own merge routine can be
tricky--especially with JET.

--
____________________________________
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Q. John Chen" <qjchen@email.com> wrote in message
news:1173461525.450037.143040@c51g2000cwc.googlegroups.com...
>I have two access database (with identical schema). One is the master
> and the other is SHOULD be a subset of master data (Let call them
> MasterDB and SubDB
>
> The user enters data into the SubDB and I need to bring the new
> changes to the MasterDB. Traditionally, I can get a the new changes
> (into a dataTable). Loop through all the rows and insert them into the
> MasterDB ONE AT A TIME.
>
> My question: IS THERE A BETTER WAY?
>
> For SQL Server, there is a SqlBulkCopy but I am dealing with MS
> Access.
>
> In my case, performance is not an issue, I just want write less
> code.
>
> Thanks
>
> John
>

Q
Fri Mar 09 13:34:17 CST 2007

On Mar 9, 12:44 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@nwlink.com> wrote:
> I don't know that JET/Access supports replication, but that's the directi=
on
> I would take. This is supported (perhaps to the extent you need) with SQL
> Server Compact Edition. Writing your own merge routine can be
> tricky--especially with JET.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.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 right=
s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -------------------------------------------------------------------------=
--=AD--------------------------------------------
>
> "Q. John Chen" <qjc...@email.com> wrote in messagenews:1173461525.450037.=
143040@c51g2000cwc.googlegroups.com...
>
>
>
> >I have two access database (with identical schema). One is the master
> > and the other is SHOULD be a subset of master data (Let call them
> > MasterDB and SubDB
>
> > The user enters data into the SubDB and I need to bring the new
> > changes to the MasterDB. Traditionally, I can get a the new changes
> > (into a dataTable). Loop through all the rows and insert them into the
> > MasterDB ONE AT A TIME.
>
> > My question: IS THERE A BETTER WAY?
>
> > For SQL Server, there is a SqlBulkCopy but I am dealing with MS
> > Access.
>
> > In my case, performance is not an issue, I just want write less
> > code.
>
> > Thanks
>
> > John- Hide quoted text -
>
> - Show quoted text -

Thanks for the response. The access was using replication for sync
data - What a mess! I got involved just because of this.

While the criteria for the new changes of the data is not hard to
define, the table is so wide that I don't want write all the arguments
to call the Insert (which is generate by DataSet design and saved my
half the time). Loop is not really too bad compare to all the columns.

I DID find a solution for MY problem. Simply create another access
database and prelink all the tables. Then I can just do "select
into .."

Thanks

Johhn