migrating excel database to access

[ænimoral]

Hi All,

new to the Forum and to Access.
at work we use an excel sheet to keep track of projects. For a variety of reasons (multiuser being the biggest concern) this is ..not ideal.

After some deliberating it has been decided to migrate to Access.

The .xlsx gets it's entries fed from a variety of external sources via scripts that are run according to a schedule. Then multiple Users update additional entries by Hand.

I can think of 2 ways to proceed:

Rewrite all the "feeding" scripts so they populate the new Accdb which Users can then manipulate (in moderation)

or

Leave as it is and update the Accdb from the xlsx.

the latter seems.. untidy



I hope this outlines my scenario. Any suggestions?

[xps35]

Keeping data in Excel and Access is not an good idea. So forget option 2.

Option 1 is the best I think, but isn't as simple as it seems.
You first need to design a proper data structure in Access. Simply translating a sheet into a table will certainly produce a bad database. Next, you need to handle the conversion of the current data in Excel to Access. Finally you rebuild the feeding.
Of course you will also need forms, reports and queries to enter, update and report data.

Bottom line: yes it can be done, but it is a huge job.

[ArviLaanemets]

Or you remain using excel, but redesign a bit.

You need a main Excel workbook, where all tables containing data needed by all users are kept. And every user has his/her own workbook, which contains all data entered/edited by this user. User's workbook reads general data from main workbook (e.g. through ODBC queries). Main workbook reads data from every user's workbook (e.g. to hidden sheets for every user, using ODBC queries there), and then combines read data to overall data tables (e.g. using UNION queries which read data from those hidden sheets).

As result, any user can work with his/her workbook without affecting the use of other workbooks. Refreshing the queries returns latest saved data from any source workbook.

[CarlettoFed]

You should attach the Excel file to see its structure and then show you how to recreate it in Access.

[Micron]

As I read it, Excel isn't viable any longer at least due to file sharing issues because Excel does not do concurrent users very well (if at all). So no Master workbook or anything like that. I would also rule out maintaining the wb and linking to its sheets from Access because of that - unless you can have someone who maintains the wb and you link to the sheets. However, as stated, xl sheets usually make for bad related db tables. This means if you link to the sheets, you will most likely need db tables that are correctly designed and related and you port data from the linked sheets into these tables with mucho append and update queries. Then there is the code issue. Most of the operations that have been coded for Excel will need to be re-written for Access if they are still needed. Those that won't be needed might be replaced by things like queries against Access tables.

IMO the best route to take is to bite the bullet and build a proper db that supports the objectives while still using Exel in the meantime. There may be a bit of an issue wrt a lack of relational db and Access experience. There is a bit of a steep learning curve if one is unfamiliar. Much research should be done to get off on the right foot lest you continually encounter road blocks because of improper design. The #1 thing to learn first is db normalization. If you have vba experience in Excel it may help a bit with common things such as vba functions (e.g. Left, Right, Mid, etc.) but it won't help much with coding for the Access object model. Perhaps review the links in this thread and decide who is a candidate for taking this on or whether or not to contract this out.

Last 2 points I'll make for now regard the potential file size and db sharing. I imagine that you won't come close to the 2GB max for Access given that you are now doing this in Excel but that and the lack of web support for Access may be factors. Note, you must not attempt to share Access via the cloud or over wifi - there are other options if remote access is required.