Using an Access query to pull data from excel

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
Not sure if this is an Access or Excel question, but is it possible to create a query in Access to pull data from an Excel spreadsheet? On the menus I see that I can get external data but that appears separate from the query stuff. I want to end up with a query that has primary keys set that I can access from another program that can hit a table or query. I want to use a query so that the connection to Excel will be live as needed.

thanks
Fred Emmerich
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
As far as I know you cannot set primary keys on data stored in Excel -- Excel is not a database program and although it can store data in tables it cannot create unique indexes on the data. If that's a requirement I think the data needs to be stored in an Access table, or you have to create some means of ensuring there is a unique ID yourself.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
It is possible to use Excel as a front end for Access but the best option is to let Access create the primary key values for you.
Something to consider when you have a linked workbook; both Excel and Access lock their data sources. If you open Access first the workbook will be unusable, and vice versa.

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top