MS ACCESS query returns empty recordset from odbc linked sql server tables

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,

I'm developing a VB6 application with a MS Access 2000 database (for
reporting purposes etc.) using an ODBC link. Some tables of it are linked
(another ODBC link) to a SQL Server 2005 database. I need to show some
records in my application coming from a MS Access query running on LINKED
tables (not imported tables) from the SQL database.

Problem: when I IMPORT the tables, recordset is complete, when I LINK the
tables, the recordset is empty (no records, only colum heads, no error
message). Probably this is caused by the differences in SQL syntax (thank
you Bill ;-)).

This is my query in Access:

SELECT
IIf([dbo_cicntp.cnt_email]="",LCase([dbo_cicmpy.cmp_e_mail]),LCase([dbo_cicntp.cnt_email]))
AS EMAIL, UCase(CStr([dbo_cicmpy.ID])) AS CODE, dbo_cicmpy.cmp_name AS
BEDRIJF, [cnt_l_name] & " " & [cnt_f_name] AS FAMILIENAAM, dbo_cicntp.Gender
AS MVJ,
IIf(UCase([dbo_cicntp.taalcode])="NL","NL",IIf(UCase([dbo_cicntp.taalcode])="FR","F","E"))
AS TAAL, IIf([dbo_cicntp].[active_y]=1,True,False) AS ACTIEF,
dbo_cicmpy.SubSector AS VESTIG, "CATEGORIE" AS CAT, dbo_cicntp.cnt_f_tel AS
TEL, Trim([dbo_cicmpy.cmp_fadd1] & " " & [dbo_cicmpy.cmp_fadd2] & " " &
[dbo_cicmpy.cmp_fadd3]) AS STRAAT, dbo_cicmpy.cmp_fpc AS POSTNR,
dbo_cicmpy.cmp_fcity AS GEMEENTE, dbo_cicmpy.cmp_fctry AS LAND, etc...
FROM (dbo_cicmpy INNER JOIN dbo_cicntp ON dbo_cicmpy.cnt_id =
dbo_cicntp.cnt_id) INNER JOIN dbo_pred ON dbo_cicntp.predcode =
dbo_pred.predcode
WHERE (((dbo_cicmpy.DivisionCreditorID) Is Null));


Is there any solution?

Could this be a solution: make an first query to the SQL database just to
gather the "clean" fields (I mean, without IIFs, Lcases, Ucases and other
functions unknown by SQL Server I use now) and then adapt my query here
above mentioned, and apply my IIF, Lcases and Ucases on the recordset
returned by my first query?

Thanks a lot for your time.

Mark
 
Instead of directly accessing a SQL-Server, you are doing so through the JET
database engine through a MDF file with ODBC linked tables. Not only this
is performance hole, joigning one above the other unecessary and inefficient
layers but this strange method was also known in the past for its numerous
problems like the one you have.

However, as not only VB6 is now a thing of the past but the method that you
are using is even archaic - I remember people suggesting this method to
access a SQL-Server with VB5 - I've totally forgot what was the solution to
this particular problem; is there is one.

I would suggest that you forget about using this strange method of using an
intermediary MDB file and that you make the request(s) directly to the
SQL-Server without bothering about the JET engine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Mark said:
Hi,

I'm developing a VB6 application with a MS Access 2000 database (for
reporting purposes etc.) using an ODBC link. Some tables of it are linked
(another ODBC link) to a SQL Server 2005 database. I need to show some
records in my application coming from a MS Access query running on LINKED
tables (not imported tables) from the SQL database.

Problem: when I IMPORT the tables, recordset is complete, when I LINK the
tables, the recordset is empty (no records, only colum heads, no error
message). Probably this is caused by the differences in SQL syntax (thank
you Bill ;-)).

This is my query in Access:

SELECT
IIf([dbo_cicntp.cnt_email]="",LCase([dbo_cicmpy.cmp_e_mail]),LCase([dbo_cicntp.cnt_email]))
AS EMAIL, UCase(CStr([dbo_cicmpy.ID])) AS CODE, dbo_cicmpy.cmp_name AS
BEDRIJF, [cnt_l_name] & " " & [cnt_f_name] AS FAMILIENAAM,
dbo_cicntp.Gender
AS MVJ,
IIf(UCase([dbo_cicntp.taalcode])="NL","NL",IIf(UCase([dbo_cicntp.taalcode])="FR","F","E"))
AS TAAL, IIf([dbo_cicntp].[active_y]=1,True,False) AS ACTIEF,
dbo_cicmpy.SubSector AS VESTIG, "CATEGORIE" AS CAT, dbo_cicntp.cnt_f_tel
AS
TEL, Trim([dbo_cicmpy.cmp_fadd1] & " " & [dbo_cicmpy.cmp_fadd2] & " " &
[dbo_cicmpy.cmp_fadd3]) AS STRAAT, dbo_cicmpy.cmp_fpc AS POSTNR,
dbo_cicmpy.cmp_fcity AS GEMEENTE, dbo_cicmpy.cmp_fctry AS LAND, etc...
FROM (dbo_cicmpy INNER JOIN dbo_cicntp ON dbo_cicmpy.cnt_id =
dbo_cicntp.cnt_id) INNER JOIN dbo_pred ON dbo_cicntp.predcode =
dbo_pred.predcode
WHERE (((dbo_cicmpy.DivisionCreditorID) Is Null));


Is there any solution?

Could this be a solution: make an first query to the SQL database just to
gather the "clean" fields (I mean, without IIFs, Lcases, Ucases and other
functions unknown by SQL Server I use now) and then adapt my query here
above mentioned, and apply my IIF, Lcases and Ucases on the recordset
returned by my first query?

Thanks a lot for your time.

Mark
Click to expand...
 
In addition to what Sylvain said: you are also making a big error of
mixing presentation logic with database logic. Rather you should query
the db without LCase, IIf, etc, and leave that up to the middle tier
(if you have one) or the presentation layer.

-Tom.
 
Supplemental information: many people have used this method in the past, so
I suppose that fundamentally there is nothing wrong with it. However, I
remember seeing reports from peoples having exactly the same problem as you;
so either they were all making the same fundamental error (permission
problem, login problem (maybe using a SQL-Server instead of a Windows
account could help?), DSN problem, etc.) or there has been a change with one
of the service packs from MS that has bring up this problem in the recent
years.

Suggestions:

1- first try with an easy query (without any VBA functions). The use of
VBA functions might be the culprit here, especially if the MDB file has not
been recompiled since the latest service pack or if it's coming from another
Windows installation.

2- Take a look at the connection string and see if the use of another
ODBC provider or of another account could solve the problem.

3- Revise your expections about accessing any kind of database used by
the clients. You are dealing with old stuff here, so don't ask to much.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Sylvain Lafontaine said:
Instead of directly accessing a SQL-Server, you are doing so through the
JET database engine through a MDF file with ODBC linked tables. Not only
this is performance hole, joigning one above the other unecessary and
inefficient layers but this strange method was also known in the past for
its numerous problems like the one you have.

However, as not only VB6 is now a thing of the past but the method that
you are using is even archaic - I remember people suggesting this method
to access a SQL-Server with VB5 - I've totally forgot what was the
solution to this particular problem; is there is one.

I would suggest that you forget about using this strange method of using
an intermediary MDB file and that you make the request(s) directly to the
SQL-Server without bothering about the JET engine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Mark said:
Hi,

I'm developing a VB6 application with a MS Access 2000 database (for
reporting purposes etc.) using an ODBC link. Some tables of it are linked
(another ODBC link) to a SQL Server 2005 database. I need to show some
records in my application coming from a MS Access query running on LINKED
tables (not imported tables) from the SQL database.

Problem: when I IMPORT the tables, recordset is complete, when I LINK the
tables, the recordset is empty (no records, only colum heads, no error
message). Probably this is caused by the differences in SQL syntax (thank
you Bill ;-)).

This is my query in Access:

SELECT
IIf([dbo_cicntp.cnt_email]="",LCase([dbo_cicmpy.cmp_e_mail]),LCase([dbo_cicntp.cnt_email]))
AS EMAIL, UCase(CStr([dbo_cicmpy.ID])) AS CODE, dbo_cicmpy.cmp_name AS
BEDRIJF, [cnt_l_name] & " " & [cnt_f_name] AS FAMILIENAAM,
dbo_cicntp.Gender
AS MVJ,
IIf(UCase([dbo_cicntp.taalcode])="NL","NL",IIf(UCase([dbo_cicntp.taalcode])="FR","F","E"))
AS TAAL, IIf([dbo_cicntp].[active_y]=1,True,False) AS ACTIEF,
dbo_cicmpy.SubSector AS VESTIG, "CATEGORIE" AS CAT, dbo_cicntp.cnt_f_tel
AS
TEL, Trim([dbo_cicmpy.cmp_fadd1] & " " & [dbo_cicmpy.cmp_fadd2] & " " &
[dbo_cicmpy.cmp_fadd3]) AS STRAAT, dbo_cicmpy.cmp_fpc AS POSTNR,
dbo_cicmpy.cmp_fcity AS GEMEENTE, dbo_cicmpy.cmp_fctry AS LAND, etc...
FROM (dbo_cicmpy INNER JOIN dbo_cicntp ON dbo_cicmpy.cnt_id =
dbo_cicntp.cnt_id) INNER JOIN dbo_pred ON dbo_cicntp.predcode =
dbo_pred.predcode
WHERE (((dbo_cicmpy.DivisionCreditorID) Is Null));


Is there any solution?

Could this be a solution: make an first query to the SQL database just to
gather the "clean" fields (I mean, without IIFs, Lcases, Ucases and other
functions unknown by SQL Server I use now) and then adapt my query here
above mentioned, and apply my IIF, Lcases and Ucases on the recordset
returned by my first query?

Thanks a lot for your time.

Mark
Click to expand...
Click to expand...
 
Back
Top