CODEWITHSUNDEEP
functionmodulems-access-2007
user2806630
user2806630

Reputation: 41

How to call ms-access function from C#?

I have written an ms-access function in a module called ‘StripLead’. I made a single line procedure called ‘CallFunction’. Now when I call this procedure from ms-access it works fine. But when I call this procedure from C# I get error:

Undefined function 'Striplead' in expression.

Ms-access module code:

Public Function StripLead(pstrPhrase As String) As String
  Dim strFirstWord As String
  Dim strReturn As String
  Dim intPos As Integer

  strReturn = pstrPhrase
  intPos = InStr(pstrPhrase, " ")
  If intPos > 0 Then
    strFirstWord = Left$(pstrPhrase, intPos - 1)
    Select Case strFirstWord
      Case "A", "An", "The"
      strReturn = Right$(pstrPhrase, Len(pstrPhrase) - intPos)
    End Select
  End If

StripLead = strReturn
End Function

Ms-access procedure "CallFunction"(sql view):

SELECT Customers.ID, Striplead([ContactName]) AS a FROM Customers;

C# code: 

   public void CallStoredProcedure(string NewQry)
       {
           try
           {
            DataTable tbl = new DataTable();
            using (OleDbConnection connection = new OleDbConnection(ConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "CallFunction";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = connection;

                using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                {
                    adp.Fill(tbl);
                }
            }

         }
        catch(Exception ex)
        {
            throw;   
        }
    }

Please suggest whether the approach is incorrect. Or else, if there is a way to make call to ms-access function from C# to fetch data. My aim is to fetch modified data from ms-access and fill the datagrid in C#.

Upvotes: 2

Views: 2771

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

Access offers the ability to call user-defined VBA functions from SQL queries, but that capability is restricted to queries that are made from within the Microsoft Access application itself. That is because the VBA functions require the Access application's infrastructure (specifically, the VBA "Expression Service") to execute.

In your case you might be able to run a "real" Access query from within your C# code using Microsoft.Office.Interop.Access but that would give you a DAO Recordset object, and fiddling around with that to work with an OleDbDataAdapter could well be more trouble than it's worth (if it is possible at all).

You'll probably just be better off to re-create the VBA function logic in C#.

Upvotes: 3

Related Questions