[AccessD] Query runs and doesn't run

Wed Jan 10 14:23:03 CST 2018

I tried it as Application.RefreshDatabaseWindow and it worked fine. There may be other ways.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, January 10, 2018 2:16 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query runs and doesn't run

[This email message was received from the Internet and came from outside of Kinder Morgan]

My code sample had a two-step toggle, because a database could be in a mode where the pane is not shown, and I have seen that closing and reopening the pane works wonders, manually, therefore anticipated like success when done with VBA.

Granted, in this case the navigation pane is obviously already in view due to the fact that the OP was noticing, visually, the "seeming" absence of the table, which is why I offered code that would close then force open the navigation pane. At least, that is what I have been able to use that code for, in the past.

Stuart or Jim, what is the "required" expression with which to anchor the *RefreshDatabaseWindow *method? The link says it is required, then the code sample doesn't even specify one!

On Tue, Jan 9, 2018 at 9:29 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Excellent code tool.
>
> Jim
>
> ----- Original Message -----
> From: "stuart" <stuart at lexacorp.com.pg>
> To: "Access Developers discussion and problem solving" < 
> accessd at databaseadvisors.com>
> Sent: Tuesday, January 9, 2018 5:13:02 PM
> Subject: Re: [AccessD] Query runs and doesn't run
>
> Yep, that's the most likely situation.  The query is working, butt the 
> table is not being displayed.
>
>
> RefreshDatabaseWindow may be the solution:
>
> https://msdn.microsoft.com/en-us/library/office/aa221557(v=office.11).
> aspx
>
> On 10 Jan 2018 at 0:59, McGillivray, Don wrote:
>
> > So it is.
> >
> > I tested this and got a result similar to Chester's.  However, when 
> > I loop through all the table defs in the DB, the new table is there.
> > (That would explain why his deletion of the table doesn't fail.)  
> > So, it's not that the table isn't there, but just that it doesn't 
> > appear in the object pane.
> >
> > There's probably a way to make it appear using code, but I found 
> > that using F5 in the DB container will do it.  Closing and reopening 
> > the DB seems to work as well.
> >
> > I seem to remember having to refresh the table def collection when 
> > creating a table from scratch in code in order to see the result in 
> > the object pane.  I tried db.TableDefs.Refresh, but that didn't do it.
> >
> > Maybe somebody else knows the secret sauce.
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of Stuart McLachlan Sent: Tuesday, January 09, 2018 4:19 PM 
> > To: Access Developers discussion and problem solving 
> > <accessd at databaseadvisors.com> Subject: Re: [AccessD] Query runs and 
> > doesn't run
> >
> > It's a "SELECT...   INTO [tbl Initial Pattern Factors]... "   that is
> > failing.  That is  an action query.
> >
> >
> > On 9 Jan 2018 at 23:17, McGillivray, Don wrote:
> >
> > > I'm pretty sure that "dbs.execute" requires an action query 
> > > (INSERT, UPDATE, DELETE).  Won't work with a SELECT.
> > >
> > > If you think about it, that makes sense; what do you expect to 
> > > happen with the results of that SELECT in your code?
> > >
> > > -----Original Message-----
> > > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > > Behalf Of Paul Hartland via AccessD Sent: Tuesday, January 09, 
> > > 2018
> > > 2:34 PM To: Access List <accessd at databaseadvisors.com> Cc: Paul 
> > > Hartland <paul.hartland at googlemail.com> Subject: Re: [AccessD] 
> > > Query runs and doesn't run
> > >
> > > Have you tried running it without the where clause, then if it 
> > > works try again with one of the where clauses, then the 
> > > other....also when I used to query dates in access I would use 
> > > something like the below, formatting the date
> > >
> > > Where MyDate =#" &  format("06/01/1935", "dd/mm/yyyy")& "#"
> > >
> > > On 9 Jan 2018 22:23, "Kaup, Chester" 
> > > <Chester_Kaup at kindermorgan.com>
> > > wrote:
> > >
> > > > Well I did find one syntax error. Need two pairs of double 
> > > > quotes around PATT. Should have been ""PATT"". Still getting no 
> > > > results though.
> > > >
> > > > From: Kaup, Chester
> > > > Sent: Tuesday, January 09, 2018 3:50 PM
> > > > To: 'Access Developers discussion and problem solving'
> > > > Subject: Query runs and doesn't run
> > > >
> > > > I have the following query that run just fine from the query 
> > > > grid
> > > >
> > > > SELECT Allocations.ChildPID, Allocations.ChildPTYPE, 
> > > > Allocations.PID, Allocations.PTYPE, Allocations.Date, 
> > > > Allocations.Factor_1, Allocations.Factor_2 INTO [tbl Initial 
> > > > Pattern Factors] FROM Allocations WHERE
> > > > (((Allocations.PTYPE)="PATT") AND 
> > > > ((Allocations.Date)=#6/1/1935#));
> > > >
> > > > I have the same query in VBA but it generates no output. I am at 
> > > > a loss as to what is going on. Here is the VBA part.
> > > >
> > > > Dim dbs As DAO.Database
> > > > Set dbs = CurrentDb
> > > >
> > > > strSQL = "SELECT Allocations.ChildPID, Allocations.ChildPTYPE, 
> > > > Allocations.PID, Allocations.PTYPE, Allocations.Date, 
> > > > Allocations.Factor_1, Allocations.Factor_2 INTO [tbl Initial 
> > > > Pattern Factors] " & _
> > > >                   "FROM Allocations " & _
> > > >                   "WHERE Allocations.PTYPE='PATT' AND 
> > > > Allocations.Date=#6/1/1935#;"
> > > >
> > > >         dbs.Execute strSQL
> > > >
> > > >
> > > > What is even stranger is the following statement does not fail 
> > > > although I do not see the table in the list of tables
> > > >
> > > > DoCmd.DeleteObject acTable, "tbl Initial Pattern Factors"
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com