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

[colmkav]

What is the best way to approach my problem:
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01.
How best is it to do this? Currently the queries run fine on
themselves but when I try to use VBA to run the queries and store the
results in a
new table using “SELECT * INTO…” I get overflow errors.

What’s the neatest/best way to do this?

Colm

[Deanna Earley]

Post by colmkav
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01.
How best is it to do this? Currently the queries run fine on
themselves but when I try to use VBA to run the queries and store the
results in a
new table using “SELECT * INTO…” I get overflow errors.
What’s the neatest/best way to do this?

Using a SELECT .. INTO .. query as you've found (but it varies for each
database).
As for the errors, you've made an error in the query.

As my crystal ball isn't working in this weather you'll need to show us
the full code/sql that is failing and the full error message

[colmkav]

Post by Deanna Earley

Post by colmkav
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01.
How best is it to do this? Currently the queries run fine on
themselves  but when I try to use VBA to run the queries and store the
results in a
new table using “SELECT * INTO…”  I get overflow errors.
What’s the neatest/best way to do this?

Using a SELECT .. INTO .. query as you've found (but it varies for each
database).
As for the errors, you've made an error in the query.
As my crystal ball isn't working in this weather you'll need to show us
the full code/sql that is failing and the full error message
--
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.)

Ok. Well as my query statements are quite complicated I was trying to
summarise a bit but I see your point.

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.

[Deanna Earley]

Post by colmkav
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"
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.

[colmkav]

Post by Deanna Earley

Post by colmkav
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"
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.
--
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?

[Henning]

Post by Deanna Earley

Post by colmkav
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"
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.
--
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?

I don't use Access, but try divide the query into CREATE and SELECT * INTO.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

Not one (noone is already taken :)) can help you from your description of
what you're doing. Post the complete query.

/Henning

[ralph]

On Fri, 8 Jun 2012 08:03:09 -0700 (PDT), colmkav

Post by colmkav

Post by Deanna Earley

Post by colmkav
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"
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.
--
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

[colmkav]

Post by ralph
On Fri, 8 Jun 2012 08:03:09 -0700 (PDT), colmkav

Post by colmkav

Post by Deanna Earley

Post by colmkav
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"
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.
--
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- Hide quoted text -
- Show quoted text -

Here are the full list of queries:

AppendtblColmRule01
INSERT INTO tblColmRule01
SELECT *
FROM ColmRule01;

ColmRule01
SELECT [Colm Report 1 - Flow patterns - 2].*
FROM [Colm Report 1 - Flow patterns - 2]
WHERE (((IIf([AvgCreated transactions (number)]<>0,
(CLng([SumOfSumOfCreated transactions (number)])-CLng([AvgCreated
transactions (number)]))*100/([AvgCreated transactions (number)]),
0))>20 Or (IIf([AvgCreated transactions (number)]<>0,
(CLng([SumOfSumOfCreated transactions (number)])-CLng([AvgCreated
transactions (number)]))*100/([AvgCreated transactions (number)]),
0))<-20) AND (([Colm Report 1 - Flow patterns - 2].[SumOfSumOfCreated
transactions (number)])<10000));


Colm Report 1 - Flow Patterns - 2
SELECT [Colm Report 1 - flow patterns - 1].[Client-id], [Merchant
Info].[Special monitoring], [Colm Report 1 - flow patterns - 1].
[Client-name], [Merchant Info].[Business Segment], [Merchant Info].
[Top Merchant], [Merchant Info].[Risk Level Credit], [Merchant Info].
[Credit Manager], [Merchant Info].[Risk Level Fraud], [Merchant Info].
[Risk Manager], [Merchant Info].[Forecast daily expected trx], [Colm
Averages - 2].[AvgCreated transactions (number)], [Merchant Info].[Low
threshold (trx)], Sum([Colm Report 1 - flow patterns - 1].
[SumOfCreated transactions (number)]) AS [SumOfSumOfCreated
transactions (number)], [Merchant Info].[high threshold (trx)],
[SumOfTrx EUR amount]/[SumOfSumOfCreated transactions (number)] AS
[Avg trx amount], [Merchant Info].[low threshold (value)], Sum([Colm
Report 1 - flow patterns - 1].[Trx EUR amount]) AS [SumOfTrx EUR
amount], [Colm Averages - 2].[Avg Trx EUR amount], [Merchant Info].
[high threshold (value)], [Colm Averages - 2].[AvgCreated refunds
(number)], Sum([Colm Report 1 - flow patterns - 1].[SumOfCreated
refunds (number)]) AS [SumOfSumOfCreated refunds (number)], Sum([Colm
Report 1 - flow patterns - 1].[Refund EUR amount]) AS [SumOfRefund EUR
amount], [Colm Report 1 - flow patterns - 1].[Processing date],
[Merchant Info].Remark
FROM ([Merchant Info] INNER JOIN [Colm Report 1 - flow patterns - 1]
ON [Merchant Info].[Client-id] = [Colm Report 1 - flow patterns - 1].
[Client-id]) INNER JOIN [Colm Averages - 2] ON [Merchant Info].[Client-
id] = [Colm Averages - 2].[Client-id]
GROUP BY [Colm Report 1 - flow patterns - 1].[Client-id], [Merchant
Info].[Special monitoring], [Colm Report 1 - flow patterns - 1].
[Client-name], [Merchant Info].[Business Segment], [Merchant Info].
[Top Merchant], [Merchant Info].[Risk Level Credit], [Merchant Info].
[Credit Manager], [Merchant Info].[Risk Level Fraud], [Merchant Info].
[Risk Manager], [Merchant Info].[Forecast daily expected trx], [Colm
Averages - 2].[AvgCreated transactions (number)], [Merchant Info].[Low
threshold (trx)], [Merchant Info].[high threshold (trx)], [Merchant
Info].[low threshold (value)], [Colm Averages - 2].[Avg Trx EUR
amount], [Merchant Info].[high threshold (value)], [Colm Averages - 2].
[AvgCreated refunds (number)], [Colm Report 1 - flow patterns - 1].
[Processing date], [Merchant Info].Remark;

Colm Report 1 - Flow Patterns - 1
SELECT [MIND Accounts].[Client-id], [MIND Accounts].[Client-name],
Data.Currency, [Currency conversion].[Conversion rate], Sum(Data.
[Created transactions (number)]) AS [SumOfCreated transactions
(number)], Sum(Data.[Created transactions (value)]) AS [SumOfCreated
transactions (value)], Sum([Created transactions (value)]*[Conversion
Rate]) AS [Trx EUR amount], Sum(Data.[Created refunds (number)]) AS
[SumOfCreated refunds (number)], Sum(Data.[Created refunds (value)])
AS [SumOfCreated refunds (value)], Sum([Created refunds
(value)]*[Conversion Rate]) AS [Refund EUR amount], Data.[Processing
date]
FROM [MIND Accounts] INNER JOIN (Data INNER JOIN [Currency conversion]
ON Data.Currency = [Currency conversion].Currency) ON [MIND Accounts].
[Account-id] = Data.[Merchant CID]
GROUP BY [MIND Accounts].[Client-id], [MIND Accounts].[Client-name],
Data.Currency, [Currency conversion].[Conversion rate], Data.
[Processing date]
HAVING (((Data.[Processing date])=Date()-1));

[colmkav]

Post by colmkav

Post by ralph
On Fri, 8 Jun 2012 08:03:09 -0700 (PDT), colmkav

Post by colmkav

Post by Deanna Earley

Post by colmkav
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"
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.
--
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- Hide quoted text -
- Show quoted text -

AppendtblColmRule01
INSERT INTO tblColmRule01
SELECT *
FROM ColmRule01;
ColmRule01
SELECT [Colm Report 1 - Flow patterns - 2].*
FROM [Colm Report 1 - Flow patterns - 2]
WHERE (((IIf([AvgCreated transactions (number)]<>0,
(CLng([SumOfSumOfCreated transactions (number)])-CLng([AvgCreated
transactions (number)]))*100/([AvgCreated transactions (number)]),
0))>20 Or (IIf([AvgCreated transactions (number)]<>0,
(CLng([SumOfSumOfCreated transactions (number)])-CLng([AvgCreated
transactions (number)]))*100/([AvgCreated transactions (number)]),
0))<-20) AND (([Colm Report 1 - Flow patterns - 2].[SumOfSumOfCreated
transactions (number)])<10000));
Colm Report 1 - Flow Patterns - 2
SELECT [Colm Report 1 - flow patterns - 1].[Client-id], [Merchant
Info].[Special monitoring], [Colm Report 1 - flow patterns - 1].
[Client-name], [Merchant Info].[Business Segment], [Merchant Info].
[Top Merchant], [Merchant Info].[Risk Level Credit], [Merchant Info].
[Credit Manager], [Merchant Info].[Risk Level Fraud], [Merchant Info].
[Risk Manager], [Merchant Info].[Forecast daily expected trx], [Colm
Averages - 2].[AvgCreated transactions (number)], [Merchant Info].[Low
threshold (trx)], Sum([Colm Report 1 - flow patterns - 1].
[SumOfCreated transactions (number)]) AS [SumOfSumOfCreated
transactions (number)], [Merchant Info].[high threshold (trx)],
[SumOfTrx EUR amount]/[SumOfSumOfCreated transactions (number)] AS
[Avg trx amount], [Merchant Info].[low threshold (value)], Sum([Colm
Report 1 - flow patterns - 1].[Trx EUR amount]) AS [SumOfTrx EUR
amount], [Colm Averages - 2].[Avg Trx EUR amount], [Merchant Info].
[high threshold (value)], [Colm Averages - 2].[AvgCreated refunds
(number)], Sum([Colm Report 1 - flow patterns - 1].[SumOfCreated
refunds (number)]) AS [SumOfSumOfCreated refunds (number)], Sum([Colm
Report 1 - flow patterns - 1].[Refund EUR amount]) AS [SumOfRefund EUR
amount], [Colm Report 1 - flow patterns - 1].[Processing date],
[Merchant Info].Remark
FROM ([Merchant Info] INNER JOIN [Colm Report 1 - flow patterns - 1]
ON [Merchant Info].[Client-id] = [Colm Report 1 - flow patterns - 1].
[Client-id]) INNER JOIN [Colm Averages - 2] ON [Merchant Info].[Client-
id] = [Colm Averages - 2].[Client-id]
GROUP BY [Colm Report 1 - flow patterns - 1].[Client-id], [Merchant
Info].[Special monitoring], [Colm Report 1 - flow patterns - 1].
[Client-name], [Merchant Info].[Business Segment], [Merchant Info].
[Top Merchant], [Merchant Info].[Risk Level Credit], [Merchant Info].
[Credit Manager], [Merchant Info].[Risk Level Fraud], [Merchant Info].
[Risk Manager], [Merchant Info].[Forecast daily expected trx], [Colm
Averages - 2].[AvgCreated transactions (number)], [Merchant Info].[Low
threshold (trx)], [Merchant Info].[high threshold (trx)], [Merchant
Info].[low threshold (value)], [Colm Averages - 2].[Avg Trx EUR
amount], [Merchant Info].[high threshold (value)], [Colm Averages - 2].
[AvgCreated refunds (number)], [Colm Report 1 - flow patterns - 1].
[Processing date], [Merchant Info].Remark;
Colm Report 1 - Flow Patterns - 1
SELECT [MIND Accounts].[Client-id], [MIND Accounts].[Client-name],
Data.Currency, [Currency conversion].[Conversion rate], Sum(Data.
[Created transactions (number)]) AS [SumOfCreated transactions
(number)], Sum(Data.[Created transactions (value)]) AS [SumOfCreated
transactions (value)], Sum([Created transactions (value)]*[Conversion
Rate]) AS [Trx EUR amount], Sum(Data.[Created refunds (number)]) AS
[SumOfCreated refunds (number)], Sum(Data.[Created refunds (value)])
AS [SumOfCreated refunds (value)], Sum([Created refunds
(value)]*[Conversion Rate]) AS [Refund EUR amount], Data.[Processing
date]
FROM [MIND Accounts] INNER JOIN (Data INNER JOIN [Currency conversion]
ON Data.Currency = [Currency conversion].Currency) ON [MIND Accounts].
[Account-id] = Data.[Merchant CID]
GROUP BY [MIND Accounts].[Client-id], [MIND Accounts].[Client-name],
Data.Currency, [Currency conversion].[Conversion rate], Data.
[Processing date]
HAVING (((Data.[Processing date])=Date()-1));- Hide quoted text -
- Show quoted text -

The table definition of the table I am trying to append to is:

tblColmRule01
Client-id: Number (long integer)
Special monitoring: Yes/No
Client-name: Text
Business Segment: Text
Top Merchant: Yes/No
Risk Level Credit: Text
Credit Manager: Text
Risk Level Fraud: Text
Risk Manager: text
Forecast daily expected trx: Number (long integer)
AvgCreated transactions (number): (double)
Low threshold (trx): (Long integer)
SumOfSumOfCreated transactions (number): Number (double)
high threshold (trx): Number (Long Integer)
Avg trx amount: Number (double)
low threshold (value): Number (Long Integer)
SumOfTrx EUR amount: Number (double)
Avg Trx EUR amount: Number (double)
high threshold (value): Number (Long Integer)
AvgCreated refunds (number): Number (double)
SumOfSumOfCreated refunds : Number (double)
SumOfRefund EUR amount: Number (double)
Processing date: Date/Time
Remark: Memo

[Henning]

Post by Deanna Earley

Post by colmkav
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01.
How best is it to do this? Currently the queries run fine on
themselves but when I try to use VBA to run the queries and store the
results in a
new table using “SELECT * INTO…” I get overflow errors.
What’s the neatest/best way to do this?

Using a SELECT .. INTO .. query as you've found (but it varies for each
database).
As for the errors, you've made an error in the query.
As my crystal ball isn't working in this weather you'll need to show us
the full code/sql that is failing and the full error message
--
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.)

Ok. Well as my query statements are quite complicated I was trying to
summarise a bit but I see your point.

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.

See if this helps.
http://office.microsoft.com/en-us/access-help/create-a-make-table-query-HA010108505.aspx

/Henning

[GS]

Given that you're working with MS Access *and* VBA.., would it not
server you better to ask/post your issue in an access.programming
group?