ODBC errors with new pivot table

[John Michl]

Here's the problem.
- I opened a file called Results.xls that was developed by someone else. In
it there are several sheets that automatically extract data from an Access
database to run pivot tables and extracts.

- I added two new sheets each with a pivot table that connects to the same
Access database and run a pivot based on an Access query. I originally
connected to the Access database by following the pivot table wizard.

- The pivots work fine on my machine but when the other user pulls it up she
gets the following errors:

<<First Error>> [Microsoft][ODBC Driver Manager]Data Source name not found
and no default driver specified

<<Second Error>> [Microsoft][ODBC Microsoft Access Driver]General error,
Unable to open registry key 'SOFTWARE\ODBC\ODBC.INI\Results.

<<Third Error>> [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr
failed

<<Fourth Error>> The following data range failed to refresh from MS Access
database. Continue to refresh all?

This user is on Excel 97 and win2000. I'm on Excel 2002 and XP pro.

Two other notes:
The first time I recieved Error 1 on her machine, I was prompted to set up a
data connection which I did and called Results.
Reminder - these new pivot tables link to the very same Access database as
all of the other pivots and data extracts in this file so I would think it
would work.

Questions:
Is there a way to determine the actual connection for the earlier pivot
tables and then change my newer ones to match? I assume this would require
some VBA code.

Or, any ideas on what may be causing this? I want to avoid needing to set
up connection strings specific to each machine, if at all possible.

Thanks
- John Michl

 

[John Michl]

I believe I solved the problem using the following utilities I found while
searching Google Groups. Turns out there was DSN reference at the start of
connection in my new pivot tables but not in the original pivots. It stated
something like ODBC;DSN = Microsoft Access97...I used the change server
utility below to remove the DSN reference and it appears to work fine now.

- John Michl

============================================================================
=======================



Sub ListSourceData()
'Declare our variables.
Dim newSheet As Worksheet, sdArray As Variant
Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

'Set our variables.
Set oldSheet = ActiveSheet
Set newSheet = ActiveWorkbook.Worksheets.Add

newSheet.Range("A1").Value = oldSheet.Name
r = 3

'Loop through each PivotTable on the active sheet and place its ODBC
information on a new sheet.
For Each pt In oldSheet.PivotTables
newSheet.Cells(r, 1).Value = pt.Name
newSheet.Cells(r + 1, 1).Value = pt.PivotCache.Connection
newSheet.Cells(r + 2, 1).Value = pt.PivotCache.Sql
r = r + 4
Next pt
newSheet.Cells.EntireColumn.ColumnWidth = 100
newSheet.Cells.EntireRow.AutoFit
End Sub

Sub ChangeServer()
'Declare our variables.
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = InputBox("Input the name of the old server or file path as
listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newSrv = InputBox("Input the name of the new server or file path which
you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently
active.
Set ptc = ActiveCell.PivotTable.PivotCache
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)
ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub

Here's the problem.
- I opened a file called Results.xls that was developed by someone else. In
it there are several sheets that automatically extract data from an Access
database to run pivot tables and extracts.

- I added two new sheets each with a pivot table that connects to the same
Access database and run a pivot based on an Access query. I originally
connected to the Access database by following the pivot table wizard.

- The pivots work fine on my machine but when the other user pulls it up she
gets the following errors:

<<First Error>> [Microsoft][ODBC Driver Manager]Data Source name not found
and no default driver specified

<<Second Error>> [Microsoft][ODBC Microsoft Access Driver]General error,
Unable to open registry key 'SOFTWARE\ODBC\ODBC.INI\Results.

<<Third Error>> [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr
failed

<<Fourth Error>> The following data range failed to refresh from MS Access
database. Continue to refresh all?

This user is on Excel 97 and win2000. I'm on Excel 2002 and XP pro.

Two other notes:
The first time I recieved Error 1 on her machine, I was prompted to set up a
data connection which I did and called Results.
Reminder - these new pivot tables link to the very same Access database as
all of the other pivots and data extracts in this file so I would think it
would work.

Questions:
Is there a way to determine the actual connection for the earlier pivot
tables and then change my newer ones to match? I assume this would require
some VBA code.

Or, any ideas on what may be causing this? I want to avoid needing to set
up connection strings specific to each machine, if at all possible.

Thanks
- John Michl

 

[John Michl]

Me again. Apparently that didn't work. In fact, I've tried deleted the
sheets that I added and I still get the errors on the machine with XL97.
I'm starting to think that when I opened the file and did my work in Excel
2002, something funky happened to all of the queries. For kicks, I tried
saving the file (on my 2002 machine) to the 97 format and received a few
errors and warnings regarding query references.

Any ideas?

Thanks - John

I believe I solved the problem using the following utilities I found while
searching Google Groups. Turns out there was DSN reference at the start of
connection in my new pivot tables but not in the original pivots. It stated
something like ODBC;DSN = Microsoft Access97...I used the change server
utility below to remove the DSN reference and it appears to work fine now.

- John Michl

============================================================================
=======================



Sub ListSourceData()
'Declare our variables.
Dim newSheet As Worksheet, sdArray As Variant
Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

'Set our variables.
Set oldSheet = ActiveSheet
Set newSheet = ActiveWorkbook.Worksheets.Add

newSheet.Range("A1").Value = oldSheet.Name
r = 3

'Loop through each PivotTable on the active sheet and place its ODBC
information on a new sheet.
For Each pt In oldSheet.PivotTables
newSheet.Cells(r, 1).Value = pt.Name
newSheet.Cells(r + 1, 1).Value = pt.PivotCache.Connection
newSheet.Cells(r + 2, 1).Value = pt.PivotCache.Sql
r = r + 4
Next pt
newSheet.Cells.EntireColumn.ColumnWidth = 100
newSheet.Cells.EntireRow.AutoFit
End Sub

Sub ChangeServer()
'Declare our variables.
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = InputBox("Input the name of the old server or file path as
listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newSrv = InputBox("Input the name of the new server or file path which
you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently
active.
Set ptc = ActiveCell.PivotTable.PivotCache
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)
ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub

Here's the problem.
- I opened a file called Results.xls that was developed by someone else. In
it there are several sheets that automatically extract data from an Access
database to run pivot tables and extracts.

- I added two new sheets each with a pivot table that connects to the same
Access database and run a pivot based on an Access query. I originally
connected to the Access database by following the pivot table wizard.

- The pivots work fine on my machine but when the other user pulls it up she
gets the following errors:

<<First Error>> [Microsoft][ODBC Driver Manager]Data Source name not found
and no default driver specified

<<Second Error>> [Microsoft][ODBC Microsoft Access Driver]General error,
Unable to open registry key 'SOFTWARE\ODBC\ODBC.INI\Results.

<<Third Error>> [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr
failed

<<Fourth Error>> The following data range failed to refresh from MS Access
database. Continue to refresh all?

This user is on Excel 97 and win2000. I'm on Excel 2002 and XP pro.

Two other notes:
The first time I recieved Error 1 on her machine, I was prompted to set

up

a
data connection which I did and called Results.
Reminder - these new pivot tables link to the very same Access database as
all of the other pivots and data extracts in this file so I would think it
would work.

Questions:
Is there a way to determine the actual connection for the earlier pivot
tables and then change my newer ones to match? I assume this would require
some VBA code.

Or, any ideas on what may be causing this? I want to avoid needing to set
up connection strings specific to each machine, if at all possible.

Thanks
- John Michl