Solved

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

Posted on 2011-09-11
19
295 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

8 Experts available now in Live!

Get 1:1 Help Now