Having trouble with some sql

  • Thread starter Thread starter Sandy Hayman
  • Start date Start date
S

Sandy Hayman

Hi
I am having a bit of trouble with a couple of Pass Through queries I'm
trying to run.

Our back end is an Ingres database and the sql (via a querydef) I am trying
to run is:

"INSERT INTO cdbdba.ae_series
(ahecc,anzsic,bec,created_by,destination,id,measure,origin,periodicity,series_type,sitc,unit)
" & _
"SELECT ahecc,anzsic,bec,ae_series.created_by,index_details.id AS
newDestination, " & _
"ae_series.id,measure,origin,periodicity,series_type,sitc,unit " & _
"FROM ae_series INNER JOIN index_details ON ae_series.destination =
index_details.index_item_detail"

and

"INSERT INTO cdbdba.ae_commodity_data SELECT * from ae_commodity_data;"

I keep getting an [Intersolve-ODBC] error saying that the correct syntax is
"PREPARE INTO table_name ...."

The queries run ok in access so I figure the problem is in the sql when it
comes to writing directly to ingres. I checked their documentation but
couldn't find the problem. Any clues?

Thanks in advance
Sandy
 
Sandy said:
Hi
I am having a bit of trouble with a couple of Pass Through queries I'm
trying to run.

Our back end is an Ingres database and the sql (via a querydef) I am
Click to expand...

Pass-through queries are sent directly to the remote database. They need
to be in the syntax that that database will understand. I would post to
an Ingres forum or newsgroup for help with this.
trying to run is:

"INSERT INTO cdbdba.ae_series
(ahecc,anzsic,bec,created_by,destination,id,measure,origin,periodicity,s
eries_type,sitc,unit)
" & _
"SELECT
ahecc,anzsic,bec,ae_series.created_by,index_details.id AS
newDestination, " & _

"ae_series.id,measure,origin,periodicity,series_type,sitc,unit " & _
"FROM ae_series INNER JOIN index_details ON ae_series.destination =
index_details.index_item_detail"
Click to expand...

Hint, the people trying to help you will probably be more comfortable
reading the actual sql statement that results from this vba code. Use
debug.print to write it to the Immediate window and copy it from there.
 
Hi Roy
I have done some more research and testing and have come to the following
conclustion -

The sql is ok but I'm starting to think that I can't select records from a
local table to append into an Ingres table. When I change the sql to actual
values rather than selecting them the sql works. As soon as I try to select
from my local table to append to the ingres table, it doesn't work. No
errors - just nothing.

I think I'll repost to see if this is an Access limitation.

Thanks
Sandy

Roy Hann said:
Sandy said:
Hi
I am having a bit of trouble with a couple of Pass Through queries I'm
trying to run.

Our back end is an Ingres database and the sql (via a querydef) I am
trying
to run is:

"INSERT INTO cdbdba.ae_series
(ahecc,anzsic,bec,created_by,destination,id,measure,origin,periodicity,series_type,sitc,unit)
" & _
"SELECT ahecc,anzsic,bec,ae_series.created_by,index_details.id AS
newDestination, " & _
"ae_series.id,measure,origin,periodicity,series_type,sitc,unit "
& _
"FROM ae_series INNER JOIN index_details ON ae_series.destination
=
index_details.index_item_detail"

and

"INSERT INTO cdbdba.ae_commodity_data SELECT * from ae_commodity_data;"

I keep getting an [Intersolve-ODBC] error saying that the correct syntax
is
"PREPARE INTO table_name ...."

The queries run ok in access so I figure the problem is in the sql when
it
comes to writing directly to ingres. I checked their documentation but
couldn't find the problem. Any clues?
Click to expand...

The SQL syntax you are using for the first INSERT is perfectly
acceptable to any currently supported versions of Ingres (and a lot of
obsolete versions too). However I notice you are using the Intersolve
driver which makes me wonder if you are using a very old version of
Ingres.

What does SELECT DBMSINFO("_version") say?

Also, are you certain you are connecting as a user who has access to the
cdbdba schema?

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.
Click to expand...
 
Since you are using a pass-through query, Ingres has no way of knowing
about any tables in a Jet (Access) database. In MS SQL server you can
"link" to Access databases and tables. There may be a similar ability in
Ingres.

Otherwise, you cannot use a pass-through query, but should be able to
use a query to a linked Ingres table.

"INSERT INTO cdbdba.ae_series" & _
" (ahecc, anzsic, bec, created_by, destination, id, measure" & _
", origin, periodicity, series_type, sitc, unit) " & _
"SELECT ahecc, anzsic, bec, ae_series.created_by" & _
", index_details.id AS newDestination, ae_series.id" & _
", measure ,origin ,periodicity, series_type, sitc, unit " & _
"FROM ae_series INNER JOIN index_details " & _
"ON ae_series.destination = index_details.index_item_detail"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Sandy said:
Hi Roy
I have done some more research and testing and have come to the following
conclustion -

The sql is ok but I'm starting to think that I can't select records from a
local table to append into an Ingres table. When I change the sql to actual
values rather than selecting them the sql works. As soon as I try to select
from my local table to append to the ingres table, it doesn't work. No
errors - just nothing.

I think I'll repost to see if this is an Access limitation.

Thanks
Sandy

Roy Hann said:
Sandy said:
Hi
I am having a bit of trouble with a couple of Pass Through queries I'm
trying to run.

Our back end is an Ingres database and the sql (via a querydef) I am
trying
to run is:

"INSERT INTO cdbdba.ae_series
(ahecc,anzsic,bec,created_by,destination,id,measure,origin,periodicity,series_type,sitc,unit)
" & _
"SELECT ahecc,anzsic,bec,ae_series.created_by,index_details.id AS
newDestination, " & _
"ae_series.id,measure,origin,periodicity,series_type,sitc,unit "
& _
"FROM ae_series INNER JOIN index_details ON ae_series.destination
=
index_details.index_item_detail"

and

"INSERT INTO cdbdba.ae_commodity_data SELECT * from ae_commodity_data;"

I keep getting an [Intersolve-ODBC] error saying that the correct syntax
is
"PREPARE INTO table_name ...."

The queries run ok in access so I figure the problem is in the sql when
it
comes to writing directly to ingres. I checked their documentation but
couldn't find the problem. Any clues?
Click to expand...
The SQL syntax you are using for the first INSERT is perfectly
acceptable to any currently supported versions of Ingres (and a lot of
obsolete versions too). However I notice you are using the Intersolve
driver which makes me wonder if you are using a very old version of
Ingres.

What does SELECT DBMSINFO("_version") say?

Also, are you certain you are connecting as a user who has access to the
cdbdba schema?

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.
Click to expand...
Click to expand...
 
Back
Top