I have a worksheet with a Pivot Table whose source data is an Access Database
encrypted using a Workgroup File (.mdw). I created a User DSN within the
OBDC Object Administrator and I specified the .mdw file. I created the
PivotTable by pointing to my DSN data source and I created the new Pivot
Table succesfully.

I moved the files (.mdb .mdw and .xls) to another computer with the same
versions of Windows and Office and recreated the DSN entry but now I can not
refresh the data. I get an error message saying "OBDC Microsoft Access
Driver Login Failed. Cannot start your application. The workgroup
information file is missing or opened exclusively by another user". What is
wrong? How can I check the workgroup file used in the original pivottable in
VBA and how can I change it?

FCS wrote:
I have a worksheet with a Pivot Table whose source data is an Access
Database encrypted using a Workgroup File (.mdw). I created a User
DSN within the OBDC Object Administrator and I specified the .mdw
file. I created the PivotTable by pointing to my DSN data source and
I created the new Pivot Table succesfully.

I moved the files (.mdb .mdw and .xls) to another computer with the
same versions of Windows and Office and recreated the DSN entry but
now I can not refresh the data. I get an error message saying "OBDC
Microsoft Access Driver Login Failed. Cannot start your application.
The workgroup information file is missing or opened exclusively by
another user". What is wrong? How can I check the workgroup file
used in the original pivottable in VBA and how can I change it?

Sadly, the external data query doesn't just point to the DSN, it actually
stores the database location. Go to the Immediate Window (Alt-F11, Cntl+G)
and type

?Sheet1.PivotTables(1).PivotCache.Connection

and

?Sheet1.PivotTables(1).PivotCache.CommandText

and see if those string don't contain the path to the old mdw. You can read
this page

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

It doesn't discuss PivotTables specifically, but it will give you an idea of
what the Connection and CommandText strings look like. Every time you see
something like

Sheet1.QueryTables(1)

just replace

Sheet1.PivotTables(1).PivotCache

and it should work similarly. I've never based a PT on and mdw, so there
maybe something more going on than I can see, but this should give you a
start.


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

"Dick Kusleika" wrote:

FCS wrote:
I have a worksheet with a Pivot Table whose source data is an Access
Database encrypted using a Workgroup File (.mdw). I created a User
DSN within the OBDC Object Administrator and I specified the .mdw
file. I created the PivotTable by pointing to my DSN data source and
I created the new Pivot Table succesfully.

I moved the files (.mdb .mdw and .xls) to another computer with the
same versions of Windows and Office and recreated the DSN entry but
now I can not refresh the data. I get an error message saying "OBDC
Microsoft Access Driver Login Failed. Cannot start your application.
The workgroup information file is missing or opened exclusively by
another user". What is wrong? How can I check the workgroup file
used in the original pivottable in VBA and how can I change it?

Sadly, the external data query doesn't just point to the DSN, it actually
stores the database location. Go to the Immediate Window (Alt-F11, Cntl+G)
and type

?Sheet1.PivotTables(1).PivotCache.Connection

and

?Sheet1.PivotTables(1).PivotCache.CommandText

and see if those string don't contain the path to the old mdw. You can read
this page

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

It doesn't discuss PivotTables specifically, but it will give you an idea of
what the Connection and CommandText strings look like. Every time you see
something like

Sheet1.QueryTables(1)

just replace

Sheet1.PivotTables(1).PivotCache

and it should work similarly. I've never based a PT on and mdw, so there
maybe something more going on than I can see, but this should give you a
start.


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

Thanks for your reference. I have made some tests and it seems that the mdw
file name is stored in the PT. It can be seen in the PivotCahe Connection
property and it follows the format "SystemDB=<file". Any idea on how can I
force it to read again the entry from the DSN?

"Dick Kusleika" wrote:

FCS wrote:
I have a worksheet with a Pivot Table whose source data is an Access
Database encrypted using a Workgroup File (.mdw). I created a User
DSN within the OBDC Object Administrator and I specified the .mdw
file. I created the PivotTable by pointing to my DSN data source and
I created the new Pivot Table succesfully.

I moved the files (.mdb .mdw and .xls) to another computer with the
same versions of Windows and Office and recreated the DSN entry but
now I can not refresh the data. I get an error message saying "OBDC
Microsoft Access Driver Login Failed. Cannot start your application.
The workgroup information file is missing or opened exclusively by
another user". What is wrong? How can I check the workgroup file
used in the original pivottable in VBA and how can I change it?

Sadly, the external data query doesn't just point to the DSN, it actually
stores the database location. Go to the Immediate Window (Alt-F11, Cntl+G)
and type

?Sheet1.PivotTables(1).PivotCache.Connection

and

?Sheet1.PivotTables(1).PivotCache.CommandText

and see if those string don't contain the path to the old mdw. You can read
this page

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

It doesn't discuss PivotTables specifically, but it will give you an idea of
what the Connection and CommandText strings look like. Every time you see
something like

Sheet1.QueryTables(1)

just replace

Sheet1.PivotTables(1).PivotCache

and it should work similarly. I've never based a PT on and mdw, so there
maybe something more going on than I can see, but this should give you a
start.


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

FCS wrote:
Thanks for your reference. I have made some tests and it seems that the
mdw file name is stored in the PT. It can be seen in the PivotCahe
Connection property and it follows the format "SystemDB=<file". Any
idea on how can I force it to read again the entry from the DSN?


Yes, you can recreate the pivot table. That option stinks, I'm sure you'll
agree. You can change the location of the db in the pivottable. It doesn't
'read' it from the DSN, but it has the same effect.

Sub Changedbloc()

Const sOLDPATH As String = "C:\OldFolder\"
Const sNEWPATH AS String = "C:\NewFolder\"

With Sheet1.PivotTables(1).PivotCache
.Connection = Replace(.Connection, sOLDPATH, sNEWPATH)
.Refresh
End With
End Sub


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Agree, is this not a Bug?. The main issue is that I have to rely on the user
specifying the same mdw as in the DNS and keeping consistency which is
clearly a problem.

I've also noticed that the PivotCache connection property (as reported in
the VBA watch) seems to truncate the end of the string when using long file
path names although it seems that "internally" things function properly. I
see the same issue in the SourceData property (array) of the PivotTable. Is
this just a reporting issue? I am concerned that the code that you propose
may not work in that case.

"Dick Kusleika" wrote:

FCS wrote:
Thanks for your reference. I have made some tests and it seems that the
mdw file name is stored in the PT. It can be seen in the PivotCahe
Connection property and it follows the format "SystemDB=<file". Any
idea on how can I force it to read again the entry from the DSN?


Yes, you can recreate the pivot table. That option stinks, I'm sure you'll
agree. You can change the location of the db in the pivottable. It doesn't
'read' it from the DSN, but it has the same effect.

Sub Changedbloc()

Const sOLDPATH As String = "C:\OldFolder\"
Const sNEWPATH AS String = "C:\NewFolder\"

With Sheet1.PivotTables(1).PivotCache
.Connection = Replace(.Connection, sOLDPATH, sNEWPATH)
.Refresh
End With
End Sub


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

FCS wrote:
Agree, is this not a Bug?. The main issue is that I have to rely on
the user specifying the same mdw as in the DNS and keeping
consistency which is clearly a problem.

Is your mdw location changing that much? Generally you would point to the
database and you would never need to change it unless you moved the
database. Are you sending this file to other people who have their database
in different locations? If so, you may want to ask them via GetOpenFilename
where the database is the first time they open the file, then you shouldn't
have to change it.

Is it a bug? Surely MS will claim it's a feature or a decision.


I've also noticed that the PivotCache connection property (as
reported in the VBA watch) seems to truncate the end of the string
when using long file path names although it seems that "internally"
things function properly. I see the same issue in the SourceData
property (array) of the PivotTable. Is this just a reporting issue?
I am concerned that the code that you propose may not work in that
case.

The following may or may not be true: If your connection string is greater
than 255 characters, it's actually stored in an array of strings, not just a
string. Same goes for the CommandText property. If you were to record a
macro that creates an external data table, you would see code like

Connection:=Array("DSN: Some data; somemore data;")

The recorder puts the string in an array even if it's not a long string,
which leads me to believe that it *has* to be in an array if it's too long.
I think if you want to see the rest of the long connection string, you would
have to use

?Sheet1.PivotTables(1).PivotCache.Connection(1) 'may be zero
?Sheet1.PivotTables(1).PivotCache.Connection(2) 'may be one

but I haven't tested it to see if it's true. SourceData is, I believe, a
deprecated property which has not kept up with the changes to the querytable
object. I don't think you can rely on that property in any case. All this
may not be true because I've asked Microsoft directly and have never
received an answer.

I think you're right that my code would fail for long connection strings.
You need to convert the string into an array before you use it. Honestly,
I've never had that code fail even with long connection strings, but it must
be possible.

Function StringToArray(sInput As String) As Variant

Dim i As Long
Dim lCount As Long
Dim aTemp() As String

For i = 1 To Len(sInput) Step 255
lCount = lCount + 1
ReDim Preserve aTemp(1 To lCount)
aTemp(lCount) = Mid(sInput, i, 255)
Next i

StringToArray = aTemp

End Function

Run your strings through that function before you put them into the
Connection or CommandText property and they should work under any
circumstances. You'd use the function like:

Dim vaConn as Variant

vaConn = StringToArray("Somelongstring")


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com