[AccessD] Query runs and doesn't run

[Jim Lawrence]

It was luck we all had "auto-correct" on. ;-)

Jim

----- Original Message -----
From: "Bill Benson" <bensonforums at gmail.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Tuesday, January 9, 2018 5:13:51 PM
Subject: Re: [AccessD] Query runs and doesn't run

Vba, not cba.

On Jan 9, 2018 8:13 PM, "Bill Benson" <bensonforums at gmail.com> wrote:

> There ate cba command to toggle (hide then show)  the object pane.
> RunCommand(acCmdWindowHide)
> DoCmdSelectObject(acTable, ,True)
>
> On Jan 9, 2018 8:00 PM, "McGillivray, Don" <DMcGillivray at ctc.ca.gov>
> 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