none
vb.net run a access 2007 query

    Question

  • Hi all,

    i need to know how i can run a access query that uses columnhistory in the sql, from .net

    i  set up a oledb connection, and i can run other querys but not one with this function in it. says can find function.

    any ideas?

     

     

    thanks

    Thursday, September 02, 2010 3:03 PM

Answers

  • If this is a user created VBA function that is being accessed by the Access query then it will not be available via the OLEDB Provider. Only Microsoft Access can execute these functions.

    Below is a list of the built-in functions that are available in sandbox mode when using OLEDB:

    http://support.microsoft.com/kb/239482

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, September 13, 2010 12:20 PM
  • Hi Dino,

     

    Welcome to MSDN forums!

     

    SELECT ColumnHistory("BEST_SHORE_PO","Comments","[ID]=" & Nz([id],0)) AS Comments, Request_Status, Date_Request FROM BEST_SHORE_PO AS X

    è  According to your description, the ColumnHistory and NZ function work fine in MS Access environment but not in VB.NET code, thus I think they are Access-specific functions, which is not recognized by Jet Provider as a valid one.

     

    Suggestion 1: Using IIf statement and IsNull to replace the NZ function

    e.g. SELECT IIF(ISNULL(inventory_id),0,inventory_id) AS inventory_id FROM inventory

     

    Suggestion 2: Manage to do your task by automating Access from VB.Net. Create a Marco in Access database, and use this Macro to run the query.

     

    Here is one threads discussing the similar issue to yours, please check it for detailed instruction.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/dd40a2ed-b4cc-47cf-96fe-c555709713c8

     

    KB reference: http://support.microsoft.com/kb/239482

    How to configure Jet 4.0 to prevent unsafe functions from running in Microsoft Access

     

    Best regards,

    Martin Xie

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 13, 2010 8:31 AM

All replies

  • Show your code sofar

     


    Success
    Cor
    Thursday, September 02, 2010 3:09 PM
  • My access query is as follows:

     

    INSERT INTO PO (comments,Request_Status,date_Request)

    SELECT ColumnHistory("BEST_SHORE_PO","Comments","[ID]=" & Nz([id],0)) AS Comments, Request_Status, Date_Request

    FROM BEST_SHORE_PO AS X

    WHERE (( modified > last_ran and modified < to_date) ) 

     

    The vb code is as follows.

     

    Static Dim myConnection As OleDbConnection = New OleDbConnection(GetConnectString(database_location))

     

    Static Dim myCommand As New OleDbCommand(Squery)

     

    If storeproc Then

    CreateCommand(Squery, params, CommandType.StoredProcedure, myCommand)

     

    Else

    CreateCommand(Squery, params, CommandType.Text, myCommand)

     

    End If

    myCommand.CommandTimeout = 90

    myCommand.Connection = myConnection

     

     

    Try

     

    myConnection.Open()

    myCommand.ExecuteNonQuery()

     

     

    myCommand.Dispose()

    myConnection.Close()

    myConnection.Dispose()

     

    Return ""

     

    Catch EXC As Exception

     

    If myConnection.State = ConnectionState.Open Then myConnection.Close()

     

    ' myDataAdapter.Dispose()

    myConnection.Dispose()

    myCommand.Dispose()

     

    Return EXC.Message

     

    End Try

     

     

    Thursday, September 02, 2010 3:22 PM
  • is this possible in .net?
    Thursday, September 02, 2010 5:34 PM
  • Hi Dino,

     

    Welcome to MSDN forums!

     

    SELECT ColumnHistory("BEST_SHORE_PO","Comments","[ID]=" & Nz([id],0)) AS Comments, Request_Status, Date_Request FROM BEST_SHORE_PO AS X

    è  According to your description, the ColumnHistory and NZ function work fine in MS Access environment but not in VB.NET code, thus I think they are Access-specific functions, which is not recognized by Jet Provider as a valid one.

     

    Suggestion 1: Using IIf statement and IsNull to replace the NZ function

    e.g. SELECT IIF(ISNULL(inventory_id),0,inventory_id) AS inventory_id FROM inventory

     

    Suggestion 2: Manage to do your task by automating Access from VB.Net. Create a Marco in Access database, and use this Macro to run the query.

     

    Here is one threads discussing the similar issue to yours, please check it for detailed instruction.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/dd40a2ed-b4cc-47cf-96fe-c555709713c8

     

    KB reference: http://support.microsoft.com/kb/239482

    How to configure Jet 4.0 to prevent unsafe functions from running in Microsoft Access

     

    Best regards,

    Martin Xie

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 13, 2010 8:31 AM
  • If this is a user created VBA function that is being accessed by the Access query then it will not be available via the OLEDB Provider. Only Microsoft Access can execute these functions.

    Below is a list of the built-in functions that are available in sandbox mode when using OLEDB:

    http://support.microsoft.com/kb/239482

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, September 13, 2010 12:20 PM