Re: Merging Access to SQL

ChazP (c.parvez@xxxxxxxxxxxxxx) writes:
I have 2 remote users with laptops working in the middle of a field
collecting data and entering it into identical Access databases (data
would be different of course). One database is called user1.mdb the
other user2.mdb

When the users return to the office and connect to the network, would
it be possible to merge the two seperate Access databases into one
MSSQL database?

Yes. You would have to decide table for table how to merge. I guess that
some table are lookup tables, and if one entry appears in both, it should
not be entered twice in the MS SQL Server Database. This can be more
tricky than it sounds. Maybe there is a product category "shrinkwraps"
in both databases, but the id is 8 in one of them and 17 in the other.

For other tables, it may be that you should copy the entire tables, because
the data in those tables are completely disjunct. In this case, you should
probably add a column to add a key to tell where the data comes from.
This is particularly important if you have used the autonumber features.
A tip: you should not use IDENTITY for your SQL Server tables, since you
will import ids from Access.

As for you actually should do the copying, I don't know, since I
never work with Access. But options include using linked servers or
SSIS.

Once merged, would it then be possible to display the data using
ASP.NET on a web page and run reports etc?

Yes. As for how, well, I guess people in and ASP .Net forum can get you
started.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: why>?
    ... On your desktop-- you'll be running SQL Server behind the scenes. ... it isn't risky to allow end users to create databases. ... it is no more risky than giving you the ability to create spreadsheets. ... I'm not saying that Oracle and IBM are going away. ...
    (microsoft.public.excel)
  • Re: Please answer my queries for fresh Installation
    ... Moving SQL Server Databases ... Using WITH MOVE in a Restore to a New Location with Detach/Attach ... Disaster Recovery Articles for SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Please answer my queries for fresh Installation
    ... You can restore MSDB as well as master. ... > SQL Server, It will create Master, MSDB databases. ...
    (microsoft.public.sqlserver.server)
  • Re: Moving SQL Server 2000 from NT4.0 to Windows 2003
    ... Detach your existing user databases, ... user databases (or RESTORE) to the new server. ... > Hey Steve, ... Windows 2003 will not allow SQL Server SP less than 3. ...
    (microsoft.public.sqlserver.server)
  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... MDB is friggin dead.. ... SQL Server has taken over the world. ... than read-only access to central company databases. ... I learned to write queries in Access after an hours' ...
    (microsoft.public.excel)