Editing Records using microsoft query

[Dave H]

I am querying an access table from excel using microsoft query. I have selected the option under records allow editing. While in microsoft query I can edit the source data but when I return the query to excel I lose that functionality. Is it possible to run a query on an outside source return the data to an excel spreadsheet then still have the editing capabilities within the excel spreadsheet?

 

[Ziggy1]

have you tried on the "Data" menu > "Edit Query" or refresh? In MS Query you can create a Parameter (query) pop up by enclosing a question with square brackets (same as is done in Access).

You can save your MS query (.dqy) and then run it any time, either by double clicking on it or rightclick and edit with MS query.

The problem is that you are not saving your query, when following the steps to create it (with wizard) you will have that choice near the end before you return it to Excel (Or edit with MS query)

 

[Dave H]

Actually I am not trying to edit the query but the source data table. While I am in MS Query under records I can select allow editing which then enables me to edit the source data. However once I return this query to excel I lose this capability. I would like to know if there is a way similar to access to actual endit the source table from the query in excel. Thanks for your response.

 

[Ziggy1]

So you want to query Access, bring it to Excel. Edit records and then want those edits reflected back to Access?

I am just trying to visualize what you are trying to do, if you explain in more detail I can direct you to another solution perhaps.

I use MS Query all the time, but I save MyQueries and rarely Edti them because it is more for Reading data rather than editing. I am assuming you do not have access to the Database, because if you did you could link to it through another database or Excel.

explain how much authority you have to the database, is it supported by your IT department and/OR lockedup so you can't do much to it?

 

[Dave H]

I have full access to the access database. I have a complex excel spreadsheet that queries data from a sequel server. I would like to capture text info to supplement the sequel data. I would like to store this text data in access. Lets say you had a table with approximately 500 columns and at least 3000 rows. I know how to query the table from excel. Then how do I record responses to that table from the spreadsheet? I would have approximately 2 dozen users.