Access to access, is query the best option?

[BLIMA]

All,

What's the best way to bring data from one access file (user-oriented) to another one (main database center)? I tried using Append Query, but it adds all rows and not only the ones that are new. Moreover, it seems to be a manual process rather than an automated one.

Thanks!

 

[Micron]

Can you create a composite index in the target table, or would altering table design not be possible? If it was known ahead of time that these appends were going to be done on a main db, the tables involved should have had a composite index at the beginning. You'd have to decide how many fields would make that index unique and it's likely that there is a limit.
With a composite index you can run the append query and duplicates on the index will be rejected. However, you'd either manually dismiss the warning and run the query, or this warning about duplicates can be suppressed in code.

 

[BLIMA]

Hello Micron,

I haven't developed the workflow yet. The objective is to have (1) access file for each user and (1) access file for the main DB, so each user can fill-out a simple form in the Access file and these line-items will be automatically updated in a table in the main DB.

Ultimately, the goal is to have dozens of linked access files populating the main access DB file.

 

[Micron]

The only scenario I can see that this might be the way to go is if the users are working out in the field and need to bring data back to the office, and that would only be if other tech (e.g. Citrix) cannot be used to provide remote access to the db. If you're all on the same network in some office, it is unequivocally the wrong approach.

Sticking with the original post, I mentioned you could create a unique index on the target table to prevent duplicate records. I just recalled another approach, which is to create an unmatched query to show which records exist in the feed table that don't exist in the target table. You then use that query as the set of records to append to the target table. Again, if you are all on the same wired (not wifi) network forget about a separate db file for each user. You'd take the standard approach of one back end db (be) containing only tables and a separate fe (front end) file containing everything else per user.

 

[BLIMA]

Makes sense what you're saying, but wouldn't it be a problem to have 10-20 people using the main db at the same time? Moreover, it also exponentially increases the changes for someone to mess with something up. With that in mind, the idea was to give users a standard access file with a simple form they'll fill-out and that would populate the main database without actually giving them access to it, so they'd be able to add worked hours and overall inputs associated to project statuses. Am I approaching it the wrong way or overthinking everything?

 

[jackd]

Suggest you tell us about the evolving workflow in plain, simple English. Will users be on individual PCs/laptops with their own database to capture some data (records/revisions) that will eventually be used as transactions to your MAIN database? Orr will users be on some network with individual databases?

Am I approaching it the wrong way or overthinking everything? Not necessarily, but you and readers should clearly understand the requirement.

 

[BLIMA]

Jack,

Both. We want users to be able to input data from wherever they are, using whatever device they have on their hands as long as they can log-in & access it. From there, each users will be able to add daily worked hours. All inputs will be stored in the main DB file, which will then compile everything into one table and process everything in multiple instances. That's why I can't have everything in one access-file. Makes sense?

 

[Micron]

wouldn't it be a problem to have 10-20 people using the main db at the same time?

Not sure what you mean by that. It is common practice to have one back end db with only tables and each user has their own front end. Research 'ms access split db'. If you think I meant multiple users using one db, that's not the same thing and isn't what I suggested. If you want to have multiple complete copies and mesh their data into a master file, you will have a dicey situation that will take some planning and design execution to pull off and do so reliably. What do you do if 2 users edit the same record and come back to the office (not clear if this is what you're saying is going on here)? Who's record will over-write, the earliest? The last one in? The later one because it is thought to be the most up to date?

Access used to have a feature called Replication for this but it is no longer supported because of advances in connection tech. Remote users should be connecting to one be file via one of the available technologies (and that is not One Drive and such) or don't bother - IMHO.

 

[jackd]

?? Will your user -each with their own FE - have access to your network housing the BE database??
If yes, then each with their own copy of the same front end could login to connect to the BE.
If no, then each user may have a complete accde with logic to record transactions. When they get back to a connection to the BE, they could process their transactions (new/edited) data against the BE. In this case you should have unique identifiers for each transaction and company and user of each standalone and some common processing logic to identify who, what, when, where made changes/insertions etc.

 

[BLIMA]

All, thanks for helping with this. Please see below and let me know what you think.

1. UsersAll will not have access to the main DB (FE / BA).
2. All they can do is add/delete/modify worked hours in a form I created in a different access.
3. All inputs are linked to the main DB using external linked access files.
4. From there, I developed a query that can compile all those inputs - not an issue.
5. This allows me to track all worked hours in all projects and even break it down using different parameters - again, not an issue.

The problem is:

1. I don't know how to add a complete line item to a table on access, so UserManagers can input complete line items into the DB.
2. The reason why it's not working is because when I create a column on a query, I want it to swap all external references and, if not null, add that new row.
3. I tested using the built-in function with this function: IIf(IsNull([User]![DateUserInput]),"",[User]![DateUserInput]), but instead of returning only (2) rows that had been added to one of the external references, it returned 72, which doesn't make sense.
4. I've also tested Date: IIf([User]![DateUserInput] Is Not Null,[User]![DateUserInput],""), but it returned the same 72 rows.
5. My idea was to, if it had worked, use the same command line for all users, because what I want, in essence, is to have "N" columns (date/project#/description/etc...) and (1) lookup table looking at all external references and compiling data in that query.

It'd be similar to using =lookup on excel.