[AccessD] Query runs and doesn't run

Wed Jan 10 14:15:53 CST 2018

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
>