- Joined
- Feb 19, 2006
- Messages
- 1
- Reaction score
- 0
Hello
I am converting an existing Access sytem from a split mdb to using an adp connected to SQLExpress. This has been less painful than I feared and has produced mostly excellent results, but I have hit the 'cross tab' problem (ie analysing the user data that has been entered into the system). My solution so far has been to rebuild xtab query sources for supply to the Dynamic Cross-Tabs/Pivot Tables method (see http://www.sqlteam.com/item.asp?ItemID=2955, I thoroughly recommend it). This has enabled me to build complex 'cross tabs' in SQL server on the fly without writing ridiculous amounts of CASE...ELSE... statements. I have varied the method slightly by outputting data to a view that is then kind-of accessible from the adp.
However...
I now want to show the views once they are built and have hit the problem that unless I explicitly go to the adp database window, select the views tab and then F5, the view only half refreshes, ie it shows the data for the rebuilt view, but shows the metadata (ie column headers) from the previous version of the view. This isn't going to work in an ade (especially bearing in mind that end users would fill their pants if they saw the database window). SQLExpress shows the view changes straight away, so I know my sp is working.
I would like to know if there is a way of updating the view metadata/structure from code. I have tried the related suggestions on this board, but it all seems to come back to the same couple of problems:
can't return parameters from an sp into adp code (vba), can't refresh a view in adp code.
Am I missing a trick here, or is this a problem that I can't work round? If it's an issue with adp's should I be considering moving my large mdb to .NET, or waiting for a fix from microsoft?
Cheers
Andy
I am converting an existing Access sytem from a split mdb to using an adp connected to SQLExpress. This has been less painful than I feared and has produced mostly excellent results, but I have hit the 'cross tab' problem (ie analysing the user data that has been entered into the system). My solution so far has been to rebuild xtab query sources for supply to the Dynamic Cross-Tabs/Pivot Tables method (see http://www.sqlteam.com/item.asp?ItemID=2955, I thoroughly recommend it). This has enabled me to build complex 'cross tabs' in SQL server on the fly without writing ridiculous amounts of CASE...ELSE... statements. I have varied the method slightly by outputting data to a view that is then kind-of accessible from the adp.
However...
I now want to show the views once they are built and have hit the problem that unless I explicitly go to the adp database window, select the views tab and then F5, the view only half refreshes, ie it shows the data for the rebuilt view, but shows the metadata (ie column headers) from the previous version of the view. This isn't going to work in an ade (especially bearing in mind that end users would fill their pants if they saw the database window). SQLExpress shows the view changes straight away, so I know my sp is working.
I would like to know if there is a way of updating the view metadata/structure from code. I have tried the related suggestions on this board, but it all seems to come back to the same couple of problems:
can't return parameters from an sp into adp code (vba), can't refresh a view in adp code.
Am I missing a trick here, or is this a problem that I can't work round? If it's an issue with adp's should I be considering moving my large mdb to .NET, or waiting for a fix from microsoft?
Cheers
Andy