[AccessD] Query runs and doesn't run

McGillivray, Don DMcGillivray at ctc.ca.gov
Tue Jan 9 18:59:31 CST 2018


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



More information about the AccessD mailing list