August 8, 2008 at 8:13 am
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
August 12, 2008 at 4:01 pm
On some views I have found you need to refresh the connection to SQL Server.
August 13, 2008 at 4:28 pm
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
August 19, 2008 at 9:34 am
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
August 20, 2008 at 10:21 pm
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
September 3, 2008 at 2:53 am
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