How to run a store procedure using VBA

Question

Hello experts,

      I want to call a store procedure using excel vba from SAP HANA.

This is what i wrote in vba by:

Note: I have already enable the reference library Microsoft ActiveX Data Objects 6.1 Library

Sub runstoreproc()

Dim conn As ADODB.Connection 

Dim cmd As ADODB.Command 

Dim strconn As String 

strconn = "Driver=HanaAkash;" & \_ 

           "Server=xxx.xxx.xx.xx;" & \_ 

           "UID=SYSTEM;PWD=ABC@12345;" & \_ 

           "Database=mydatabase;" & \_ 

           "Trusted\_Connection=yes;" 

Set conn = New ADODB.Connection 

Set cmd = New ADODB.Command 

conn.Open strconn 

cmd.ActiveConnection = conn 

cmd.CommandType = adCmdStoredProc 

cmd.CommandText = "mystoreproc" 

cmd.Execute 

Set cmd = Nothing 

Set conn = Nothing 

End Sub

Please let me know if i am doing something wrong

Run-time error * 2147467259 (80004005)':

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have already created the ODBC DSN but I don't know why I am getting this error

Any help world really be appreciable

Answer 1

Hi Saiyam,

Greetings! Thank you for posting to Microsoft Community.

I find a reference may help.

Open ADO connection and Recordset objects - SQL Server | Microsoft Learn

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

Sub runstoreproc()

 Dim conn As New adodb.Connection 

 Dim cmd As New adodb.Command 

 Dim strconn As String  

strconn = "Driver=HanaAkash;" & "Server=xxx.xxx.xx.xx;" & "UID=SYSTEM;PWD=ABC@12345;" & "Database=mydatabase;" & "Trusted_Connection=yes;"

conn.Open strconn  

cmd.ActiveConnection = conn  

cmd.CommandType = adCmdStoredProc  

cmd.CommandText = "mystoreproc"  

cmd.Execute  

Set cmd = Nothing  

Set conn = Nothing  

End Sub

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

Best Regards,

Snow Lu

Answer 2

Hi Saiyam,

Greetings! Thank you for posting to Microsoft Community.

I find a reference may help.

Open ADO connection and Recordset objects - SQL Server | Microsoft Learn

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

Sub runstoreproc()

Dim conn As New adodb.Connection

Dim cmd As New adodb.Command

Dim strconn As String

strconn = "Driver=HanaAkash;" & "Server=xxx.xxx.xx.xx;" & "UID=SYSTEM;PWD=ABC@12345;" & "Database=mydatabase;" & "Trusted_Connection=yes;"

conn.Open strconn

cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "mystoreproc"

cmd.Execute

Set cmd = Nothing

Set conn = Nothing

End Sub

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

Best Regards,

Snow Lu

Hi Snow Lu MSFT

No this is not working it is still giving me the same error!

I am confused that why you concat the strconn line??

Answer 3

In reference to the error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

1. That error means that the Data Source Name (DSN) you are specifying in your connection configuration is not being found in the windows registry.
   • It is important that your ODBC driver's executable and linking format (ELF) is the same as your application. In other words, you need a 32-bit driver for a 32-bit application or a 64-bit driver for a 64-bit application. If these do not match, it is possible to configure a DSN for a 32-bit driver and when you attempt to use that DSN in a 64-bit application, the DSN won't be found because the registry holds DSN information in different places depending on ELF (32-bit versus 64-bit). Be sure you are using the correct ODBC Administrator tool. On 32-bit and 64-bit Windows, the default ODBC Administrator tool is in c:\Windows\System32\odbcad32.exe. However, on a 64-bit Windows machine, the default is the 64-bit version. If you need to use the 32-bit ODBC Administrator tool on a 64-bit Windows system, you will need to run the one found here: C:\Windows\SysWOW64\odbcad32.exe Where I see this tripping people up is when a user uses the default 64-bit ODBC Administrator to configure a DSN; thinking it is for a 32-bit DSN. Then when the 32-bit application attempts to connect using that DSN, "Data source not found..." occurs.
   • It's also important to make sure the spelling of the DSN matches that of the configured DSN in the ODBC Administrator. One letter wrong is all it takes for a DSN to be mismatched. Here is an article that may provide some additional details It may not be the same product brand that you have, however; it is a generic problem that is encountered when using ODBC data source names.
2. In reference to the OLE DB Provider portion of your question, it appears to be a similar type of problem where the application is not able to locate the configuration for the specified provider.

Reference: installation - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified - Stack Overflow