Question Converting an Access 2010 database to Excel

[molsen]

Hi All

I have a very simple database (just three tables), which I have designed using Access, together with a simple input form with a few buttons and drop downs.

Is it possible to convert the who thing to Excel (in particular, the form), and continue its development using Excel VB? The reason for this is that the firm I am working for only allow Excel/Word for everyday use, and expressly forbids the use of Access, due to a lack of support in IT.

If no, I guess I will have to start from scratch using Excel VB, which seems a little clunky compared to the Access GUI. I am using Office 2010.

Many thanks

 

[CJ_London]

Is it possible to convert the who thing to Excel (in particular, the form)

If it is simple enough then I don't see why not. You main issue will be maintain data integrity which Excel is not very good at.

You can simply copy the tables across to excel, with regards the form you will need to start again

 

[CJ_London]

Just out of curiosity - your IT depart don't support Access, do they actually support Excel in terms of modifying forms etc?

 

[Galaxiom]

It is possible to fly under the IT radar and still take advantage of the power of the database engine and relational data. The main obstacle to avoid is the IT installation block that prevents Access being installed.

So use Access files for the data but Excel for the front end. I have only done this with reporting tools but I have no doubt it is possible to write a full front end in Excel using user forms.

It would be a lot more complex to implement but Excel has a number of tools for interacting with databases. Queries can be run and recordsets returned using VBA. Some similar ActiveX controls are available as in Access.

 

[molsen]

Just out of curiosity - your IT depart don't support Access, do they actually support Excel in terms of modifying forms etc?

Thanks to you both. I thought that was the case.

I am betting they don't support VBE, but are scared of people writing bespoke Access systems which they get reliant upon. When the individual who designed it leaves, then modification or enhancing an undocumented system becomes impossible without a complete rewrite. What makes it worse is that no one has had the Access element of office installed (apart from me, for sorting large amounts of data).

Excel is ok, but having to automate pivots when I want to write a query and then present it in a nice format is a real pain! One to Many's are not one to manys in Excel, and leave lots of redundant data, where one single record may have many different related transactions. After using Access for so long I was taking it for granted!

In Access I just created a sub form to enter many transactions against a single transaction reference, in excel the user will have to create the same reference again and again, and repeat the same data. If anyone knows a way around this (when entering data in a form), I would be all ears!

Many thanks

Mark

 

[CJ_London]

If anyone knows a way around this (when entering data in a form), I would be all ears!

if you have Excel 2013 this may be of use

https://support.office.com/en-us/ar...add-a629-8a3848820be3?ui=en-US&rs=en-US&ad=US

or look at powerpivot

https://support.office.com/en-gb/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

https://algconsultings.wordpress.co...excel-2010-creating-relationships-among-data/

 

[Solo712]

In Access I just created a sub form to enter many transactions against a single transaction reference, in excel the user will have to create the same reference again and again, and repeat the same data. If anyone knows a way around this (when entering data in a form), I would be all ears!

Many thanks

Mark

Hi Mark,
you can do many things with Excel to simulate one-to-many relationships. Basically, you can simulate just about any relationship with Excel, though it may become cumbersome beyond a certain size of project. I am attaching a small General Ledger application for independent consutants in Canada, as an example. You can take it apart and study how to create diverse transactions and reports and automate them refering to the same Chart of Accounts. The app I am sending is a bit dated; I use a newer version now but unfortunately the documentation hasn't kept up. But, if you are keen, I am sure you will get the idea.

Best,
Jiri