[AccessD] Query runs and doesn't run

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Jan 10 08:55:16 CST 2018


Did some testing and a compact and repair made the table visible. Tried your suggestion (CmdSelectObject(acTable, ,True) and It worked when I specified the table name. Thanks everyone for your assistance.

I am now thinking however of changing ny method of deleting and recreating table to deleting the table contents and then appending to the tables. Fear of database bloat.


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Tuesday, January 09, 2018 7:13 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]


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




More information about the AccessD mailing list