Access97 Queries connecting via ODBC to Sybase SQLanywhere backend

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

I have a Access97 frontend database and it links tables and queries to a
Sybase SQLAnywhere backend database. I have imported all the tables from
the SQLAnywhere into a Access97 database. I have changed the linked
relationship in the tables section to point to the Access97 backend now and
not the Sybase backend. Problem is I have SQL queries written in the Access
97 frontend that still try to access Sybase via ODBC. How do I change these
queries to access the new Backend.

Jamie
Network Administrator
 
If the queries were originally written using linked tables, they should
still work. However, if you were using Passthrough queries directly to the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
 
These are SQL Passthrough Queries. Can you tell me an easy way to recreate
these? There are many of them per frontend database and I have roughly 6
Frontends.


Jamie
 
For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc #"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie
 
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change the "
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc #]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John,
First thank you for all your help so far!
I'm not the original programmer of this database and the original has been
gone for awhile. We had a different programmer that worked on it but he is
now gone also. I have been stuck with this thing. So what we are doing is
eliminating the Sybase backend (in 2 months) because it runs on Novell and
we are going to MS server 2003. We are currently have a 3rd party vendor
application that we are setting up to replace the complete Front end and
back end but I'm afraid it will not be complete in time and want to move
this to an Access back end so that it will still function after the Server
change until we get the new application in.
As far as why certain things are the way they are in the database queries I
can't answer this because I have never been involved in the programming of
this.

What do ExprN Clauses do for the query?

Jamie


John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change the "
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc #]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Jamie said:
For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc #"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

to
the
Click to expand...
to
a
Click to expand...
Click to expand...
 
John,
I made all the changes as you stated below and now I get an error:
ORDER BY ? (MCP_Associate_Combobox.M) ? DISTINCT ?????????

If I run the old passthrough query it works just fine.

Jamie

John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change the "
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc #]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Jamie said:
For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc #"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

to
the
Click to expand...
to
a
Click to expand...
Click to expand...
 
The AS ExprN assigns an alias name to the column. You'll need to keep those
only if the form or report that uses this query expects the alias names. It
sounds like this is the row source for a combo box, so the AS clauses are
superfluous.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Jamie said:
John,
First thank you for all your help so far!
I'm not the original programmer of this database and the original has been
gone for awhile. We had a different programmer that worked on it but he is
now gone also. I have been stuck with this thing. So what we are doing is
eliminating the Sybase backend (in 2 months) because it runs on Novell and
we are going to MS server 2003. We are currently have a 3rd party vendor
application that we are setting up to replace the complete Front end and
back end but I'm afraid it will not be complete in time and want to move
this to an Access back end so that it will still function after the Server
change until we get the new application in.
As far as why certain things are the way they are in the database queries I
can't answer this because I have never been involved in the programming of
this.

What do ExprN Clauses do for the query?

Jamie


John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change
Click to expand...
the
"
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc #]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Jamie said:
For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID,
Click to expand...
MCP_Associate_Combobox."Assoc
#"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

If the queries were originally written using linked tables, they should
still work. However, if you were using Passthrough queries directly to
the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I have a Access97 frontend database and it links tables and
Click to expand...
Click to expand...
queries
to backend
now
Click to expand...
Click to expand...
 
Well, I bet there's not a column named "M" in that table. I see the query
is returning the MI column, so perhaps changing it to Order By
MCP_Associate_Combobox.MI will fix it.

After you fix the SQL, you should be able to switch to the query design grid
to get a better picture of what's really happening.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Jamie said:
John,
I made all the changes as you stated below and now I get an error:
ORDER BY ? (MCP_Associate_Combobox.M) ? DISTINCT ?????????

If I run the old passthrough query it works just fine.

Jamie

John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change
Click to expand...
the
"
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc #]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Jamie said:
For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID,
Click to expand...
MCP_Associate_Combobox."Assoc
#"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

If the queries were originally written using linked tables, they should
still work. However, if you were using Passthrough queries directly to
the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I have a Access97 frontend database and it links tables and
Click to expand...
Click to expand...
queries
to backend
now
Click to expand...
Click to expand...
 
This was the problem.
Thank you. I have one more question about Expr I will post it off of that
topic.

Jamie

John Viescas said:
Well, I bet there's not a column named "M" in that table. I see the query
is returning the MI column, so perhaps changing it to Order By
MCP_Associate_Combobox.MI will fix it.

After you fix the SQL, you should be able to switch to the query design grid
to get a better picture of what's really happening.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Jamie said:
John,
I made all the changes as you stated below and now I get an error:
ORDER BY ? (MCP_Associate_Combobox.M) ? DISTINCT ?????????

If I run the old passthrough query it works just fine.

Jamie

John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change
Click to expand...
the
"
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID,
Click to expand...
MCP_Associate_Combobox.[Assoc
#]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc
#"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

If the queries were originally written using linked tables, they should
still work. However, if you were using Passthrough queries
Click to expand...
directly
to
the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I have a Access97 frontend database and it links tables and
Click to expand...
queries
to
a
Sybase SQLAnywhere backend database. I have imported all the tables
from
the SQLAnywhere into a Access97 database. I have changed the linked
relationship in the tables section to point to the Access97 backend
now
and
not the Sybase backend. Problem is I have SQL queries written
Click to expand...
in
the
Access
97 frontend that still try to access Sybase via ODBC. How do I change
these
queries to access the new Backend.

Jamie
Network Administrator
Click to expand...
Click to expand...
Click to expand...
 
Now that the statement works there is still one minor difference. When I
run the Access query it has the Expr as the column titles. When I run the
SQL passthrough it shows the actual column name.

Example: SQL Passthrough = Assoc #
Access query = Expr1

Could this pose a problem?


John Viescas said:
The AS ExprN assigns an alias name to the column. You'll need to keep those
only if the form or report that uses this query expects the alias names. It
sounds like this is the row source for a combo box, so the AS clauses are
superfluous.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Jamie said:
John,
First thank you for all your help so far!
I'm not the original programmer of this database and the original has been
gone for awhile. We had a different programmer that worked on it but he is
now gone also. I have been stuck with this thing. So what we are doing is
eliminating the Sybase backend (in 2 months) because it runs on Novell and
we are going to MS server 2003. We are currently have a 3rd party vendor
application that we are setting up to replace the complete Front end and
back end but I'm afraid it will not be complete in time and want to move
this to an Access back end so that it will still function after the Server
change until we get the new application in.
As far as why certain things are the way they are in the database
Click to expand...
queries
I
can't answer this because I have never been involved in the programming of
this.

What do ExprN Clauses do for the query?

Jamie


John Viescas said:
That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new query,
switch to SQL View, paste in the SQL from the passthrough, and change
Click to expand...
the
"
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID,
Click to expand...
MCP_Associate_Combobox.[Assoc
#]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc
#"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

If the queries were originally written using linked tables, they should
still work. However, if you were using Passthrough queries
Click to expand...
directly
to
the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I have a Access97 frontend database and it links tables and
Click to expand...
queries
to
a
Sybase SQLAnywhere backend database. I have imported all the tables
from
the SQLAnywhere into a Access97 database. I have changed the linked
relationship in the tables section to point to the Access97 backend
now
and
not the Sybase backend. Problem is I have SQL queries written
Click to expand...
in
the
Access
97 frontend that still try to access Sybase via ODBC. How do I change
these
queries to access the new Backend.

Jamie
Network Administrator
Click to expand...
Click to expand...
Click to expand...
 
Yes, if whatever in the application that is using this query expects the
real names. Get rid of the AS clauses, and you'll be all set.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Jamie said:
Now that the statement works there is still one minor difference. When I
run the Access query it has the Expr as the column titles. When I run the
SQL passthrough it shows the actual column name.

Example: SQL Passthrough = Assoc #
Access query = Expr1

Could this pose a problem?


John Viescas said:
The AS ExprN assigns an alias name to the column. You'll need to keep those
only if the form or report that uses this query expects the alias names. It
sounds like this is the row source for a combo box, so the AS clauses are
superfluous.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
he
is doing
is queries
Click to expand...
programming
of
this.

What do ExprN Clauses do for the query?

Jamie


That helps... <s>

If MCP_Associate_Combobox is now a linked table, you can start a new
query,
switch to SQL View, paste in the SQL from the passthrough, and
Click to expand...
change
the
"
delimiters around field names to [ and ]. For example,

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox.[Assoc
#]
AS Expr1, MCP_Associate_Combobox.[Last Name] AS Expr2,
MCP_Associate_Combobox.[First Name] AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox.[Position Title] AS Expr4,
MCP_Associate_Combobox.[Assoc Grp] AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox.[Assoc #],
MCP_Associate_Combobox.[Last Name], MCP_Associate_Combobox.[First Name],
MCP_Associate_Combobox.M;

But I'm wondering why there are all the "ExprN" clauses. And what is
field
"M" (referenced in the Order By)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

For example here is one of the SQL Passthrough commands.

SELECT DISTINCT MCP_Associate_Combobox.ID, MCP_Associate_Combobox."Assoc
#"
AS Expr1, MCP_Associate_Combobox."Last Name" AS Expr2,
MCP_Associate_Combobox."First Name" AS Expr3, MCP_Associate_Combobox.MI,
MCP_Associate_Combobox."Position Title" AS Expr4,
MCP_Associate_Combobox."Assoc Grp" AS Expr5
FROM MCP_Associate_Combobox
ORDER BY MCP_Associate_Combobox."Assoc #", MCP_Associate_Combobox."Last
Name", MCP_Associate_Combobox."First Name", MCP_Associate_Combobox.M;

Jamie

If the queries were originally written using linked tables, they
should
still work. However, if you were using Passthrough queries directly
to
the
back end, you'll have to rewrite them as regular Access queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I have a Access97 frontend database and it links tables and queries
to
a
Sybase SQLAnywhere backend database. I have imported all the tables
from
the SQLAnywhere into a Access97 database. I have changed the linked
relationship in the tables section to point to the Access97 backend
now
and
not the Sybase backend. Problem is I have SQL queries written in
the
Access
97 frontend that still try to access Sybase via ODBC. How do I
change
these
queries to access the new Backend.

Jamie
Network Administrator
Click to expand...
Click to expand...
Click to expand...
 
Back
Top