Basic Access functionality question

[MSant14]

I do not currently know anything about Access, but before I invest the time in learning, I have a simple question about its functionality. Can you have 2 copies of the same database in different locations and have them linked and update as changes are made in one of them? If so, can one of the databases have extra fields in it, or do they have to be exactly the same?

I'm trying to create a tool at work and it can be done in excel, but I would prefer to use access in this case because I want to build off an access tool we currently have. I want to create a duplicate version of the current database and add a few extra fields in the second copy while still having both of them linked. Not sure if this is possible and I can't seem to find an answer on google. I figured someone with experience can answer this pretty quickly. Thanks

[June7]

A multi-user database should be split. Backend on central server has data. Frontend is user interface linking to backend. Each workstation runs a copy of frontend. Frontend manages what fields users view.

But yes, you can build another db with its own tables that link to your existing db.

[CJ_London]

To give you a better understanding of what Access is. Access provides a front end development and runtime environment to work in Windows - i.e. create windows style forms/reports/routines etc that enable users to interact with a database. A database is a collection of tables with defined fields, constraints, indexes and relationships and is commonly referred to as the backend. It works in a similar way to a website. Simplistically, the runtime environment is the equivalent of your browser, the forms/reports etc equivalent to web pages and the backend database is what your forms or a web page call on to provide and manipulate data.

Access can interact with many databases and included for free is the ACE database (same technology that manages Windows) which many refer to as being Access - it isn't. It can also interact with SQL Server, Oracle, MySQL and many others plus other files such as .xlsx, .doc, .csv etc. In that respect it is a powerful tool. As a tool you develop forms etc which work in the windows environment.

ACE is a file based RDBMS (relational database management system) in the same way that a .xlsx is file based. Whereas SQL Server, Oracle are server based.

The normal setup for access is to have the database (backend) located on a LAN server that all users can see, whilst the front end (forms/reports/etc) is located on each users machine.

So, to answer your questions

Can you have 2 copies of the same database in different locations

by database we mean the backend (not access). If you are referring to the ACE database, then they are a file so yes, they can be stored in different locations. But although they may have the same structure, over time they will contain different data as users enter/modify data at the different locations

and have them linked and update as changes are made in one of them?

its possible but can be complex and providing you have some means of seeing both at the same time. Otherwise you need to use some other method (such as email files from one db, for the other to import). Usual basis is to share the database on the LAN

can one of the databases have extra fields in it

yes, providing you don't want to update those extra fields to the other database. If sharing, you can code so users who don't need them, can't see them.

However the crux of your thread

I would prefer to use access in this case because I want to build off an access tool we currently have

it is quite a common requirement to use access to provide additional functionality on top of an existing system. You would need to clarify exactly what you mean, but I suspect all you need is to link to the relevant tables in the access tool and provide additional table(s) for the additional fields. Main thing is your new application does not modify data in the existing tool without proper consultation and agreement with the tool developers.