MSQUERY ODBC and Excel..

[redsand23]

Can you push data from Excel that is returned from a database via MSquery back to the database.

Here is what I am trying to accomplish.
Excel 2000 - I have created a query that links directly to an external ODBC database and its' data is populated into Excel. I need to be able to modify the data in excel and push the data back to the database.

I can go directly into the query and go into records/allow editing and it works. I just do not know how to push the data back to the database when it is modified in Excel.

Thanks!!!

 

[SkipVought]

hi,

What is your query (sql) and what fields do you want to update based on what criteria?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[redsand23]

My query is wide open, I grab all records in the table and report them in excel. Once the data is in Excel, I want to be able to modify values in excel and have it automatically update the database. I know the next question is why not use access and use datasheet form. The reason is that the user wants to be able to use the excel functions to analyze the data before changing it. For example they want to be able to select some cells in a column and show what it add ups to. I hope this helps.

 

[SkipVought]

You did not answer my questions. Please post your code and explain what fields you want to update and with what criteria!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[redsand23]

I need the ability to update all fields in the table. I created the query from excel, using the data-get external data-new database query. I then told the query to export to excel. Is it possible once the data gets into excel, to modify the value of the fields then push it back to the database to update?
Thanks for being patient. Obviously new to this!!

 

[pbrodsky]

Two possibilities.

1. save the modified Excel file and (in Access) import the modified data back into the table

2. if you want to control everything from Excel, write ADO or DAO code in Excel VBA to load the data back into the Access table.

Neither is very difficult. For VBA, look at the various examples in the VBA help file