VB for Changing an ODBC connection to Postgres

[newbie2009]

Hi everyone...

I need to write a VB procedure for an Excel PivotTable that will change its cached ODBC connection to a new Postgres ODBC connection.

I have part of the procedure written but I get an error when it is switched to the Postgres ODBC connection. My guess is that some of the arguements needed for the Postgres connection are missing.

Can anyone help with writing and posting a VB procedure that would switch from an existing ODBC Oracle connection to a Postgres ODBC connection?

Thanks!!!

 

[SkipVought]

hi,

I have part of the procedure written...

...and that would be...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Sub ChangeServer()
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = "Oracle_Prod"

'Request the name of the new server/file name.
newSrv = "Teradata_Prod"

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

 

[newbie2009]

The vb can be found above. I am trying to change this to a postgres database (i.e. NOT Teradata).

I am fairly certain that it does not contain enough parameters for the Postgres ODBC connection; so any help is appreciated.

 

[SkipVought]

Turn on your macro recorder

Select your PT.

Right-click and select Pivot Table Wizard.

[BACK]

In the MS Query QBE window, File > New... and change your Oracle server -- drill down to the table and drag into the QBE Grid.

File > return data to Excel

Turn off the macro recorder.

Observe your recorded code.

If you need help customizing, post back with your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Skip,

Loved the simple approach to solving this but when I tried to do this, after I complete the File > return data to Excel step, it will not let me stop the macro recording without clicking finish for the wizard. When Finish is clicked, it retrieves the data then issues a message of "unable to record" for the macro.

Is there another way to step through this?

Thanks!

 

[newbie2009]

Here are the two ODBC connections.

This is the current ODBC connection that needs to be changed:

DSN=Oracle_PROD;UID=JSMITH;; AUTHENTICATION=;AUTHENTICATONPARAMETER=;


Below is the desired ODBC connection that needs to replace the original one listed above:

DSN=Postgres_PROD;DATABASE=d1gp1;SERVER=10.50.128.95;PORT=5432;UID=jsmith;;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;Ma (<<<- I think it got cutoff in Excel)


Please help!

 

[newbie2009]

I was able to record this from the Macro recorder...Now how do I use it???

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=Postgres_PROD;DATABASE=xt7391;SERVER=10.90.199.25;PORT=6789;UID=jsmith;;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;FakeOidIndex" _
), Array( _
"=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxL" _
), Array( _
"ongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsCh" _
), Array( _
"ar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;" _
), Array( _
"BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1" _
))

 

[SkipVought]

assuming that you have ONE pivotcache...

Sub pivot()
    Dim sConn(1) As String, sSQL As String
    
    sConn(0) = "ODBC;DSN=Postgres_PROD;DATABASE=xt7391;SERVER=10.90.199.25;"
    sConn(0) = sConn(0) & "PORT=6789;UID=jsmith;;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;"
    sConn(0) = sConn(0) & "FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;"
    sConn(0) = sConn(0) & "ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;"
    sConn(0) = sConn(0) & "MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;"
    sConn(0) = sConn(0) & "Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;"
    sConn(0) = sConn(0) & "UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;"
    sConn(0) = sConn(0) & "ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;"
    sConn(0) = sConn(0) & "DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;"
    sConn(0) = sConn(0) & "UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1"
    
    sConn(1) = "DSN=Oracle_PROD;UID=JSMITH;; AUTHENTICATION=;AUTHENTICATONPARAMETER=;"

    With ThisWorkbook.PivotCaches(1)
        .Connection = sConn(0)
'        .CommandText = sSQL
    End With
End Sub

QUESTION: Do you use the SAME SQL in both dbs?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Skip,

Thanks..I will try this. I do have some really basic questions about the solution...

I just need to run this with the new ODBC connection information and do not need to specify changing this in the instances where the old connection is present?

Also, for this file, there is only one pivot table. If there are multiple pivot tables in the file, would this need to be run for each one or just once for the entire workbook containing all pivots?

Thanks.

 

[SkipVought]

I just need to run this with the new ODBC connection information and do not need to specify changing this in the instances where the old connection is present?

I Do not understand your question. Assign the new connection string, then refresh!

The number of PTs is irrelevant. It is the number of PivotCaches in the WORKBOOK.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Skip,

So if I have a workbook with several different pivots that point to the original connection, I will need to run this for each pivot table or once for the entire workbook?

Also, how do I modify my original Sub ChangeServer()with the Sub you provided? (Sorry for a dumb question but my skills are very basic.)

Thanks!

 

[SkipVought]

Change the PivotCache Connection property for each PivotCache...

Sub ChangeServer()
    Dim ptc As PivotCache, oldSrv As String, newSrv As String
    Dim sConn(1) As String, sSQL As String, WhichWay
    
'====================================================
'you really do not need this
    'Request the name of the old server/file name.
    oldSrv = "Oracle_Prod"
    
    'Request the name of the new server/file name.
    newSrv = "Teradata_Prod"
'====================================================
    
    WhichWay = InputBox("1) to Postgres; 2) to Oracle", "Change Source")
    
    'Replace the ODBC information of whatever PivotTable is currently active.
'    Set ptc = ActiveCell.PivotTable.PivotCache
'    ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)

    sConn(0) = "ODBC;DSN=Postgres_PROD;DATABASE=xt7391;SERVER=10.90.199.25;"
    sConn(0) = sConn(0) & "PORT=6789;UID=jsmith;;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;"
    sConn(0) = sConn(0) & "FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;"
    sConn(0) = sConn(0) & "ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;"
    sConn(0) = sConn(0) & "MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;"
    sConn(0) = sConn(0) & "Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;"
    sConn(0) = sConn(0) & "UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;"
    sConn(0) = sConn(0) & "ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;"
    sConn(0) = sConn(0) & "DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;"
    sConn(0) = sConn(0) & "UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1"
    
    sConn(1) = "DSN=Oracle_PROD;UID=JSMITH;; AUTHENTICATION=;AUTHENTICATONPARAMETER=;"

'assuming that you want ALL pivotcaches in the workbook changed
    For Each ptc In ThisWorkbook.PivotCaches
        With pct
            .Connection = sConn(WhichWay - 1)
            .Refresh
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Skip, I'd replace this:
With pct
with this:
With ptc

 

[SkipVought]

?mmmmh hmmmmm?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Skip,

I think we are getting close.

I ran the above but it errored on the following line:

.Connection = sConn(WhichWay - 1)

I couldnt figure out what it didnt like. It was executed entering 1 for the choice which should have defaulted to an sConn(0).

Also, if this works, I will remove the original stuff as it is just clutter.

BIG THANKS again for all your help. The world is a far better place with smart folks like you.

 

[SkipVought]

what was the error message?

does choosing the Oracle connection work?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

Skip,

It just gives me a bland error message with the option to run the debug console. In the console, it just highlights the offending line in yellow ---> .Connection = sConn(WhichWay - 1)

I am guessing it is having a problem doing the calculation...In the code, does the datatype for WhichWay need to be declared?

Dim ptc As PivotCache, oldSrv As String, newSrv As String
Dim sConn(1) As String, sSQL As String, WhichWay

Thanks!

 

[SkipVought]

Does choosing the Oracle connection work???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newbie2009]

No, I received the same error.