Solved

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

Posted on 2011-09-11
19
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

724 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