Call HANA Procedure from MS-Access

[Former Member]

I would like to call a procedure from MS-Access. Does anyone have an easy way to do this?

Thanks

[rama_shankar3 · Accepted solution]

Why would you want to call a HANA procedure from Access? I suggest that you use a calculation view to do your logic and use your calculation view instead.

Please expand on what you are trying to do.

Regards,

Rama

[Former Member · Accepted solution]

Because I need to obtain a large amount of data that I want to move to Access, I have created a Calc View that creates exactly what I want, then a Procedure which will dump that View into a HANA user table, which I can then connect to from Access (since Access can't use Views - only Tables). So I want to call the procedure from Access, so I get a fresh user table to pull into Access. This helps me avoid a 2 step (1) go to HANA Studio to run the procedure (2) back to Access to pull the refreshed table.

[Former Member · Accepted solution]

How about if I change this a litte: How does one call a HANA Procedure (or even SQL queries) from Visual Basic (v7) - which is in MS-Access. I am not an expert VB programmer, but nothing I have tried seems to work.

[Former Member · Accepted solution]

Ok. Finally figured this out. Spent many hours struggling with this - since I am not really a VB expert. But in case others are interested. Does not look so compicated once you know how.

-------------------------------------------------

Sub RunStoreProc()
  Dim wrkMain As Workspace
  Dim dbsHANA As Database
  
   Set wrkMain = CreateWorkspace("", "admin", "", dbUseJet)

   Set dbsHANA = wrkMain.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=SAPHANAPR1")

   dbsHANA.Execute "CALL PR_TEST('')", dbSQLPassThrough

   dbsHANA.Close
   wrkMain.Close

End Sub

-------------------------------------------------