Columns move around - multiuser database

[oliviar]

Hi guys,
I have two tables that are used by 12 people at a time.
they aren't complex tables, but they have 50 columns in them.

What is important to us is that the column order remains the same - because they are embedded in excel files via a connection link. When the order of the columns changes, our excel macros go to the wrong spots, and people get generally annoyed.

So I ask:
why is my column order randomly moving around (I have checked the table design, they are definitely in the right order in there)?
and
how can I fix it so it stops happening?

 

[HiTechCoach]

Since this is a multi user database is it properly split?

Is the front end shared?

 

[oliviar]

Sorry I don't understand what you are asking.
The tables are just there, and they are used by multiple people at once.
They all open Access, and they all go into the table, and use it, and edit it.
Everyone has their own section of the table to edit - so there is no risk of anyone editing over someone else's work.

It is a replacement for a shared excel sheet which was very unreliable. Its not supposed to be a massive database.

 

[HiTechCoach]

See:

Splitting your Access database into application and data

 

[oliviar]

There is really no need for such a complex setup for this.

It was an excel sheet, that just kept crashing because shared excel sheets are unreliable. Hence we moved it to Access.

We just need to have multiple people in it at once, and not have the formatting change unexpectedly.

The split database solution is akin to buying a tractor to put in a suburban flower bed. It is a magnificent and flawless solution, but its way overkill here :P

 

[HiTechCoach]

There is really no need for such a complex setup for this.

It was an excel sheet, that just kept crashing because shared excel sheets are unreliable. Hence we moved it to Access.

We just need to have multiple people in it at once, and not have the formatting change unexpectedly.

The split database solution is akin to buying a tractor to put in a suburban flower bed. It is a magnificent and flawless solution, but its way overkill here :P

Respectfully, I totally disagree.

Allowing multi users in an application adds a huge degree of complexity.

Splitting is the only way have a reliable set up of Access database for multiple users. I know of no way around it. It has bben that was for 15+ years I have been developing multi user applications in Access.

I know you don't want to hear this but I do believe the root cause of your issues are because the database is not split. I have seen it many time.

 

[oliviar]

It is just two tables. With nothing else. Where would I split the database, and how would they input their data?

Forms are not an option because they have to change hundreds of records at a time (using find and replace)

 

[xenou]

I'm not a fan of linking Access to Excel sheets. The data could be in Access pure and simple - that's where I'd go with this if at all possible. It depends on your setup though and what else (might) be going on with that Excel workbook. You say you have 50 users - how many are in it at one time?

Typically you don't give user's access to a table directly. They should be using a form. This might alleviate some of the problems. You can create a form that looks exactly like a table (by using a datasheet view).

Oops - sorry - that's 12 users. Are they all using it at once?

 

[oliviar]

Yes we all use it at the same time.
The excel file is simply used as an output file to run lookups for other excel files. This is why column order is so important. We have macros which rely on certain column numbers in the connection file.

I have created a datasheet in a form, but my users complained that this moved columns around too.

 

[boblarson]

Y\
I have created a datasheet in a form, but my users complained that this moved columns around too.

If you would have this as a split db where each has their own frontend copy then they could potentially move their columns around on the form (or their view of the linked table, for that matter) and it would not affect anyone else, nor of the actual column order. If you use the same database file then you will have the issues you have described. So, if you want to avoid the problems - split the database and give them their own copy of the frontend.