Excel Linking to Access

[Guest]

First, let me say - I'm new to Excel / VBA programming. I have developed
VB.net apps, however the environment in Excel is a little different. OK now
I don't feel so dumb. I am in the process of converting a MS Access App to a
SQL Server backend. There is an Excel spreadsheet that I KNOW accesses MS
Access. There is a macro that does the following -
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

How does Excel know what the data source is - it does b/c it says not found
so it must be in some nook somewhere. Can someone give me a little
assistance here?? I just want to know what query is being called.

Also, Part II - this spreadsheet has about 12 worksheets with many columns
that 'seem to be' mapped to columns from the data source - but I can't see
where the mapping is taking place. HELP!!

 

[Guest]

Not too sure what all you are looking for but here is a start.

Data -> Get external Data -> Edit Query

HTH

 

[Guest]

Didn't help - it give me the same message as when I do a 'refresh data' -
'blah\blah\blah.mde' is not a valid path. I guess for starters, how does it
know where to look - ie; 'blah\blah\blah.mde' ??

 

[Guest]

The person who originally set up the xl wbk has evidently set up a query to
get data from an Access file.

This is done using Data>Get External Data>New
and specifying MS Access as the data source. xl stores this information and
attaches it in the background and is associated with the xl range the
originator chose as the query table (destination). Usually, you can go to
Data>Get External Data > Edit Query and get access to the parameters of the
query. You can also get to MS Query thru this method and Select the SQL
button to see and edit the SQL code.

Ah, it just hit me -- you need to place your cursor INSIDE of the xl
destination range prior to do Data>Get....>Edit. Try this and see if you
find what you need. Then post back.

 

[Guest]

If you still can't locate the source file, it may be because it has been moved.