Edit Get External Data Queries

[Neil]

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

 

[Tod]

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

 

[Dick Kusleika]

Neil

You also need to edit the Connection string. Look here

http://www.dicks-clicks.com/excel/ExternalData5.htm#ChangeSQL

 

[Jan Karel Pieterse]

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