Moving the data source

[Phil Atkinson]

Hi,

I have created an ODBC connection to an Access DB for data retrieval to an
excel file. All was working great, but now I have to move the Access DB to
another location on the server. I changed the ODBC connection in the control
panel to point to the newly located database, but Excel is still looking for
the old location.

What should I do?

Help appreciated.

Phil

[rger...@fox.nstn.ca]

I've done this before with success. However, I was not sucessful on
my first attempt because of the confusing array of DSN's with similar
names in the ODBC Data Sources applet. You might check to be sure you
have changed the correct DSN.

[Chris Pettingill]

I was having somewhat similar problems with a Pivot Table using data from an
Access database. When you reference external data, you have to use an ODBC
connection. However Excel pulls the path from ODBC connection, and
hard-codes the path into the connection to the data. Depending on the
version of MSQuery you're using (I assume you used this to create your
query) and it's options, it may be also including the path to your DB in the
SQL query. Here's how I solved the problems* (so I could move my DB and
spreadsheet around):

1) Create/Open a query in MS-Query. Go to Edit|Options. Make sure
'Qualify table names in SQL statement' is UNCHECKED. Press [OK]. From this
point on, any queries created with MS-Query (directly or indirectly via
Excel) will not include the path to the actual DB in your SQL. You'll now
have to re-create any queries in Excel to make sure the path to your Access
DB is no longer included in your queries.
2) Create some code to fire on the 'Open' event of your work book. The
code below is what I used. You'll probably have to modify it a bit for your
case, as mine only deals with pivot tables that use external data:

Private Sub Workbook_Open()
Dim ws As WorkSheet
Dim pt As PivotTable
Dim dbPath As String

On Error Resume Next
For Each ws In ActiveWorkbook.Sheets
For Each pt In ws.PivotTables

'For me, my Access Db is always in the same directory
' as my spreadsheet, this may be different for you
dbPath = ActiveWorkbook.Path

'Make sure the path ends in '\'
If Right( dbPath, 1) <> "\" Then
dbPath = dbPath + "\"
End If

'My ODBC DSN is called AggInd32, and
' must be set up on any machine using this spreadsheet
'My Access Db is called 'AggInd32.mdb'
pt.PivotCache.Connection = "ODBC;DSN=AggInd32;DBQ=" & dbPath &
"AggInd32.mdb"

Next pt
Next ws
End Sub

You'll probably using the QueryTable(s) objects rather than the
PivotTable(s) objects, but the approach will be similar.

*Note: Assumes you're using the version of MS Query that comes with Office
2000 (rather than '97)

"Phil Atkinson" <ph...@firstenergy.no> wrote in message
news:9m5gda$lsj$1...@oslo-nntp.eunet.no...

[Chris Pettingill]

A few extra thoughts...

Instead of:

pt.PivotCache.Connection = "ODBC;DSN=AggInd32;DBQ=" & dbPath &
"AggInd32.mdb"

you could use:

pt.PivotCache.Connection = "ODBC;DSN=AggInd32"

This will force excel to find the DB whereever your ODBC DSN is pointing to.
This was not feasible for me since the DB may be in another location for me.
Excel '97 needed the ODBC stuff to work though, so I couldn't leave it out
since my spreadsheet needs to work in Excel 97 and 2000. With Excel 2000,
you can connect with OLEDB, and do without any ODBC:

pt.PivotCache.Connection = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & dbPath & "aggind32.mdb"

"Chris Pettingill" <ChrisPe...@Compuserve.Com> wrote in message
news:#W0btaXMBHA.1676@tkmsftngp05...

[Patrick Enoux]

Hi,

I've the same problem with multiple Pivot tables in Excel '97 workbook, using
data from Access '97 DB. I just want to be able to move, copy or rename the
directory where I put the .xls and .mdb files, just like you.

I tried the script you suggest and it's still not working. Is it because of MS
Query version ? If so, how can I upgrade MS Query or ODBC driver without
upgrading to Office 2000 ?

Patrick.

Chris Pettingill a écrit :

[Chris Pettingill]

If you have a copy of Office 2000, you may be able to just install a newer
MS Query without installing anything else. Other than that, I think you'll
have to manually change the SQL in Code using PivotCache.SQL. This of
course will be a pain because any code you have will now have to be aware of
which specific pivot table you're dealing with.

"Patrick Enoux" <patric...@francetelecom.com> wrote in message
news:3BA0E1F8...@francetelecom.com...

[Scott]

Chris, I've been looking for a solution like this for years. However, while
basing a pivot table on an ODBC source and using your code allows pivot
chart to open and correct path, after changing the database name/location
and odbc path, it gives the error about the missing path.

how can you get to the embedded hardcoded path in excel and change it?

"Chris Pettingill" <ChrisPe...@Compuserve.Com> wrote in message
news:#W0btaXMBHA.1676@tkmsftngp05...

[Chris Pettingill]

My solution assumes that the Access DB and Excel spreadsheet will always be
in the same directory, so you can copy both to wherever you want, and things
will work. I think what you're asking for is a spreadsheet that may be
located anywhere. Your DB may not be in the same directory as the
spreadsheet. The DB's location will be specified by the ODBC alias, and the
location of the DB may change from time to time. You will correct this
location change in the ODBC alias, and expect Excel to pick that change up.

Basically, you need to do the same thing as I'm doing with one change.
Instead of the line:

' Overrides the ODBC DB path
pt.PivotCache.Connection = "ODBC;DSN=AggInd32;DBQ=" &_
dbPath & "AggInd32.mdb"

Use:

' Causes Excel to refresh the settings from the AggInd32 ODBC alias
' including any path changes.
pt.PivotCache.Connection = "ODBC;DSN=AggInd32;"

This forces Excel to reload the parameters of the ODBC alias 'AggInd32'
including any new directory path. Unlike my 1st version, I'm not overriding
the db path specified by ODBC.

"Scott" <salex...@msn.com> wrote in message
news:eNJ4wIKPBHA.1548@tkmsftngp03...