LEFT JOIN - ODBC Call failed - Error 3146 - (#107)

[Torben Dahl Jensen]

I am having problems using LEFT JOIN when accessing data on
MS SQL Server 7.0 from my MS Access 97 front-end! I don't
have any problems accessing the same data stored in Access
databases, however, only on SQL Server! It's a bit
frustrating!

I have these two queries that are working ok ('qSL' & 'qTL'
below) - designed to get the data in languages en (English)
and fr (French):

qSL:
1, capital cost
2, capital duty
3, capital gearing
...

qTL:
1, coût du capital
2, droit d'apport
...

Then the query qBoth is designed to retrieve all the records
where the language pair is 'en' and 'fr', if available (or
Null):

qBoth:
1, capital cost, coût du capital
2, capital duty, droit d'apport
3, capital gearing, NULL
...

The queries are listed here:

Query: 'qSL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM tdbConcept, tdbTerm, tdbStatus, dclField
WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
tdbTerm.tdbTermID=tdbStatus.tdbTermID AND
tdbTerm.tdbTermLanguage='en' AND
tdbConcept.tdbClass1=dclField.dclCode;

Query: 'qTL'
SELECT tdbTerm_1.tdbConceptID, tdbTerm_1.tdbTerm,
tdbTerm_1.tdbTermID, tdbTerm_1.tdbSort,
tdbStatus_1.tdbStatus, dclField_1.dclAbbreviation,
tdbConcept_1.tdbClass1
FROM tdbConcept AS tdbConcept_1, tdbTerm AS tdbTerm_1,
tdbStatus AS tdbStatus_1, dclField AS dclField_1
WHERE tdbTerm_1.tdbConceptID=tdbConcept_1.tdbConceptID AND
tdbTerm_1.tdbTermID=tdbStatus_1.tdbTermID AND
tdbTerm_1.tdbTermLanguage='fr' AND
tdbConcept_1.tdbClass1=dclField_1.dclCode;

Query: 'qBoth'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm_1.tdbTerm, tdbTerm.tdbSort, tdbConcept.tdbClass1,
dclField.dclAbbreviation, tdbStatus.tdbStatus,
tdbStatus_1.tdbStatus
FROM qSL LEFT JOIN qTL ON qSL.tdbTerm.tdbConceptID =
qTL.tdbTerm_1.tdbConceptID
ORDER BY tdbTerm.tdbSort;

I get this error:

Error: 3146

ODBC: Call failed

[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbConcept_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbConcept_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'dclField_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbStatus_1' does not match with a table name or
alias name used in the query. (#107)

Any ideas?

Best regards,
Torben Dahl Jensen

[Dan McGarry]

Torben,
I'm having a tough time following what exactly the problem is. You list
three queries and four error messages - are you getting them in succession
after the last query (and does that mean that the first two work and the
third blows-up)? After a quick glance I can see two things that aren't
quite right. The first is that the Query: 'qTL' is malformed. Don't use
the word 'AS' in the 'From' clause of that query to denote an alias - just a
space and the alias name. So it should look something like this:

FROM tdbConcept tdbConcept_1, tdbTerm tdbTerm_1,
tdbStatus tdbStatus_1, dclField dclField_1

The other thing that seems off to me is the syntax used in the last query -
'qBoth'. You should use 'LEFT OUTER JOIN' not 'LEFT JOIN'.

--
Dan McGarry
HCI Systems, Inc.
http://www.hcisystems.com

"Torben Dahl Jensen" <t...@xife.dk> wrote in message
news:8ubpba$10he$1...@news.net.uni-c.dk...

[Brett J. Valjalo]

While you don't need the AS, I don't think it would cause SS to choke.
The problem is here:

> > FROM qSL LEFT JOIN qTL ON qSL.tdbTerm.tdbConceptID =
> > qTL.tdbTerm_1.tdbConceptID
> > ORDER BY tdbTerm.tdbSort;

Once the qSL is made, the table names that the fields were originally associated
with are no exposed. Dont use tdbterm and it should work.

--
Brett J. Valjalo
Database Programmer
Taos - The SysAdmin Company
bre...@taos.com

[John Benton]

Why guess? Put your queries into SQL Server 7's query analyzer and look at
the execution plan. It will tell you exacly what/where the problem is.
Chances are you'll need to add appropriate table keys.

Torben Dahl Jensen wrote in message <8ubpba$10he$1...@news.net.uni-c.dk>...

[Torben Dahl Jensen]

Dan,

The 3 queries are stored as queries in an MS Access database
(front-end)

2 of them are working - qSL and qTL!

I'm using ODBC to communicate with SQL Server 7.0 SP1

Only when opening qBoth from the front-end I get the error
messages in succession:

Error: 3146

ODBC: Call failed

[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbConcept_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbConcept_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'dclField_1' does not match with a table name or
alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'tdbStatus_1' does not match with a table name or
alias name used in the query. (#107)

I don't get the error if the tables are stored an Access
database - ONLY with data on SQL Server!!!

Please help!

Thanks,
Torben
(remove the 'x' in my email address)

[Torben Dahl Jensen]

Dan,

> Torben,
> I'm having a tough time following what exactly the
problem is. You list
> three queries and four error messages - are you getting
them in succession
> after the last query (and does that mean that the first
two work and the
> third blows-up)? After a quick glance I can see two
things that aren't
> quite right. The first is that the Query: 'qTL' is
malformed. Don't use
> the word 'AS' in the 'From' clause of that query to denote
an alias - just a
> space and the alias name. So it should look something
like this:
>
> FROM tdbConcept tdbConcept_1, tdbTerm tdbTerm_1,
> tdbStatus tdbStatus_1, dclField dclField_1
>
> The other thing that seems off to me is the syntax used in
the last query -
> 'qBoth'. You should use 'LEFT OUTER JOIN' not 'LEFT
JOIN'.

Your suggestions don't change anything.
I have tried to remove the 'AS' and change 'LEFT JOIN' to
'LEFT OUTER JOIN'.

Nothing changes!

Still the same error messages!

Torben

[John Viescas]

Your queries need just a bit of work:

Query: 'qSL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1

FROM ((tdbConcept INNER JOIN tdbTerm
ON tdbTerm.tdbConceptID=tdbConcept.tdbConceptID)
INNER JOIN tdbStatus
ON tdbTerm.tdbTermID=tdbStatus.tdbTermID)
INNER JOIN dclField ON tdbConcept.tdbClass1=dclField.dclCode
WHERE tdbTerm.tdbTermLanguage='en';

Query: 'qTL'
SELECT tdbTerm_1.tdbConceptID, tdbTerm_1.tdbTerm,
tdbTerm_1.tdbTermID, tdbTerm_1.tdbSort,
tdbStatus_1.tdbStatus, dclField_1.dclAbbreviation,
tdbConcept_1.tdbClass1

FROM ((tdbConcept AS tdbConcept_1
INNER JOIN tdbTerm AS tdbTerm_1
ON tdbTerm_1.tdbConceptID=tdbConcept_1.tdbConceptID)
INNER JOIN tdbStatus AS tdbStatus_1
ON tdbTerm_1.tdbTermID=tdbStatus_1.tdbTermID)
INNER JOIN dclField AS dclField_1
ON tdbConcept_1.tdbClass1=dclField_1.dclCode
WHERE tdbTerm_1.tdbTermLanguage='fr';
[n.b. the aliases in the above query are unnecessary]

Query: 'qBoth'
SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus

FROM qSL LEFT JOIN qTL ON qSL.tdbTerm.tdbConceptID =
qTL.tdbTerm_1.tdbConceptID

ORDER BY qSL.tdbSort;

Note that qSL and qTL act like Views -- if you use them in a subsequent
query, you have no visibility to the underlying tables. qSL becomes a
"logical" table, and the names of the columns are the names returned by the
SELECT clause, but without any qualifier. I'm surprised that you say the
third query runs at all in native Access.

This may run really slowly. You would be better off creating qSL and qTL on
the server as stored procedures, and then use a pass-through query to get
the final result.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/

"Torben Dahl Jensen" <t...@xife.dk> wrote in message
news:8ubpba$10he$1...@news.net.uni-c.dk...

> Error: 3146
>
> ODBC: Call failed
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column
> prefix 'tdbConcept_1' does not match with a table name or
> alias name used in the query. (#107)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column
> prefix 'tdbConcept_1' does not match with a table name or
> alias name used in the query. (#107)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column
> prefix 'dclField_1' does not match with a table name or
> alias name used in the query. (#107)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column
> prefix 'tdbStatus_1' does not match with a table name or
> alias name used in the query. (#107)
>

[Torben Dahl Jensen]

John,

It still doesn't work! I tried to change the queries to what
you suggested and I get the exact same error message - see
below

I also doesn't work if I change to this:

Query: 'qSL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM ((tdbConcept INNER JOIN tdbTerm
ON tdbTerm.tdbConceptID=tdbConcept.tdbConceptID)
INNER JOIN tdbStatus
ON tdbTerm.tdbTermID=tdbStatus.tdbTermID)
INNER JOIN dclField ON tdbConcept.tdbClass1=dclField.dclCode
WHERE tdbTerm.tdbTermLanguage='en';

Query: 'qTL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM ((tdbConcept INNER JOIN tdbTerm
ON tdbTerm.tdbConceptID=tdbConcept.tdbConceptID)
INNER JOIN tdbStatus
ON tdbTerm.tdbTermID=tdbStatus.tdbTermID)
INNER JOIN dclField ON tdbConcept.tdbClass1=dclField.dclCode

WHERE tdbTerm.tdbTermLanguage='fr';

- qSL and qTL are exactly the same, just the language
differs - BOTH WORKING!

Query: 'qBoth'
SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus
FROM qSL LEFT JOIN qTL ON qSL.tdbTerm.tdbConceptID =

qTL.tdbTerm.tdbConceptID
ORDER BY qSL.tdbSort;

This one does NOT WORK

The error message is:

ODBC -- Call failed

[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS2' does not match with a table name or

alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS2' does not match with a table name or

alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS3' does not match with a table name or

alias name used in the query. (#107)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS4' does not match with a table name or

alias name used in the query. (#107)

Btw, I didn't think that you could use pass-through queries
and stored-procedures as data source for a
form/report/control - which is what I'm using qBoth for?

Best regards,
Torben Dahl Jensen

"John Viescas" <Jo...@nomail.please> wrote in message
news:eAShoV9TAHA.278@cppssbbsa03...

[Torben Dahl Jensen]

This is the result of an ODBC trace:

DEVELOP fff8fb1d-fff9a1ad ENTER SQLSetConnectOption
HDBC 05B6FD0C
UWORD 101 <SQL_ACCESS_MODE>
SQLULEN 1

DEVELOP fff8fb1d-fff9a1ad EXIT SQLSetConnectOption
with return code 0 (SQL_SUCCESS)
HDBC 05B6FD0C
UWORD 101 <SQL_ACCESS_MODE>
SQLULEN 1

DEVELOP fff8fb1d-fff9a1ad ENTER SQLAllocStmt
HDBC 05B6FD0C
HSTMT * 1AE749D4

DEVELOP fff8fb1d-fff9a1ad EXIT SQLAllocStmt with
return code 0 (SQL_SUCCESS)
HDBC 05B6FD0C
HSTMT * 0x1AE749D4 ( 0x05a700c8)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLGetStmtOption
HSTMT 05A700C8
UWORD 0
PTR 0x0062C55C

DEVELOP fff8fb1d-fff9a1ad EXIT SQLGetStmtOption
with return code 0 (SQL_SUCCESS)
HSTMT 05A700C8
UWORD 0
PTR 0x0062C55C

DEVELOP fff8fb1d-fff9a1ad ENTER SQLSetStmtOption
HSTMT 05A700C8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLLEN 60

DEVELOP fff8fb1d-fff9a1ad EXIT SQLSetStmtOption
with return code 0 (SQL_SUCCESS)
HSTMT 05A700C8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLLEN 60

DEVELOP fff8fb1d-fff9a1ad ENTER SQLExecDirectW
HSTMT 05A700C8
WCHAR * 0x1AE74C18 [ -3]
"SELECT
"dbo"."tdbConcept"."tdbConceptID",
"dbo"."tdbTerm"."tdbTermID",
"dbo"."tdbStatus"."tdbTermID",
"dbo"."tdbStatus"."tdbArea",
"dbo"."dclField"."dclCode",
"dbo"."dclField"."dclLanguage",

"tdbConcept_1"."tdbConceptID",
"tdbTerm_1"."tdbTermID",
"tdbStatus_1"."tdbTermID",
"tdbStatus_1"."tdbArea",
"dclField_1"."dclCode",
"dclField_1"."dclLanguage"

FROM
"dbo"."tdbConcept",
"dbo"."dclField",
"dbo"."tdbStatus",

"dbo"."tdbConcept" "tdbConcept_1",
"dbo"."dclField" "dclField_1",
"dbo"."tdbStatus" "tdbStatus_1",

{oj "dbo"."tdbTerm" LEFT OUTER JOIN "dbo"."tdbTerm"
"tdbTerm_1" ON
(("dbo"."tdbTerm"."tdbConceptID" =
"tdbTerm_1"."tdbConceptID" ) AND
(((("tdbTerm_1"."tdbTermLanguage" = 'fr' ) AND
("tdbTerm_1"."tdbConceptID" =
"tdbConcept_1"."tdbConceptID" ) ) AND
("tdbConcept_1"."tdbClass1" = "dclField_1"."dclCode" ) )
AND
("tdbTerm_1"."tdbTermID" =
"tdbStatus_1"."tdbTermID" ) ) ) }

WHERE

(((("dbo"."tdbTerm"."tdbTermLanguage" = 'en' ) AND
("dbo"."tdbTerm"."tdbConceptID" =
"dbo"."tdbConcept"."tdbConceptID" ) ) AND
("dbo"."tdbConcept"."tdbClass1" =
"dbo"."dclField"."dclCode" ) ) AND
("dbo"."tdbTerm"."tdbTermID" =
"dbo"."tdbStatus"."tdbTermID" ) )

ORDER BY "dbo"."tdbTerm"."tdbSort" \ 0"
SDWORD -3

DEVELOP fff8fb1d-fff9a1ad EXIT SQLExecDirectW with
return code -1 (SQL_ERROR)
HSTMT 05A700C8
WCHAR * 0x1AE74C18 [ -3]
"SELECT "dbo"."tdbConcept"."tdbConceptID",
"dbo"."tdbTerm"."tdbTermID",
"dbo"."tdbStatus"."tdbTermID",
"dbo"."tdbStatus"."tdbArea",
"dbo"."dclField"."dclCode",
"dbo"."dclField"."dclLanguage",

"tdbConcept_1"."tdbConceptID",
"tdbTerm_1"."tdbTermID",
"tdbStatus_1"."tdbTermID",
"tdbStatus_1"."tdbArea",
"dclField_1"."dclCode",
"dclField_1"."dclLanguage"

FROM
"dbo"."tdbConcept",
"dbo"."dclField",
"dbo"."tdbStatus",
"dbo"."tdbConcept" "tdbConcept_1",
"dbo"."dclField" "dclField_1",
"dbo"."tdbStatus" "tdbStatus_1",

{oj "dbo"."tdbTerm" LEFT OUTER JOIN "dbo"."tdbTerm"
"tdbTerm_1" ON

(("dbo"."tdbTerm"."tdbConceptID" =
"tdbTerm_1"."tdbConceptID" ) AND
(((("tdbTerm_1"."tdbTermLanguage" = 'fr' ) AND
("tdbTerm_1"."tdbConceptID" =
"tdbConcept_1"."tdbConceptID" ) ) AND
("tdbConcept_1"."tdbClass1" = "dclField_1"."dclCode" ) )
AND
("tdbTerm_1"."tdbTermID" =
"tdbStatus_1"."tdbTermID" ) ) ) }

WHERE
(((("dbo"."tdbTerm"."tdbTermLanguage" = 'en' ) AND
("dbo"."tdbTerm"."tdbConceptID" =
"dbo"."tdbConcept"."tdbConceptID" ) ) AND
("dbo"."tdbConcept"."tdbClass1" =
"dbo"."dclField"."dclCode" ) ) AND
("dbo"."tdbTerm"."tdbTermID" =
"dbo"."tdbStatus"."tdbTermID" ) )

ORDER BY "dbo"."tdbTerm"."tdbSort" \ 0"
SDWORD -3

DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL

Server]The column prefix 'tdbConcept_1' does not match with

a table name or alias name used in the query. (107)

DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL

Server]The column prefix 'tdbConcept_1' does not match with

a table name or alias name used in the query. (107)

DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL

Server]The column prefix 'dclField_1' does not match with a

table name or alias name used in the query. (107)

DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL

Server]The column prefix 'tdbStatus_1' does not match with a

table name or alias name used in the query. (107)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLErrorW
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B1BC
SWORD 4095
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad EXIT SQLErrorW with
return code 0 (SQL_SUCCESS)
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518 (107)
WCHAR * 0x02C6B1BC [ 145]

"[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'tdbCon"
SWORD 4095
SWORD * 0x0062C508 (145)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLErrorW
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B2F4
SWORD 3939
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad EXIT SQLErrorW with
return code 0 (SQL_SUCCESS)
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518 (107)
WCHAR * 0x02C6B2F4 [ 145]

"[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'tdbCon"
SWORD 3939
SWORD * 0x0062C508 (145)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLErrorW
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B42C
SWORD 3783
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad EXIT SQLErrorW with
return code 0 (SQL_SUCCESS)
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518 (107)
WCHAR * 0x02C6B42C [ 143]

"[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'dclFi"
SWORD 3783
SWORD * 0x0062C508 (143)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLErrorW
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B560
SWORD 3629
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad EXIT SQLErrorW with
return code 0 (SQL_SUCCESS)
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518 (107)
WCHAR * 0x02C6B560 [ 144]

"[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'tdbSta"
SWORD 3629
SWORD * 0x0062C508 (144)

DEVELOP fff8fb1d-fff9a1ad ENTER SQLErrorW
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B696
SWORD 3474
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad EXIT SQLErrorW with
return code 100 (SQL_NO_DATA_FOUND)
HENV 05B6FC6C
HDBC 05B6FD0C
HSTMT 05A700C8
WCHAR * 0x0062C4CC (NYI)
SDWORD * 0x0062C518
WCHAR * 0x02C6B696
SWORD 3474
SWORD * 0x0062C508

DEVELOP fff8fb1d-fff9a1ad ENTER SQLFreeStmt
HSTMT 05A700C8
UWORD 1 <SQL_DROP>

DEVELOP fff8fb1d-fff9a1ad EXIT SQLFreeStmt with
return code 0 (SQL_SUCCESS)
HSTMT 05A700C8
UWORD 1 <SQL_DROP>

Any ideas?????

[John Viescas]

<< Btw, I didn't think that you could use pass-through queries
and stored-procedures as data source for a
form/report/control - which is what I'm using qBoth for? >>

Yes you can. You must make sure that the passthrough has it's properties
set to indicate that it returns rows.

[John Viescas]

First, I didn't "desk check" the qBoth syntax I suggested you use carefully
enough! It should be:

Query: 'qBoth'
SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus

FROM qSL LEFT JOIN qTL ON qSL.tdbConceptID =
qTL.tdbConceptID
ORDER BY qSL.tdbSort;

I'm not sure that will fix it, though.

From the SQL trace, it looks like a bug in the ODBC driver to me. It SHOULD
be passing the equivalent of query qSL (language = 'en') and qTL (language =
'fr') separately to the server and then do the OUTER JOIN in Access. It
doesn't seem to complain about the 'en' query, but the SQL is all screwed
up. If fails on the 'fr' query for a bogus reason -- those aliases ARE in
the SQL. Here's the 'en' query it says it sent:

WHERE

Note that the stuff enclosed in { } is ignored by the server. Oracle
doesn't support OUTER JOIN, so the driver is supposed to convert it to the
Oracle equivalent in the WHERE clause. The problem is it should NOT be
listing the tables twice in the FROM clause and should not be attempting an
OUTER JOIN here at all.

Is there any way that you can get the 'en' and 'fr' queries defined on the
server and then try a passthrough? You'll need to use Oracle syntax if you
do that.

SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus

FROM qSL, qTL
WHERE qSL.tdbConceptID = QTL.tdbConceptID(+)
ORDER BY qSL.tdbSort;

[Torben Dahl Jensen]

Thank you very much for your input!

> Query: 'qBoth'
> SELECT qSL.tdbConceptID, qSL.tdbTerm,
> qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
> qSL.dclAbbreviation, qSL.tdbStatus,
> qTL.tdbStatus
> FROM qSL LEFT JOIN qTL ON qSL.tdbConceptID =
> qTL.tdbConceptID
> ORDER BY qSL.tdbSort;
>
> I'm not sure that will fix it, though.

Correct, it didn't!

> From the SQL trace, it looks like a bug in the ODBC driver
to me. It SHOULD
> be passing the equivalent of query qSL (language = 'en')
and qTL (language =
> 'fr') separately to the server and then do the OUTER JOIN
in Access. It
> doesn't seem to complain about the 'en' query, but the SQL
is all screwed
> up. If fails on the 'fr' query for a bogus reason --
those aliases ARE in
> the SQL. Here's the 'en' query it says it sent:

A bug!! How do I solve this problem?

> Note that the stuff enclosed in { } is ignored by the
server. Oracle
> doesn't support OUTER JOIN, so the driver is supposed to
convert it to the
> Oracle equivalent in the WHERE clause. The problem is it
should NOT be
> listing the tables twice in the FROM clause and should not
be attempting an
> OUTER JOIN here at all.

But It's not Oracle! I'm using:

MS Access 97,
MDAC 2.6 RTM (on the Access client machine) and
MS SQL Server 7 SP2!

??

> Is there any way that you can get the 'en' and 'fr'
queries defined on the
> server and then try a passthrough? You'll need to use
Oracle syntax if you
> do that.

It' s somewhat difficult since the languages are variable. I
just hard-coded it to make it easier to read.

The languages are selected in a combobox by the user and
then inserted into the queries dynamically!

> SELECT qSL.tdbConceptID, qSL.tdbTerm,
> qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
> qSL.dclAbbreviation, qSL.tdbStatus,
> qTL.tdbStatus
> FROM qSL, qTL
> WHERE qSL.tdbConceptID = QTL.tdbConceptID(+)
> ORDER BY qSL.tdbSort;

This doesn't work using qSL and qTL (Views stored on the MS
SQL Server) The query error is:

Incorrect syntax near '+'!

Torben

[John Viescas]

See comments below...

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/

"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:8vgj8o$djo$1...@news.net.uni-c.dk...

> Thank you very much for your input!
>
> > Query: 'qBoth'
> > SELECT qSL.tdbConceptID, qSL.tdbTerm,
> > qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
> > qSL.dclAbbreviation, qSL.tdbStatus,
> > qTL.tdbStatus
> > FROM qSL LEFT JOIN qTL ON qSL.tdbConceptID =
> > qTL.tdbConceptID
> > ORDER BY qSL.tdbSort;
> >
> > I'm not sure that will fix it, though.
>
> Correct, it didn't!

Well, this SHOULD work, but I have concerns noted below...

>
> > From the SQL trace, it looks like a bug in the ODBC driver
> to me. It SHOULD
> > be passing the equivalent of query qSL (language = 'en')
> and qTL (language =
> > 'fr') separately to the server and then do the OUTER JOIN
> in Access. It
> > doesn't seem to complain about the 'en' query, but the SQL
> is all screwed
> > up. If fails on the 'fr' query for a bogus reason --
> those aliases ARE in
> > the SQL. Here's the 'en' query it says it sent:
>
> A bug!! How do I solve this problem?

Take a vacation in the Bahamas. Seriously, this all SHOULD work! I don't
know why I thought you were talking to Oracle - perhaps because of the
strange SQL the driver says it's sending. SQL Server should be a
no-brainer.

>
> > Note that the stuff enclosed in { } is ignored by the
> server. Oracle
> > doesn't support OUTER JOIN, so the driver is supposed to
> convert it to the
> > Oracle equivalent in the WHERE clause. The problem is it
> should NOT be
> > listing the tables twice in the FROM clause and should not
> be attempting an
> > OUTER JOIN here at all.
>
> But It's not Oracle! I'm using:
>
> MS Access 97,
> MDAC 2.6 RTM (on the Access client machine) and
> MS SQL Server 7 SP2!
>
> ??

DUH. I had a brain-fart on this one.

>
> > Is there any way that you can get the 'en' and 'fr'
> queries defined on the
> > server and then try a passthrough? You'll need to use
> Oracle syntax if you
> > do that.
>
> It' s somewhat difficult since the languages are variable. I
> just hard-coded it to make it easier to read.
>
> The languages are selected in a combobox by the user and
> then inserted into the queries dynamically!

AHA! Post the ACTUAL SQL you're executing. Do not substitute anything for
"readability." Give me the ORIGINAL syntax for qSL, qTL, and qBoth that
you're trying to use now.

>
> > SELECT qSL.tdbConceptID, qSL.tdbTerm,
> > qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
> > qSL.dclAbbreviation, qSL.tdbStatus,
> > qTL.tdbStatus
> > FROM qSL, qTL
> > WHERE qSL.tdbConceptID = QTL.tdbConceptID(+)
> > ORDER BY qSL.tdbSort;
>
> This doesn't work using qSL and qTL (Views stored on the MS
> SQL Server) The query error is:
>
> Incorrect syntax near '+'!

Well, because of my "brain fart" thinking you were talking to Oracle, I gave
you Oracle non-standard syntax. Of course SQL Server won't like it. You
should be able to pass the OUTER JOIN syntax directly to SQL Server.

[Torben Dahl Jensen]

Comments below...

> > > Is there any way that you can get the 'en' and 'fr'
> > queries defined on the
> > > server and then try a passthrough? You'll need to use
> > Oracle syntax if you
> > > do that.
> >
> > It' s somewhat difficult since the languages are
variable. I
> > just hard-coded it to make it easier to read.
> >
> > The languages are selected in a combobox by the user and
> > then inserted into the queries dynamically!
>
> AHA! Post the ACTUAL SQL you're executing. Do not
substitute anything for
> "readability." Give me the ORIGINAL syntax for qSL, qTL,
and qBoth that
> you're trying to use now.

I doesn't change anything! I just use a function to insert
the two languages. And the query (qBoth) fails no matter
what!

Here are the queries again:

qSL = WORKING
qTL = WORKING
qBOTH = NOT WORKING

Query: 'qSL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM tdbConcept, tdbTerm, tdbStatus, dclField
WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
tdbTerm.tdbTermID=tdbStatus.tdbTermID AND
tdbTerm.tdbTermLanguage='en' AND
tdbConcept.tdbClass1=dclField.dclCode;

If the function is used here the where clause is:
...
tdbTerm.tdbTermLanguage = getglbSourceLanguage()
...

Query: 'qTL'
SELECT tdbTerm_1.tdbConceptID, tdbTerm_1.tdbTerm,
tdbTerm_1.tdbTermID, tdbTerm_1.tdbSort,
tdbStatus_1.tdbStatus, dclField_1.dclAbbreviation,
tdbConcept_1.tdbClass1
FROM tdbConcept AS tdbConcept_1, tdbTerm AS tdbTerm_1,
tdbStatus AS tdbStatus_1, dclField AS dclField_1
WHERE tdbTerm_1.tdbConceptID=tdbConcept_1.tdbConceptID AND
tdbTerm_1.tdbTermID=tdbStatus_1.tdbTermID AND
tdbTerm_1.tdbTermLanguage='fr' AND
tdbConcept_1.tdbClass1=dclField_1.dclCode;

If the function is used here the where clause is:
...
tdbTerm.tdbTermLanguage = getglbTargetLanguage()
...

Query: 'qBoth'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm_1.tdbTerm, tdbTerm.tdbSort, tdbConcept.tdbClass1,
dclField.dclAbbreviation, tdbStatus.tdbStatus,
tdbStatus_1.tdbStatus
FROM qSL LEFT JOIN qTL ON qSL.tdbTerm.tdbConceptID =
qTL.tdbTerm_1.tdbConceptID
ORDER BY tdbTerm.tdbSort;

This query fails - you know the error message

HELP HELP...

[John Viescas]

Your qBOTH query is STILL WRONG. Do it EXACTLY like this:

Query: 'qBoth'
SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus
FROM qSL LEFT JOIN qTL ON qSL.tdbConceptID =
qTL.tdbConceptID
ORDER BY qSL.tdbSort;

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:8vitcb$vji$1...@news.net.uni-c.dk...

[Torben Dahl Jensen]

Tried it...

It didn't work either....

Here is the sql and error message:

Query: 'qSL'
SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM tdbConcept, tdbTerm, tdbStatus, dclField
WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
tdbTerm.tdbTermID=tdbStatus.tdbTermID AND
tdbTerm.tdbTermLanguage='en' AND
tdbConcept.tdbClass1=dclField.dclCode;

Query: 'qTL'

SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM tdbConcept, tdbTerm, tdbStatus, dclField
WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
tdbTerm.tdbTermID=tdbStatus.tdbTermID AND

tdbTerm.tdbTermLanguage='fr' AND
tdbConcept.tdbClass1=dclField.dclCode;

- qSL and qTL are exactly the same, just the language
differs - BOTH WORKING!

Query: 'qBoth'

SELECT qSL.tdbConceptID, qSL.tdbTerm,
qTL.tdbTerm, qSL.tdbSort, qSL.tdbClass1,
qSL.dclAbbreviation, qSL.tdbStatus,
qTL.tdbStatus
FROM qSL LEFT JOIN qTL ON qSL.tdbConceptID =
qTL.tdbConceptID
ORDER BY qSL.tdbSort;

This one does NOT WORK

The error message is:

ODBC -- Call failed

[Microsoft][ODBC SQL Server Driver][SQL Server]The column
prefix 'MS2' does not match with a table name or
alias name used in the query. (#107)

[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS2' does not match with a table name or
alias name used in the query. (#107)

[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS3' does not match with a table name or
alias name used in the query. (#107)

[Microsoft][ODBC SQL Server Driver][SQL Server]The column

prefix 'MS4' does not match with a table name or
alias name used in the query. (#107)

Could you tell me how to do it using pass through queries?

I need to be able to set the Source and Target languages at
discretion... They can be combined in a number of ways
(da-fr, da-en, en-da, fr-en etc.). And I don't think that we
would want to define all possible languages on the
server....?

qSL and qTL must be stored on the SQL Server somehow -
including the ability to put two languages into the
views/stored procedures?

And then use qBOTH as a pass through query in the MS Access
client application?

Or is there some other way than using three queries stored
in MS Access. I doesn't seem as if we can get that to work.

Again thanks for your input!

Torben Dahl Jensen

[John Viescas]

Torben-

There are some known problems with JET in Access 97 attempting to do an
OUTER JOIN on filtered ODBC data. There's a fix available at:
http://support.microsoft.com/support/kb/articles/Q225/4/22.ASP

I tried this myself using Northwind on SQL Server and three queries similar
to yours. I did not get ODBC call failed, but I did get the WRONG answer in
Access 97 (the result was as though it had done an INNER JOIN). Access 2000
returns the correct answer.

If you want to try the stored procedure route, you need to build qSL and qTL
on the server like this:

Create Procedure qSL
@Language nVarChar(5) AS

SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
dclField.dclAbbreviation, tdbConcept.tdbClass1
FROM tdbConcept, tdbTerm, tdbStatus, dclField
WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
tdbTerm.tdbTermID=tdbStatus.tdbTermID AND

tdbTerm.tdbTermLanguage=@Language AND
tdbConcept.tdbClass1=dclField.dclCode

To get the final answer, you must build a passthrough query and then
substitute the real value from code before you execute it by modifying the
QueryDef SQL property. The passthrough template will look like:

Exec qSL @Language='en'

Be sure to set the ODBC connection properties to automatically link to your
server. If you don't do that, then the user will get prompted for the info.
To modify the SQL on the fly (assuming your local pass-through query is also
named "qSL"), write a little function like this:

Function SetSL(strValue As String) As Integer
Dim qd As Querydef
Set qd = CurrentDb.QueryDefs("qSL")
qd.SQL = "Exec qSL @Language='" & strValue & "'"
SetSL = True
End Function

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:8vl707$r4g$1...@news.net.uni-c.dk...

[Torben Dahl Jensen]

Thank you very much!

This looks very interesting! Finally, it seems as if we are
near an answer

Is it possible to use this kind of passthrough query as
record source for a form?

Torben Dahl Jensen

"John Viescas" <Jo...@nomail.please> wrote in message

news:uqL1atiVAHA.246@cppssbbsa04...

> Torben-
>
> There are some known problems with JET in Access 97
attempting to do an
> OUTER JOIN on filtered ODBC data. There's a fix available
at:
>
http://support.microsoft.com/support/kb/articles/Q225/4/22.A
SP
>
> I tried this myself using Northwind on SQL Server and
three queries similar
> to yours. I did not get ODBC call failed, but I did get
the WRONG answer in
> Access 97 (the result was as though it had done an INNER
JOIN). Access 2000
> returns the correct answer.
>
> If you want to try the stored procedure route, you need to
build qSL and qTL
> on the server like this:
>
> Create Procedure qSL
> @Language nVarChar(5) AS

> SELECT tdbTerm.tdbConceptID, tdbTerm.tdbTerm,
> tdbTerm.tdbTermID, tdbTerm.tdbSort, tdbStatus.tdbStatus,
> dclField.dclAbbreviation, tdbConcept.tdbClass1
> FROM tdbConcept, tdbTerm, tdbStatus, dclField
> WHERE tdbTerm.tdbConceptID=tdbConcept.tdbConceptID AND
> tdbTerm.tdbTermID=tdbStatus.tdbTermID AND

[John Viescas]

Yes, but it won't be updatable.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:8vm2cl$ibq$1...@news.net.uni-c.dk...

[Torben Dahl Jensen]

This is simply great!

Thank you!

I created:

- one view (to retrieve the fields I need)
- one stored procedure (to perform LEFT JOIN using the view
and the two selected languages)
- one pass through query (to call the sp)

it works!

But how do I refresh the records in the pass through query.
For instance, if I edited or deleted a record?

How can I test the the recordset is empty and display
message to the user?

Torben

"John Viescas" <Jo...@nomail.please> wrote in message

news:O6czXNn...@cppssbbsa02.microsoft.com...

[John Viescas]

A Requery should get you updated and added rows. The Form's
RecordsetClone.RecordCount property should be 0 if there's no rows.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:8vtu8c$17cu$1...@news.net.uni-c.dk...

> This is simply great!
>
> Thank you!
>
> I created:
>
> - one view (to retrieve the fields I need)
> - one stored procedure (to perform LEFT JOIN using the view
> and the two selected languages)
> - one pass through query (to call the sp)
>
> it works!
>
> But how do I refresh the records in the pass through query.
> For instance, if I edited or deleted a record?
>
> How can I test the the recordset is empty and display
> message to the user?
>
> Torben
>

> "John Viescas" <Jo...@nomail.please> wrote in message

> news:O6czXNn...@cppssbbsa02.microsoft.com...

[Torben Dahl Jensen]

> A Requery should get you updated and added rows.

It doesn't work.

I tried this:

Private Sub Form_Activate()

' the user returns to this form after having
' edited/added/deleted a record on the list
' of results

SendKeys "+{F9}"
Me.Requery

End Sub

> The Form's RecordsetClone.RecordCount
> property should be 0 if there's no rows.

Yes, but I would like to test before showing an empty form.
I would rather display a msgbox.

"John Viescas" <Jo...@nomail.please> wrote in message

news:O7RbQ$KWAHA.248@cppssbbsa03...

> A Requery should get you updated and added rows. The
Form's
> RecordsetClone.RecordCount property should be 0 if there's
no rows.
>

> --
> John Viescas, author
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> news:8vtu8c$17cu$1...@news.net.uni-c.dk...
> > This is simply great!
> >
> > Thank you!
> >
> > I created:
> >
> > - one view (to retrieve the fields I need)
> > - one stored procedure (to perform LEFT JOIN using the
view
> > and the two selected languages)
> > - one pass through query (to call the sp)
> >
> > it works!
> >
> > But how do I refresh the records in the pass through
query.
> > For instance, if I edited or deleted a record?
> >
> > How can I test the the recordset is empty and display
> > message to the user?
> >
> > Torben
> >

> > "John Viescas" <Jo...@nomail.please> wrote in message

> > news:O6czXNn...@cppssbbsa02.microsoft.com...

[John Viescas]

Yup. Requery doesn't work. I think that the form considers it to be a
Snapshot, so Requery has no effect. One way I found that *will* rebuild the
recordset is to reset the form's RecordSource property -- even if all you do
is set it equal to the same passthrough query. Seems like a kludge.

As far as testing for rows, you can open a recordset on it in code and test
that first. You can also open the form bound to it hidden, test
RecordSetClone.Recordcount, and set the form's visible property to True if
there are rows. If there are no rows, simply close the hidden form.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:902k1a$opa$1...@news.net.uni-c.dk...

> > A Requery should get you updated and added rows.
>
> It doesn't work.
>
> I tried this:
>
> Private Sub Form_Activate()
>
> ' the user returns to this form after having
> ' edited/added/deleted a record on the list
> ' of results
>
> SendKeys "+{F9}"
> Me.Requery
>
> End Sub
>
> > The Form's RecordsetClone.RecordCount
> > property should be 0 if there's no rows.
>
> Yes, but I would like to test before showing an empty form.
> I would rather display a msgbox.
>

> "John Viescas" <Jo...@nomail.please> wrote in message

> news:O7RbQ$KWAHA.248@cppssbbsa03...
> > A Requery should get you updated and added rows. The
> Form's
> > RecordsetClone.RecordCount property should be 0 if there's
> no rows.
> >

> > --
> > John Viescas, author
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> > "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> > news:8vtu8c$17cu$1...@news.net.uni-c.dk...
> > > This is simply great!
> > >
> > > Thank you!
> > >
> > > I created:
> > >
> > > - one view (to retrieve the fields I need)
> > > - one stored procedure (to perform LEFT JOIN using the
> view
> > > and the two selected languages)
> > > - one pass through query (to call the sp)
> > >
> > > it works!
> > >
> > > But how do I refresh the records in the pass through
> query.
> > > For instance, if I edited or deleted a record?
> > >
> > > How can I test the the recordset is empty and display
> > > message to the user?
> > >
> > > Torben
> > >

> > > "John Viescas" <Jo...@nomail.please> wrote in message

> > > news:O6czXNn...@cppssbbsa02.microsoft.com...

[Torben Dahl Jensen]

Does it matter that the form is unbound which this one
is......?

Is it still possible to use your suggestions below?

If so, would it be possible to give an example?

Torben

"John Viescas" <Jo...@nomail.please> wrote in message

news:erWT0GlWAHA.238@cppssbbsa05...

> Yup. Requery doesn't work. I think that the form
considers it to be a
> Snapshot, so Requery has no effect. One way I found that
*will* rebuild the
> recordset is to reset the form's RecordSource property --
even if all you do
> is set it equal to the same passthrough query. Seems like
a kludge.
>
> As far as testing for rows, you can open a recordset on it
in code and test
> that first. You can also open the form bound to it
hidden, test
> RecordSetClone.Recordcount, and set the form's visible
property to True if
> there are rows. If there are no rows, simply close the
hidden form.
>

> --
> John Viescas, author
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> news:902k1a$opa$1...@news.net.uni-c.dk...
> > > A Requery should get you updated and added rows.
> >
> > It doesn't work.
> >
> > I tried this:
> >
> > Private Sub Form_Activate()
> >
> > ' the user returns to this form after having
> > ' edited/added/deleted a record on the list
> > ' of results
> >
> > SendKeys "+{F9}"
> > Me.Requery
> >
> > End Sub
> >
> > > The Form's RecordsetClone.RecordCount
> > > property should be 0 if there's no rows.
> >
> > Yes, but I would like to test before showing an empty
form.
> > I would rather display a msgbox.
> >

> > "John Viescas" <Jo...@nomail.please> wrote in message

> > news:O7RbQ$KWAHA.248@cppssbbsa03...
> > > A Requery should get you updated and added rows. The
> > Form's
> > > RecordsetClone.RecordCount property should be 0 if
there's
> > no rows.
> > >

> > > --
> > > John Viescas, author
> > > "Running Microsoft Access 2000"
> > > "SQL Queries for Mere Mortals"
> > > http://www.viescas.com/
> > > "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> > > news:8vtu8c$17cu$1...@news.net.uni-c.dk...
> > > > This is simply great!
> > > >
> > > > Thank you!
> > > >
> > > > I created:
> > > >
> > > > - one view (to retrieve the fields I need)
> > > > - one stored procedure (to perform LEFT JOIN using
the
> > view
> > > > and the two selected languages)
> > > > - one pass through query (to call the sp)
> > > >
> > > > it works!
> > > >
> > > > But how do I refresh the records in the pass through
> > query.
> > > > For instance, if I edited or deleted a record?
> > > >
> > > > How can I test the the recordset is empty and
display
> > > > message to the user?
> > > >
> > > > Torben
> > > >

> > > > "John Viescas" <Jo...@nomail.please> wrote in
message

> > > > news:O6czXNn...@cppssbbsa02.microsoft.com...

[John Viescas]

Torben-

If the form is truly unbound, then you must be opening a recordset in
code -- yes? Test the Recordcount of that recordset. To see any new or
changed rows, close and reopen the recordset. How are you getting the data
to display in the unbound form?

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
"Torben Dahl Jensen" <t...@xife.dk> wrote in message

news:90548q$13ca$1...@news.net.uni-c.dk...

> Does it matter that the form is unbound which this one
> is......?
>
> Is it still possible to use your suggestions below?
>
> If so, would it be possible to give an example?
>
> Torben
>

> "John Viescas" <Jo...@nomail.please> wrote in message

> news:erWT0GlWAHA.238@cppssbbsa05...
> > Yup. Requery doesn't work. I think that the form
> considers it to be a
> > Snapshot, so Requery has no effect. One way I found that
> *will* rebuild the
> > recordset is to reset the form's RecordSource property --
> even if all you do
> > is set it equal to the same passthrough query. Seems like
> a kludge.
> >
> > As far as testing for rows, you can open a recordset on it
> in code and test
> > that first. You can also open the form bound to it
> hidden, test
> > RecordSetClone.Recordcount, and set the form's visible
> property to True if
> > there are rows. If there are no rows, simply close the
> hidden form.
> >

> > --
> > John Viescas, author
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> > "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> > news:902k1a$opa$1...@news.net.uni-c.dk...
> > > > A Requery should get you updated and added rows.
> > >
> > > It doesn't work.
> > >
> > > I tried this:
> > >
> > > Private Sub Form_Activate()
> > >
> > > ' the user returns to this form after having
> > > ' edited/added/deleted a record on the list
> > > ' of results
> > >
> > > SendKeys "+{F9}"
> > > Me.Requery
> > >
> > > End Sub
> > >
> > > > The Form's RecordsetClone.RecordCount
> > > > property should be 0 if there's no rows.
> > >
> > > Yes, but I would like to test before showing an empty
> form.
> > > I would rather display a msgbox.
> > >

> > > "John Viescas" <Jo...@nomail.please> wrote in message

> > > news:O7RbQ$KWAHA.248@cppssbbsa03...
> > > > A Requery should get you updated and added rows. The
> > > Form's
> > > > RecordsetClone.RecordCount property should be 0 if
> there's
> > > no rows.
> > > >

> > > > --
> > > > John Viescas, author
> > > > "Running Microsoft Access 2000"
> > > > "SQL Queries for Mere Mortals"
> > > > http://www.viescas.com/
> > > > "Torben Dahl Jensen" <t...@xife.dk> wrote in message

> > > > news:8vtu8c$17cu$1...@news.net.uni-c.dk...
> > > > > This is simply great!
> > > > >
> > > > > Thank you!
> > > > >
> > > > > I created:
> > > > >
> > > > > - one view (to retrieve the fields I need)
> > > > > - one stored procedure (to perform LEFT JOIN using
> the
> > > view
> > > > > and the two selected languages)
> > > > > - one pass through query (to call the sp)
> > > > >
> > > > > it works!
> > > > >
> > > > > But how do I refresh the records in the pass through
> > > query.
> > > > > For instance, if I edited or deleted a record?
> > > > >
> > > > > How can I test the the recordset is empty and
> display
> > > > > message to the user?
> > > > >
> > > > > Torben
> > > > >

> > > > > "John Viescas" <Jo...@nomail.please> wrote in
> message

> > > > > news:O6czXNn...@cppssbbsa02.microsoft.com...