Linking Excel to Access DB (1 Viewer)

K

KC21supra

New member

Local time

Today, 12:38

Joined

Sep 20, 2011

Messages

1

• Sep 20, 2011

• #1

Hello all,

So i was given a report in excel and a copy of a database from a coworker who was recently let go. The excel file is just a couple of sheets that has a pivot table in each of them. However, these pivots are linked to a table in the Access database. When i went in to the report to refresh the pivots, it is trying to connect to my old coworkers database instead of the copy that i own. Does anyone know how to update the excel file so that it takes from my copy of the Access DB and not my coworkers old one?

Thanks in advance,
KC

 

G

GaryPanic

Smoke me a Kipper,Skipper

Local time

Today, 12:38

Joined

Nov 8, 2005

Messages

3,294

• Sep 20, 2011

• #2

you need to do it from the excel end
after that I am lost ....

 

boblarson

Smeghead

Local time

Today, 12:38

Joined

Jan 12, 2001

Messages

32,059

• Sep 20, 2011

• #3

Which version of Excel and Access?

 

boblarson

Smeghead

Local time

Today, 12:38

Joined

Jan 12, 2001

Messages

32,059

• Sep 20, 2011

• #4

Got this from a post on Mr. Excel:
http://www.mrexcel.com/forum/showthread.php?t=293796

First go to the VBA window in Excel and paste this into the Immediate Window:

Code:Copy to clipboard

debug.print activesheet.pivottables(1).pivotcache.connection

Then hit enter and it should return a string. Modify the path to the database in that string and then use this below (replacing "edited string" with the string you edited) and hit enter:

Code:Copy to clipboard

activesheet.pivottables(1).pivotcache.connection="edited string"

 

M

malaydash

Registered User.

Local time

Tomorrow, 01:08

Joined

Jan 18, 2010

Messages

43

• Jan 24, 2012

• #5

boblarson said:

Got this from a post on Mr. Excel:
http://www.mrexcel.com/forum/showthread.php?t=293796

First go to the VBA window in Excel and paste this into the Immediate Window:

Code:Copy to clipboard

debug.print activesheet.pivottables(1).pivotcache.connection

Click to expand...

Where is this Immediate window. I am also facing a similar problem. I opened the VBA window but was not sure where to paste the code. Please help.

 

J

JANR

Registered User.

Local time

Today, 21:38

Joined

Jan 21, 2009

Messages

1,623

• Jan 24, 2012

• #6

the immediatewindow should be at the buttom of your screen when you are in the VBA editor.

If not the go to menu:

View -> Immediate Window

JR

 

M

malaydash

Registered User.

Local time

Tomorrow, 01:08

Joined

Jan 18, 2010

Messages

43

• Jan 24, 2012

• #7

Thanks JANR

I got the immediate window. But can you please suggest what should be the code if I am linking Access Data to Excel File as "Table" & not as "Pivot Table", because when I am running the code it is showing the following error:-

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

Any suggestion please

Thanks & Regards

 

Rx_

Nothing In Moderation

Local time

Today, 13:38

Joined

Oct 22, 2009

Messages

2,803

• Feb 16, 2012

• #8

Immediate Window:
ActiveWorkbook.Connections(1).Name = "BE_ver2010_retire 2-7"

or to find out what it is:
? ActiveWorkbook.Connections(1).Name

B.T.W. in the Immediate Window the "?" is a shortcut for Debug.Print

Subroutine Version:

Code:Copy to clipboard

With ActiveWorkbook.Connections("BE_ver2010_retire 2-7")
  .Name = "BE_ver2010_retire 2-7"
  .Description = "Give it a name here"
End With
ActiveWorkbook.Connections("BE_ver2010_retire 2-7").Refresh

See Attachment:
-point at the first cell in you datarange
-Choose DATA menu
-choose Properties
-Select search - choose 2nd tab on next property