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

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

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 Fri, 8 Jun 2012 08:03:09 -0700 (PDT), colmkav
<colmjkav@yahoo.co.uk> wrote:

>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?
>

Need to use a MakeTable Query. Info in MS Access help.

The "mystery" becomes clearer when you appreciate that the results of
a query you are seeing in MS Access is NOT a table, - it is a 'view'.
It has no storage. The data (and thus also attributes) of the various
fields is still in the original table/s.

-ralph

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