• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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

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
AMixMaster
Asked:
AMixMaster
1 Solution
 
Nick67Commented:
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
 
aikimarkCommented:
use the Application.FileDialog() object.  The results of the user's selection will facilitate what actions your application takes -- which database is opened.
0
 
Simon BallCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
AMixMasterAuthor Commented:
Still puzzling over this one :)
in the meantime, I rename the attached files with the same name and excel refreshes the data ok.
0
 
TracyVBA DeveloperCommented:
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
 
AMixMasterAuthor Commented:
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
 
Nick67Commented:
<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
 
AMixMasterAuthor Commented:
Nick67
thanks, i will put up an example within 24 or so
AJP
0
 
AMixMasterAuthor Commented:
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
 
AMixMasterAuthor Commented:
LinkData folder resides in C:\
0
 
Nick67Commented:
Give this a spin and see how it works
DevLinkData.xlsm
0
 
AMixMasterAuthor Commented:
The code stops at :
ActiveWorkbook.PivotCaches(1).Connection = ConString
0
 
Nick67Commented:
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
 
AMixMasterAuthor Commented:
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
 
AMixMasterAuthor Commented:
Run=time error '1004':
Application-defined or object-defined error
0
 
Nick67Commented:
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
 
Nick67Commented:
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
 
AMixMasterAuthor Commented:
We are where I expected to be at this point.  
The next step will no doubt raise another question.  
Cheers
AJP
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now