Making Access continuous form behaving like Excel

Hi Access is far more structured than Excel, and so may of the ways in which Excel data can be manipulated (and destroyed), cannot be easily replicated in Access.

Yes, you can add sort columns and write code so that they can display records in whatever order they want. Columns can be dragged and dropped to change their order on forms. Beyond that, you're going to spend a lot of effort for not much gain.


Kelvin

+1 on what kelvin said.  

You can do clickable headers for sorting, bump up/down for moving rows, but it will never be as flexible.  It just doesn't work that way.

Jim

@Kelvin, Jim,

I thought of something, perhaps you can help me materialize it..

When user wants to do those major modifications, a button should perform export data to excel, and a popup screen will show the excel file, and once they close that, there should be a some code that updates the sort column in access according to what was done in Excel.

Thanks,
Ben

Yes, but a lot of work.

The export is easy, but ready that data back in and interpreting it is a lot harder than it seems. As Excel is a "free text tool", you have no control of what they do and where, and so the data may not be what you think it is.

Importing data is only as reliable as the person who played with the data.

Kelvin

Ben,

<<When user wants to do those major modifications, a button should perform export data to excel, and a popup screen will show the excel file, >>

There's no need to even Export; you can embed excel right in an Access form.   But again as Kelvin pointed out, there are too many things that can be done with the data and with no checking in place, it may be impossible to sort out after the fact.

Really what you need to understand is why the users are asking for the functionality they are.  For example, maybe they cut and paste rows because it's the only way for them to re-order the data.  So adding a "Display Order" column to a display might replace that.

Maybe they need to copy and paste data across a row as a simple means of copying their previous entries. Well you certainly can do that without Excel.

Explore the reasons behind the need to do what their asking for and you will probably find that you don't need Excel.

You will also probably find that you can simplify the task/process there are performing and come out with a win-win.

Jim.

Hi Experts,

@Kelvin,

As Excel is a "free text tool", you have no control of what they do and where

So far I am only exposing this for sorting purposes, although if that turns out to work well they may request additional functionality, will worry then..

@Jim,

Really what you need to understand is why the users are asking for the functionality they are

So far the only request was to be able to sort them the easiest way possible, like if there are 20 records and they want move the last 5 somewhere in middle, they should not have to edit each individual record, instead just selecting and dragging them to the desired place.

@Pat, Nick,

Will have to study your suggestions, perhaps if using Excel is not an option, will consider one of those.

@All,
Taking this in consideration that Excel will be used only for sorting, I still not sure why cant something like I proposed work.

Meaning the exported Excel file will contain all the fields including the ID field (auto Num), and then when they're done with alterations, the program will read the Excel in order they saved and update the sort column in access accordingly?

My main question here is, how does Excel exposes the sorting order property to other app?

Thanks,
Ben

@Kelvin,

Unless you specify something else, that is the default sort key in Access. A sort column will assist there

You are then dependant on the users updating the sort column

Actually this brings me to think of a different approach, the same as you're referring to, just instead of having the users doing it, perhaps its possible to have code to open that excel file and run a macro that updates the excel's sort column in order they are saved, then run an update query in access joining both ID fields..

@Jim,

Using the  top, height, and width properties, you can let them select a block of records.  

well, this sounds better then previous suggestions, as users can move records without editing them, however would still wait if someone can suggest something with Excel as that would definitely satisfy users requirement (for the moment..).

Thanks,
Ben

Actually we have the following code that reads an Excel file row by row

        conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Application;"
        rs.Open "SELECT * FROM [Applications.csv]", conn, adOpenStatic, adLockReadOnly, adCmdText
rs.MoveFirst...

Select allOpen in new window

I tested now with two records and was reading in saved order, then I manually changed order and saved, and the code was reading in the modified order, wondering if we can rely on that?

Thanks,
Ben

I'll bow out at this point with a warning:
You are opening yourself up to a huge, perhaps insurmountable future avalanche of requests.
If you try to give users Excel as a user interface, instead of an Access datasheet or continuous form, they WILL want you to iterate upon it, much to your detriment.

Imagine if they want to EDIT the data in Excel, and you will be forced to try to validate every cell in an Excel spreadsheet, and run update SQL if it has changed, with no data validation or any of the other tools Access provides.

You are embarked upon a task that I would decline with the only excuse I generally give: 'it's not impossible, but the amount of developer time and data corruption risk make it something not worth doing.'

Exporting throwaway data to Excel for people to play with is one thing.  Excel as UI is something all together different.  And you STILL can't edit Excel data in Access because of a patent lawsuit MS lost.

Just don't go there.

To Nick's point, I'd once again reiterate that the best way to move forward is to find out why they need the Excel features, then give them a better way to do that with the UI and/or code and do it the "Access" way.

 Lot less hassle.  Using Excel for anything more than output is going to be fraught with problems.

Jim.

OK my experts, Although I feel that Excel solution may have worked, but without your backing & support I'll bow out from this as well..

Thanks,
Ben