Solved

How to build a dialogue box that chooses a different Access data source for Excel.

Posted on 2011-09-11
19
275 Views
Last Modified: 2012-05-12
Experts,

I have several Access data sources with different data.
The names of the Access databases are different
The table structure is identical in all cases.  
The data is used in a simple Excel Pivot Table.  
When I want to pull different data into the Pivot Table I have to open the data connection and modify the property settings.  
This requires confirming all of the inputs.

I want to build a dialogue box that changes the Source database and leaves all of the other properties the same.  

Can the connect string be inserted into a module that contains a break to insert the database name by means of a "file open" type dialogue?

Thanks People
AJP
0
Comment
Question by:AMixMaster
19 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36524208
I am sure a UserForm with a combo box could be created.
On another sheet you could store MRU's for the db's you opened lately
And probably you could use a FileDialog for any new one.

But, if you are doing this in Excel, I think you are in the wrong primary Zone, as the only really 'Access' part of this is the connection string--which you already have worked out.

If you post an Excel file and the connection strings I'll see what I can do--but you may want to 'request attention' and change the primary Zone to Excel, to get an Excel Expert to give you a hand.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36524500
use the Application.FileDialog() object.  The results of the user's selection will facilitate what actions your application takes -- which database is opened.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36534497
You could go at this the opposite way, have one access database with linked tables from all the other mdb's, and use some

vba object to open excel ad run the pivot table from a form in access, with a table selected.

e.g. access mdb with all linked tables
form in mdb with drop down of table names..

button to export the table contents to excel, open excel object, vba automating add new sheet with pivot table...
0
 

Author Comment

by:AMixMaster
ID: 36571825
Still puzzling over this one :)
in the meantime, I rename the attached files with the same name and excel refreshes the data ok.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 37148607
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:AMixMaster
ID: 37148608
perhaps we could rephrase the initial question.  
How do we build a dialoque box that allows you to choose a different database for the attached tables?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37149388
<I am sure a UserForm with a combo box could be created.>

You could post a sample Excel and Access file for us to work with
0
 

Author Comment

by:AMixMaster
ID: 37149573
Nick67
thanks, i will put up an example within 24 or so
AJP
0
 

Author Comment

by:AMixMaster
ID: 37151156
Nick67 et al,

All databases are from a previous version of Access and are identical in structure but have different names.  

Currently, in order to change the underlying data, I make a copy of "LinkData2" and change the name to "LinkData".  
As far as Excel is concerned, the data source for the excel pivot table remains the same and displays the result from the renamed database.  

I want to leave the name of the source databases as they are and change the Data Connection Properties by means of a "File Open" type dialoque.  

AJP LinkData.zip
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:AMixMaster
ID: 37151167
LinkData folder resides in C:\
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37157118
Give this a spin and see how it works
DevLinkData.xlsm
0
 

Author Comment

by:AMixMaster
ID: 37160892
The code stops at :
ActiveWorkbook.PivotCaches(1).Connection = ConString
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37161101
It stops for me there too, because I run Office 2003, and not 2007+
Try this one and report there error number and text you may get
DevLinkData.xlsm
0
 

Author Comment

by:AMixMaster
ID: 37161629
same result...
I am using Office Pro 10
possibly related to:
Provider=Microsoft.ACE.OLEDB.12.0
Also, ConString has different syntax, for Excel it is ConString, others use ConnString
0
 

Author Comment

by:AMixMaster
ID: 37162792
Run=time error '1004':
Application-defined or object-defined error
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37162800
Constring is a variable I declared earlier in the procedure, not an instrinsic VBA property.  I'll try it in 2010 later this evening.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 37163266
Ok, it barfs in 2010 too.
I tried to use VBA to overcopy linkdata.mdb, but since it is in use, that barfs too
Look here
http://www.mrexcel.com/forum/showthread.php?t=341278

I think the code can be adapted to do what you need.
Basically the PivotTable and PivotChache need to wiped out and rebuilt on the fly.
That means knowing a little bit more about the data than I do.

The present file gives you the user form, a way to select a new file, and a way to build up the new connection string.
From the noted site do you think you can get the manipulation of the Pivot objects to work?
0
 

Author Closing Comment

by:AMixMaster
ID: 37164662
We are where I expected to be at this point.  
The next step will no doubt raise another question.  
Cheers
AJP
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now