Edit Get External Data Queries
Application:Excel 2002
Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated |
Edit Get External Data Queries
MSQuery is only good for 'set it and forget it' solutions.
It's very good at doing the same thing every time. It's lousy if you ever have to change anything. Instead you might look into ADO or DAO for connecting to the datasource and retrieving data. I use ADO and only have to change my code if anything changes. Here's a sample: 'Set a reference to Microsoft ActiveX Data Objects 2.x Dim cn as New ADODB.Connection Dim rs as New ADODB.Recordset 'This is the connection string to your Access database cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\DatabasePath\DatabaseName.mdb;" 'Selects all from my Access query called ReportQuery. 'You can also put the entire sql string here. sql = "SELECT * FROM ReportQuery" 'Creates a Recordset using the connection and the query rs.Open sql, cn 'Pastes the results to A2 of the ActiveSheet ActiveSheet.Range("A2").CopyFromRecordset rs This is just an example. You can read more about using ADO by going to this link: http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnmdac/html/datechartoverview.asp tod -----Original Message----- Application:Excel 2002 Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated . |
Edit Get External Data Queries
Neil
You also need to edit the Connection string. Look here http://www.dicks-clicks.com/excel/Ex....htm#ChangeSQL -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Neil" wrote in message ... Application:Excel 2002 Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated |
Edit Get External Data Queries
Hi Neil,
Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. You could download and test Dick Kusleika and my Query manager: www.jkp-ads.com/QueryManager.zip Regards, Jan Karel Pieterse Excel MVP JKP Application Development Services www.jkp-ads.com |
All times are GMT +1. The time now is 04:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com