Altered views not executing in access project

  • Hi

    when altering a view backend in sql server, the alteration is not reflected when the view is executed withing access project. let me explain a little more:

    i open access project and execute the view - fine. then i alter the view backend. i then re-execute this view front end and this alteration does not show up. (within the design mode in access i can see this alteration)

    thanks

    pete

  • On some views I have found you need to refresh the connection to SQL Server.

  • Like SQL views, Access holds details of the database schema and does not recognise SQL changes until the Access tables/views are refreshed.

    In an Access MDB/MDE you need to refresh the linked tables/views using the Linked Table Manager (Tools > Database Utilities > Linked Table Manager)

    In an Access ADP/ADE you need to refresh the SQL Connection (File > Connection, then click the Refresh button)

    HTH

    🙂

    Chris

  • thats brilliant thanks. do you happen to know the VBA to trigger this? I have:

    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = CurrentProject.AccessConnection

    cmd.CommandText = strSQLFinal

    which executes some sql commands.

    do I need to refresh CurrentProject.AccessConnection? if so how?

    thanks

    Pete

  • Hi Pete,

    sorry for the delay in responding - I've been trying to work out the bit of VB to do this. If you add the following lines of code somewhere in the initial application launch (eg: in the Form_Load event for the main form) this will close/reopen the connection, which will refresh any SQL schema changes.

    Dim tmpConnectionString As String

    tmpConnectionString = CurrentProject.BaseConnectionString

    CurrentProject.CloseConnection

    CurrentProject.OpenConnection (tmpConnectionString)

    😉

    Chris

  • Thank you ever so much :0)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply