Solved: Appending data to my tables from an external access DB

[f9073341]

Hi guys,

I currently have a central database which I will be sending extracts of to individuals in different offices on separate networks. I need them to use the database and send their version of the database back to me. I then need to append their data to my central database.

What would be the best way of doing this?

Many thanks for your time. :thumb

[OBP]

That is frought with the possibility for problems, first of all you will need to Import the tables, then perform an Append Query to Append new data and then perform an Update Query to update those records that have been changed.
You have to be especially careful to provide a very good means of preventing Duplication, simple Autonumbers will not work, as each person will generate the same numbers.

[f9073341]

OK, thanks for that. Do you have a better solution?

[CreganTur]

OK, thanks for that. Do you have a better solution?

If it is impossible to have all of your users connected to the same shared drive or network, so that they are all interacting with the same backend database, then my next suggestion would be web development.

I've never worked with Access pages before, but they are an option. Also, you could develop with ASP classic- it is very easy to integrate it with an Access database.

An excellent book on the subject is Access 2003 Programming by Example with VBA, XML, and ASP (http://www.amazon.com/Access-2003-Programming-Example-VBA/dp/1556222238/ref=sr_1_1?ie=UTF8&s=books&qid=1240230876&sr=8-1).

[f9073341]

Unfortunately, that won't be an option for me as my resources are really limited at work. I should only have a maximum of 10 users accessing the database at any one time in the same office - could this cause a problem?

However, I have another group of people on a different network - which is where this problem has come from.

[CreganTur]

I should only have a maximum of 10 users accessing the database at any one time in the same office - could this cause a problem?

Access can handle 255 concurrent users, so you'd be fine.


However, I have another group of people on a different network - which is where this problem has come from.
Is there anyway to setup a shared drive that everyone can connect to?

[stanl]

Why not Disconnected Recordsets? .02 Stan

[hansup]

OK, thanks for that. Do you have a better solution?There is no simple/easy solution for sharing the same Access database between users on different networks.

Consider another method to store your data which does support reliable sharing between users on different networks.

For instance, you could store your data in SharePoint "lists". Then your Access database could serve as a front-end to the SharePoint data. You would have to replace your native Access tables with links to SharePoint lists.

Another option would be to "upsize" your data tables to SQL Server. Then, as with the SharePoint approach, your existing database would become a front-end to data in SQL Server.

It's also possible to use databases other than SQL Server on the back-end, but SQL Server is easier because you can take advantage of Access' built-in SQL Server integration capabilities.

Good luck,
Hans

[CreganTur]

Why not Disconnected Recordsets? .02 Stan

I forgot all about those- you can do a batch transaction updates with them, correct?

I've always wanted to build an app that uses Disconnected Recordsets, but I haven't had the opportunity yet.

[stanl]

I forgot all about those- you can do a batch transaction updates with them, correct?


Yes, if Recordsets are opened as Keyset, BatchOptimistic - they work great for updating as well as keeping a log of changes at the remote site. Of course, you would want to minimize or avoid 'deadly embraces', viz. 2 persons updating the same row at the same time, but if you are not a complete moron you can disperse records to avoid that. For example, assume you had 100 installers or salespersons each of whom wanted to update their customer account data whenever. You would design the db tables in such a way that they could only select records to add/edit/delete based on their unique installer/sales ID. .02++ Stan