[AccessD] Query runs and doesn't run

Bill Benson bensonforums at gmail.com
Tue Jan 9 18:35:51 CST 2018


Is it possible that the table already exists? Is it possible that the table
is bound to any queries reports or forms that might be open? Have you tried
deleting the table first and refreshing the tabledefs collection as a
prerequisite?

On Jan 9, 2018 7:20 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg> wrote:

> 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