Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1208

Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables

Show all headers | View raw

On Jun 8, 3:40 pm, Deanna Earley <dee.ear...@icode.co.uk> wrote:
> > My problem isnt with the query itself. If I run the query in Access
> > without returning it to a new table it runs fine. However when I do
> > the "SELECT * INTO MYNEWTABLE from MYQUERY"
>
> > I get the following error:
>
> > Microsoft Office Access can't add all the records in the update or
> > append query.
> > It set 19 field(s) to Null due to a type conversion failure.
> > A type conversion failure is caused when the data in one or more
> > fields doesnt match the DataType or FieldSize property in the
> > destination table. For example, leaving blank fiels in a Yes/No field
> > or entering text in a numeric field will cause this error.
> > Do you want to ignore the errors and run the update or append query
> > anyway?
> > To ignore the error(s) and run the query, click Yes.
>
> Soo... My psychic powers at work here, The output of the query and the
> table you trying to put it into don't match in the fields and data types.
>
> Make them the same or clean up the source data and it'll all work.
>
> --
> Deanna Earley (dee.ear...@icode.co.uk)
> i-Catcher Development Teamhttp://www.icode.co.uk/icatcher/
>
> iCode Systems
>
> (Replies direct to my email address will be ignored.
> Please reply to the group.)- Hide quoted text -
>
> - Show quoted text -

But the new table is only being created via this new query so the
sizes/types will be default to whatever Access chooses. Are the field
types not simply defaulted to be the same as that which are used to
display the results of the query? I dont really understand why Access
can work out the field sizes/types when it returns the results of the
query in its display but cant work out the right field sizes/types
when you tell it to put the results in a new table.

I have since tried using INSERT and set all the types/fields to an
already made table but still getting a similar error. If I make them
all the same as that of the original source tables should this then
work?

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables colmkav <colmjkav@yahoo.co.uk> - 2012-06-08 01:01 -0700
  Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables Deanna Earley <dee.earley@icode.co.uk> - 2012-06-08 09:19 +0100
    Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables colmkav <colmjkav@yahoo.co.uk> - 2012-06-08 05:52 -0700
      Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables Deanna Earley <dee.earley@icode.co.uk> - 2012-06-08 14:40 +0100
        Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables colmkav <colmjkav@yahoo.co.uk> - 2012-06-08 08:03 -0700
          Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables "Henning" <computer_hero@coldmail.com> - 2012-06-09 13:18 +0200
          Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables ralph <nt_consulting64@yahoo.com> - 2012-06-09 14:46 -0500
            Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables colmkav <colmjkav@yahoo.co.uk> - 2012-06-11 01:17 -0700
              Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables colmkav <colmjkav@yahoo.co.uk> - 2012-06-11 01:34 -0700
      Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables "Henning" <computer_hero@coldmail.com> - 2012-06-15 13:26 +0200
  Re: REVISED QUESTION: Best way to execute Access SQL queries in VBA and return results in new saved tables GS <gs@somewhere.net> - 2012-06-08 13:01 -0400

csiph-web