Using an Access query to pull data from excel

[Mr930]

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

 

[soccerkingpilot]

Fred,
Access has a link tables feature...that may be what you're looking for.

-Austin

 

[Mr930]

I tried that but I need a way to create primary keys.

 

[xenou]

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]

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