0

I was asked to create a database and based on the information I was given assumed that it would be used for storing data and would be used by multiple people. Now I'm realizing that's not the case. I'm the only person using the database and I'm using it to import Excel spreadsheets, run queries on the data to modify it or convert it, then export back to Excel. I'm wondering now if I should stop working on this database and switch to something else (e.g. Power Query/Pivot).

Things I'm doing in Access that I don't know if can be done with something else better or easier:

Outer/Inner joins on multiple tables using = and Like operators, update queries, union queries, totals queries

Since I don't need to store data or have a user interface, it seems like I should keep the data in Excel (or use something else) but I don't know if Excel has the functionality that I need. The most records that I process in Access at once is about 20k. Access works very well for what I've created so far and my tasks are mostly automated, but each time I need to create a new query or edit an existing query or write code I start thinking there must be an easier to use tool available.

3
  • Why change if irs working. Only you know what you require and will require. Oct 4, 2023 at 5:05
  • @RohitGupta It's working but incomplete. I have more work to do. And using database software only to modify and merge spreadsheets seems wrong to me. Access is not easy to use. Before I start trying to implement my processes in Excel I thought I'd ask if Excel has the same query tools as Access.
    – tomasm
    Oct 4, 2023 at 17:19
  • There are a couple ways to tie your excel directly to the database via ODBC or attaching to the mdb file directly. This could reduce or eliminate the amount of importing and exporting. Also Excel supports Macros which use the VBA (Visual Basic for Applications) programming language for advance functionality. However, this is also a manual process of writing scripts one line at a time.
    – cybernard
    Oct 9, 2023 at 14:05

2 Answers 2

0

From your description, you should continue with Access for a few reasons:

  1. Unlike Excel, Access has fixed column data types. This will help prevent those typical Excel errors where, for example, a date written as text ends up in a proper date column (hard to calculate text and numbers).
  2. Access allows you to join different (Excel) tables. This is also possible in Excel, but much more work and very fragile, too.
  3. Queries in Access can also be written in SQL - the default query design screen is optional.
  4. Access overlaps in most functionalities with Excel, for example functions, filters, etc. Some things may be in a different place/menu but it's not that hard to find them. The learning curve should not be steep.
0

You may have to download and install accessdatabaseengine.exe or accessdatabaseengine_X64.exe from microsoft to get this to work.

The part of the question I am trying to answer here is automating the work flow with less manual importing/exporting and also using VBA.

This code is oversimplified but should give you an idea of what is possible in excel macros.

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
    
connectionString = "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=d:\access.accdb"
cn.Open connectionString
rs.ActiveConnection = connectionString 
    

cn.Execute "insert into "

This demonstrates the ability to have your data in an access database but update it and query from inside of excel.

1
  • I already have some tables in Access linked to Excel spreadsheets so no need to import everything, and use VBA to export. Because I don't need to store data or have a user interface, I may be able to accomplish what I'm doing in Excel or maybe Power BI. I like how Access organizes everything into objects and I can write queries in SQL, but I will look into Power Query in Excel and Power BI since it would be good for me to learn those anyway.
    – tomasm
    Oct 10, 2023 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.