ODBC link failed

[Peter Adema]

I am using Access 2000 and have an ODBC driver in place to get data from an
AS/400.
Whe retreiving data (from a very big table) via a query I fill in a from
till range to determain invoiced transactions.
Criteria: >=20060300 and <= 20060400.
This works perfectly.

I made a selection on a form to be able to select the different periods.
Criteria: >=[Forms]![HOOFDMENU]![Start] And <=[Forms]![HOOFDMENU]![end]
When this is the criteria I get an error ODBC link failed.
Also when I want to create a pop-up to fill in the ranges myself, the ODBC
fails.
Criteria: >=[From which period?] And <=[Till which period?]
Is there another way to retreive a range of data without having to adapting
the query?
Thanks.

 

[Duane Hookom]

Is your query a Pass-through query? If not it should work if you enter
values in the correct format. If it is a p-t then it would have been an
important bit of information that should have been included in your original
post. Your original post should have contained the SQL view of your query.

 

[Peter Adema]

Duane,

Please find enclosed the SQL view of the query.
It is a "create tabel" query.

SELECT WI61BPDNL_ITDD19L1.DLKD19, WI61BPDNL_ITDD19L1.FIRM19,
WI61BPDNL_ITDD19L1.DOSS19, WI61BPDNL_ITDD19L1.DOSL19,
WI61BPDNL_ITDD19L1.FADT19, WI61BPDNL_ITDD19L1.BILJ19,
WI61BPDNL_ITDD19L1.PCFI19, WI61BPDNL_ITDD19L1.AGVK19,
WI61BPDNL_ITDD19L1.PNCR19, WI61BPDNL_ITDD19L1.CRSN19 INTO tickets
FROM WI61BPDNL_ITDD19L1
WHERE (((WI61BPDNL_ITDD19L1.DLKD19)<>"4") AND
((WI61BPDNL_ITDD19L1.FIRM19)="WL") AND
((WI61BPDNL_ITDD19L1.FADT19)>[Forms]![HOOFDMENU]![Start] And
(WI61BPDNL_ITDD19L1.FADT19)<[Forms]![HOOFDMENU]![end]) AND
((WI61BPDNL_ITDD19L1.BILJ19)<>""));

Is your query a Pass-through query? If not it should work if you enter
values in the correct format. If it is a p-t then it would have been an
important bit of information that should have been included in your original
post. Your original post should have contained the SQL view of your query.

--
Duane Hookom
MS Access MVP

I am using Access 2000 and have an ODBC driver in place to get data from an
AS/400.
Whe retreiving data (from a very big table) via a query I fill in a from
till range to determain invoiced transactions.
Criteria: >=20060300 and <= 20060400.
This works perfectly.

I made a selection on a form to be able to select the different periods.
Criteria: >=[Forms]![HOOFDMENU]![Start] And <=[Forms]![HOOFDMENU]![end]
When this is the criteria I get an error ODBC link failed.
Also when I want to create a pop-up to fill in the ranges myself, the ODBC
fails.
Criteria: >=[From which period?] And <=[Till which period?]
Is there another way to retreive a range of data without having to
adapting
the query?
Thanks.

 

[Duane Hookom]

You might try enter the data types of your parameters. This is done in
Query->Parameters. When the table is created, is FADT19 text or numeric?

--
Duane Hookom
MS Access MVP

Duane,

Please find enclosed the SQL view of the query.
It is a "create tabel" query.

SELECT WI61BPDNL_ITDD19L1.DLKD19, WI61BPDNL_ITDD19L1.FIRM19,
WI61BPDNL_ITDD19L1.DOSS19, WI61BPDNL_ITDD19L1.DOSL19,
WI61BPDNL_ITDD19L1.FADT19, WI61BPDNL_ITDD19L1.BILJ19,
WI61BPDNL_ITDD19L1.PCFI19, WI61BPDNL_ITDD19L1.AGVK19,
WI61BPDNL_ITDD19L1.PNCR19, WI61BPDNL_ITDD19L1.CRSN19 INTO tickets
FROM WI61BPDNL_ITDD19L1
WHERE (((WI61BPDNL_ITDD19L1.DLKD19)<>"4") AND
((WI61BPDNL_ITDD19L1.FIRM19)="WL") AND
((WI61BPDNL_ITDD19L1.FADT19)>[Forms]![HOOFDMENU]![Start] And
(WI61BPDNL_ITDD19L1.FADT19)<[Forms]![HOOFDMENU]![end]) AND
((WI61BPDNL_ITDD19L1.BILJ19)<>""));

Is your query a Pass-through query? If not it should work if you enter
values in the correct format. If it is a p-t then it would have been an
important bit of information that should have been included in your
original
post. Your original post should have contained the SQL view of your
query.

--
Duane Hookom
MS Access MVP

I am using Access 2000 and have an ODBC driver in place to get data from
an
AS/400.
Whe retreiving data (from a very big table) via a query I fill in a
from
till range to determain invoiced transactions.
Criteria: >=20060300 and <= 20060400.
This works perfectly.

I made a selection on a form to be able to select the different
periods.
Criteria: >=[Forms]![HOOFDMENU]![Start] And <=[Forms]![HOOFDMENU]![end]
When this is the criteria I get an error ODBC link failed.
Also when I want to create a pop-up to fill in the ranges myself, the
ODBC
fails.
Criteria: >=[From which period?] And <=[Till which period?]
Is there another way to retreive a range of data without having to
adapting
the query?
Thanks.

 

[Peter Adema]

Thanks for your suggestion but it does not have any effect.

But I now know that the problem is the estimated query time. It exceeds the
60 seconds.
What I do not understand is that it works within the 60 seconds if I define
the periods inside the query manually, but that it isn't working when
selecting from another tabel or via a prompt.

You might try enter the data types of your parameters. This is done in
Query->Parameters. When the table is created, is FADT19 text or numeric?

--
Duane Hookom
MS Access MVP

Duane,

Please find enclosed the SQL view of the query.
It is a "create tabel" query.

SELECT WI61BPDNL_ITDD19L1.DLKD19, WI61BPDNL_ITDD19L1.FIRM19,
WI61BPDNL_ITDD19L1.DOSS19, WI61BPDNL_ITDD19L1.DOSL19,
WI61BPDNL_ITDD19L1.FADT19, WI61BPDNL_ITDD19L1.BILJ19,
WI61BPDNL_ITDD19L1.PCFI19, WI61BPDNL_ITDD19L1.AGVK19,
WI61BPDNL_ITDD19L1.PNCR19, WI61BPDNL_ITDD19L1.CRSN19 INTO tickets
FROM WI61BPDNL_ITDD19L1
WHERE (((WI61BPDNL_ITDD19L1.DLKD19)<>"4") AND
((WI61BPDNL_ITDD19L1.FIRM19)="WL") AND
((WI61BPDNL_ITDD19L1.FADT19)>[Forms]![HOOFDMENU]![Start] And
(WI61BPDNL_ITDD19L1.FADT19)<[Forms]![HOOFDMENU]![end]) AND
((WI61BPDNL_ITDD19L1.BILJ19)<>""));

Is your query a Pass-through query? If not it should work if you enter
values in the correct format. If it is a p-t then it would have been an
important bit of information that should have been included in your
original
post. Your original post should have contained the SQL view of your
query.

--
Duane Hookom
MS Access MVP

I am using Access 2000 and have an ODBC driver in place to get data from
an
AS/400.
Whe retreiving data (from a very big table) via a query I fill in a
from
till range to determain invoiced transactions.
Criteria: >=20060300 and <= 20060400.
This works perfectly.

I made a selection on a form to be able to select the different
periods.
Criteria: >=[Forms]![HOOFDMENU]![Start] And <=[Forms]![HOOFDMENU]![end]
When this is the criteria I get an error ODBC link failed.
Also when I want to create a pop-up to fill in the ranges myself, the
ODBC
fails.
Criteria: >=[From which period?] And <=[Till which period?]
Is there another way to retreive a range of data without having to
adapting
the query?
Thanks.

 

[Duane Hookom]

Can you use a pass-through query for the Select statement rather than a
query against a linked table? In either case, you can modify the SQL of your
query with a little DAO code:

Dim strSQL as String
strSQL = "SELECT DLKD19, FIRM19, DOSS19, DOSL19, " & _

"FADT19, BILJ19, PCFI19, AGVK19, PNCR19, CRSN19 " & _

"INTO tickets " & _
"FROM WI61BPDNL_ITDD19L1 " & _
"WHERE DLKD19<>'4' AND FIRM19='WL' AND FADT19 > " & _

[Forms]![HOOFDMENU]![Start] & " And FADT19< " & _

[Forms]![HOOFDMENU]![end] & " AND BILJ19<>'';"

CurrentDb.QueryDefs("YourQueryName").SQL = strSQL


--
Duane Hookom
MS Access MVP

Thanks for your suggestion but it does not have any effect.

But I now know that the problem is the estimated query time. It exceeds
the
60 seconds.
What I do not understand is that it works within the 60 seconds if I
define
the periods inside the query manually, but that it isn't working when
selecting from another tabel or via a prompt.

You might try enter the data types of your parameters. This is done in
Query->Parameters. When the table is created, is FADT19 text or numeric?

--
Duane Hookom
MS Access MVP

Duane,

Please find enclosed the SQL view of the query.
It is a "create tabel" query.

SELECT WI61BPDNL_ITDD19L1.DLKD19, WI61BPDNL_ITDD19L1.FIRM19,
WI61BPDNL_ITDD19L1.DOSS19, WI61BPDNL_ITDD19L1.DOSL19,
WI61BPDNL_ITDD19L1.FADT19, WI61BPDNL_ITDD19L1.BILJ19,
WI61BPDNL_ITDD19L1.PCFI19, WI61BPDNL_ITDD19L1.AGVK19,
WI61BPDNL_ITDD19L1.PNCR19, WI61BPDNL_ITDD19L1.CRSN19 INTO tickets
FROM WI61BPDNL_ITDD19L1
WHERE (((WI61BPDNL_ITDD19L1.DLKD19)<>"4") AND
((WI61BPDNL_ITDD19L1.FIRM19)="WL") AND
((WI61BPDNL_ITDD19L1.FADT19)>[Forms]![HOOFDMENU]![Start] And
(WI61BPDNL_ITDD19L1.FADT19)<[Forms]![HOOFDMENU]![end]) AND
((WI61BPDNL_ITDD19L1.BILJ19)<>""));


:

Is your query a Pass-through query? If not it should work if you enter
values in the correct format. If it is a p-t then it would have been
an
important bit of information that should have been included in your
original
post. Your original post should have contained the SQL view of your
query.

--
Duane Hookom
MS Access MVP

I am using Access 2000 and have an ODBC driver in place to get data
from
an
AS/400.
Whe retreiving data (from a very big table) via a query I fill in a
from
till range to determain invoiced transactions.
Criteria: >=20060300 and <= 20060400.
This works perfectly.

I made a selection on a form to be able to select the different
periods.
Criteria: >=[Forms]![HOOFDMENU]![Start] And
<=[Forms]![HOOFDMENU]![end]
When this is the criteria I get an error ODBC link failed.
Also when I want to create a pop-up to fill in the ranges myself,
the
ODBC
fails.
Criteria: >=[From which period?] And <=[Till which period?]
Is there another way to retreive a range of data without having to
adapting
the query?
Thanks.