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?

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

ChazP (c.par... at (no spam) btinternet.com) 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, esq... at (no spam) sommarskog.se

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

ChazP (c.par... at (no spam) btinternet.com) 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, esq... at (no spam) sommarskog.se

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