Running Make-Table Query in Accdb Through Excel

[VBAuser]

Hi Everyone,

I'm new to VBA and haven't been able to find a complete answer for this online. I have a linked table in excel that pulls from a table in an Access database. I would like to fully automate my workbook by also making the macro that refreshes the table and updates my charts, update the original table in Access that I am linking my excel to. To update the table in Access I need to run a make-table query. Any tips would be appreciated.

Thanks in advance.

 

[PCRIDE]

how are you updating the excel file? Is this a pivot table that has the data connection to the access db?

How does the Access DB get updated? manually or does it pull from SQL or other data source?

 

[VBAuser]

The excel file has a data table that is connected to a table in an access database. The accdb table is created with a make table query that i have to run everyday to bring in new data. The make table query queries through an ODBC database connection.

I don't think there is a way to have my excel file pull data from a regular select query in access so i turned the select query into a make table that creates a table.

 

[PCRIDE]

Well if its actual data table and linked, then you should be able to refresh it and it would pull in from access.

Then create matrix that calculates your data and make graphs. This would be no different than having a pivot table connected to your access db and pressing refresh.

right?

 

[jkpieterse]

So why do you have a table in Excel, link it in Access and then have Excel pull the data from Access? Why not use the data in the Excel file directly?

 

[VBAuser]

Well if its actual data table and linked, then you should be able to refresh it and it would pull in from access.

Then create matrix that calculates your data and make graphs. This would be no different than having a pivot table connected to your access db and pressing refresh.

right?

But the table itself needs to be updated, and I would like to update the accdb table through the a macro in my excel spreadsheet. The table is updated by running a make-table query.

 

[VBAuser]

So why do you have a table in Excel, link it in Access and then have Excel pull the data from Access? Why not use the data in the Excel file directly?

Because the accdb pulls data from multiple, pre-existing tables and queries- it is not worth replicating all of the sql in vba.

 

[PCRIDE]

I do this all the time and here is how I use access and excel.

Excel has a limitation in a pivot table of around 250,000 rows with at least 40 columns. (10 million cells)

Thats not exact but I have experimented with all sorts of data tables and excel begins to lock up and choke at that point.

What I do to workaround this is upload all the data into an Access table because access can handel it no issues. Then you can make all your fancy queries etc.. and make other dynamic tables. Store this as table in access.

Then get a new excel file, create a data connection, point to the Access DB table, pull all your data into a pivot table.

If you need more formulas in Excel that you didn't do or know how in access, create more formulas in excel to create fields if needed. This is what I call a data matrix

Then build all your formulas to populate tables, graphs and charts, I call this a Graphing matrix.

Then as long as Access is updated (even could query a SQL server) all you need to do is hit refresh in Excel and everything updates, assuming that its built correctly.


Sounds like your first importing the excel sheet into Access, combining other access data sources into a main query, then connecting excel to that new query and you need macros to update it all. Correct?

Where do you get the source data if you have to update it all the time, its seems that it needs to be pulling from somewhere.