View Will Not Refresh? Creating Dynamic SQL

[smedvid]

I am perplexed over this one... I have a screen where I permit the users to construct an SQL Select Statement and Where Clause. It works great! Problem is on second execution of SQL to create a view, the original view fields appear.

Is there a option on Execute to force database cache to clear or insure new records, or is the issue on the Create View? Any clues are appreciated.



'Delete Existing View If Found
' Create temp view
lcSQL = " IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " _
& " WHERE TABLE_NAME = '" & lcViewName & "')" _
& " DROP VIEW " & lcViewName & " "

DBConn.Execute lcSQL, , adExecuteNoRecords

lcSQL = "CREATE VIEW " & lcViewName & " AS (" & Me.txtSQL.Value & ")"

DBConn.Execute lcSQL, , adExecuteNoRecords

DoCmd.OpenView lcViewName, , acReadOnly



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[smedvid]

After extensive research... my solution was to refresh the connection.. before launching the view via OpenView
This solved the issue... Albeit, not te optimal solution, it works...



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...