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?

 

[xenou]

It's a fairly straightforward exercise to split a database and is quite common practice for databases large and small. Worth a try to see if it helps.

What kind of updates are occurring in this database? What is the nature of the data storage in Excel and how is it being used to feed other processes? Maybe there's a better way to integrate these processes more harmoniously.

 

[HiTechCoach]

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)

I use find and replace in a form all the time.

Even better is to make teh form smart enoufh to accept the parameters and run an update query.

Form provide a smarter and more user freindly interface. I never let users work directly ion a table. Even I never open tables. I always use a query at the very least.

FWIW:
To prove to myself that you don't need to every open a table except for design change I tried only using queries. I found out that this actually made things a lot easier and more powerful. This help me to get really good with SQL I also could do lots of things that are not possible when you open a table directly.

 

[adam087]

Perhaps consider the formulae doing the Lookup in Excel as well. They might be better adapted so that the column order becomes irrelevant to them?

Adam

 

[oliviar]

Solution - why columns are moving around

Hi Guys,
I have discovered why the columns were moving around. The users were hiding and unhiding columns, then saving the layout when exiting.
For some reason, this causes the columns to be returned in a funny order.

I have now stopped users from being able to save the table layout, and all is good.