October 20, 2004 at 3:01 pm
I am converting an Access linked DB to an Access Project. I seem to be having a timing issue.
I have a form which uses VBA to create a View, then opens it. My problem is it comes back with an error of can't find the object [ViewName]. I know it creates the view, I can see it, but apparently Access Project cannot see it right after it is created.
My code:
Set ADOCmd = New ADODB.Command
Set ADOCmd.ActiveConnection = Dbs
SQL = "CREATE VIEW " & QueryName(UBound(QueryName)) & " AS " & SQL
ADOCmd.CommandText = SQL
Set Rs = ADOCmd.Execute
DoEvents
DoCmd.OpenView QueryName(UBound(QueryName)), acViewNormal
How do I refresh the Project or whatever so I can open the View? DoEvents obviously does not work.
Michael Lee
October 21, 2004 at 2:32 am
Look at the adox help on refreshing the views collection.
Doevents is a red herring. It does work, but not in the way you expect. The typical use of doevents is when you open a form to get parameters and then pick them up. Typical (pseudo) code is:
Hope that helps.
October 21, 2004 at 6:49 am
What you are looking for is :
Application.RefreshDatabaseWindow
October 21, 2004 at 9:27 am
We had an issue where our Access code appeared to execute faster than what our LAN could handle and we had to add code to actually have the application pause for a second before trying to access new objects. This is the only thing that would work. Everything would work fine if stepping through the code, but when executed normally, it would fail because when it checked for the object, it would appear to not be there. When stepping through the code, it would always be there, so we just added a piece of code that would pause for a second and then continue with the execution of the normal code and everything was fine. I have no explanation for this behavior, but it worked. Seems pretty crappy to have to add code to intentionally add execution time to an app!!!
October 21, 2004 at 12:09 pm
Thanks for all the tips. I know it is not a timing issue, because I can enter debug and wait as long as i wish, it still will not find the View.
The RefreshDatabaseWindow will not work for Views, for some reason (seems to be a lot of Googles on that subject w/o any solution)
I don't even begin to understand ADOX, but I am looking into that solution.
Y'all are great!
Michael Lee
October 21, 2004 at 2:10 pm
Well, you should not be creating VIEWS. You should be using a stored procedure and passing the parameters into it for execution.
Also, either you are missing some of the SQL or it is just designed wrong in your code you gave us.
1 Set ADOCmd = New ADODB.Command
2 Set ADOCmd.ActiveConnection = Dbs
3 SQL = "CREATE VIEW " & QueryName(UBound(QueryName)) & " AS " & SQL
4 ADOCmd.CommandText = SQL
On line 3 you have SQL being Appended to the string, but no value for it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply