Home

Error when refreshing OLEDB connection - "Method Refresh of Object WorkbookConnection Failed"

%3CLINGO-SUB%20id%3D%22lingo-sub-517952%22%20slang%3D%22en-US%22%3EError%20when%20refreshing%20OLEDB%20connection%20-%20%22Method%20Refresh%20of%20Object%20WorkbookConnection%20Failed%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-517952%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20experiencing%20an%20issue%20with%20a%20workbook%20I've%20created%20that%20has%20VBA%20code%20embedded%20to%20refresh%20an%20OLEDB%20connection%20to%20SQL%20Server.%26nbsp%3B%20This%20all%20works%20fine%20on%20computers%20with%20Excel%202013%2C%20but%20I%20have%20a%20user%20on%20Excel%202010%20and%20it%20is%20not%20working%20on%20their%20computer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20message%20is%3A%3C%2FP%3E%3CP%3E%22Run%20time%20error%3A%20'-2147417848%20(80010108)'%3A%20Method%20'Refresh'%20of%20object%20'WorkbookConnection'%20failed%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20ActiveWorkbook.Connections(%22worksheet%22).OLEDBConnection%3CBR%20%2F%3E.Connection%20%3D%20Array(%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22OLEDB%3BProvider%3DSQLOLEDB.1%3BPersist%20Security%20Info%3DTrue%3BUser%20ID%3DUser%3BPassword%3DPassword%3BInitial%20Catalog%3DInitialTable%3BData%20Source%3DServerName%3B%22%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22Use%20Procedure%20for%20Prepare%3D1%3BAuto%20Translate%3DTrue%3BPacket%20Size%3D4096%3BWorkstation%20ID%3DA1234%3BUse%20Encryption%20for%20Data%3DFalse%3BTag%20with%20%22%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20%22column%20collation%20when%20possible%3DFalse%22)%3CBR%20%2F%3E.SavePassword%20%3D%20True%3CBR%20%2F%3E.CommandText%20%3D%20%22select%20*%20from%20query%22%3C%2FP%3E%3CP%3EActiveWorkbook.Connections(%22worksheet%22).Refresh%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20or%20suggestions%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-517952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
TheGush
Occasional Visitor

Hi All,

 

I'm experiencing an issue with a workbook I've created that has VBA code embedded to refresh an OLEDB connection to SQL Server.  This all works fine on computers with Excel 2013, but I have a user on Excel 2010 and it is not working on their computer.

 

The error message is:

"Run time error: '-2147417848 (80010108)': Method 'Refresh' of object 'WorkbookConnection' failed"

 

The code is:

 

With ActiveWorkbook.Connections("worksheet").OLEDBConnection
.Connection = Array( _
        "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=User;Password=Password;Initial Catalog=InitialTable;Data Source=ServerName;" _
        , _
        "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=A1234;Use Encryption for Data=False;Tag with " _
        , "column collation when possible=False")
.SavePassword = True
.CommandText = "select * from query"

ActiveWorkbook.Connections("worksheet").Refresh
End With

 

Any help or suggestions appreciated.

 

Thanks.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies