oledbprovider vs odbc provider ???

[serge calderara]

Dear all,

Does any one have any idea why an sql statment with INNER
JOIN syntax is working well with odbcprovider but not with
Oledbprovider when accessing an access 2000 database?

here is the code I have used with oledbprovider which fails

Dim monodbc As New
OleDb.OleDbConnection 'Odbc.OdbcConnection
monodbc.ConnectionString = ("Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDBatabase Locking Mode=1;Data Source='D:\Nomos Net
Suite\Runtime\Config\NetDBConf.mdb';Jet OLEDB:Engine
Type=5;Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:System
database=;Jet OLEDB:SFP=False;persist security
info=False;Extended Properties=;Mode=Share Deny None;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Global Bulk
Transactions=1")

'"MaxBufferSize=2048;FIL=MS
Access;DSN=TRYME;PageTimeout=5;UID=admin;DBQ=D:\Nomos Net
Suite\Runtime\Config\NetDBConf.mdb;DriverId=25"

monodbc.Open()
Dim momo As OleDb.OleDbDataAdapter
momo = New OleDb.OleDbDataAdapter
Dim sds As OleDb.OleDbCommand

sds = New OleDb.OleDbCommand(Query, monodbc)
momo.SelectCommand = sds
Return momo.SelectCommand.ExecuteReader()

if I use exactly the same thing but with odbcprovider no
problem works ok.

regards
serge

 

[Carlos J. Quintero]

You have not posted the query text or the error message, but:

- The .NET Data Provider for OLE DB uses the Jet OLEDB Data Provider
- The .NET Data Provider for ODBC uses the Access ODBC Driver

The Access ODBC Driver and Jet OLEDB Data Provider are diferent components
that can parse differently the SQL statements (or to have its own bugs). A
known issue is that while the Access ODBC Driver accepts ODBC syntax (for
outer joins, for example), the Jet OLEDB Data Provider does not (other OLEDB
Providers accept ODBC syntax, though).

So, you could reduce your query text to a minimum and post it here for
examination of others...

Carlos Quintero

 

[calderara serge]

here is the text queery I am using and that I forgot to join here:

SELECT USER_PARAM.*, LANGUAGE.NAME
FROM USER_PARAM INNER JOIN [LANGUAGE] ON USER_PARAM.LANGUAGE_ID =
LANGUAGE.ID;

thnaks for your comments
regards
serge

 

[calderara serge]

We have made some more testing on passing SQL synthax with inner join
statement and the only way we make it work was to use an odbcprovider.
No way to make it work with oledbprovider.

Do u have any idea if it is a know issue as we use to call it "bug".

thanks for your reply
regards
Serge

 

[Carlos J. Quintero]

I have tested it on my system and my results are:

1) If you use that query, it only works with the ODBC Driver. Using the
OLEDB Provider, it returns an error opening the recordset. This seems to be
your case.
2) If you use LANGUAGE2 as the name of the table instead of LANGUAGE, it
works fine with both providers.
3) If you use [LANGUAGE] in each occurrence of this word in the statement,
that is:

SELECT USER_PARAM.*, [LANGUAGE].NAME
FROM USER_PARAM INNER JOIN [LANGUAGE] ON USER_PARAM.LANGUAGE_ID =
[LANGUAGE].ID;

instead of your original statement (which misses 2 occurrences), it works
fine with both providers.

HTH,

Carlos Quintero

 

[calderara serge]

Hello carlos,

thnaks so much it works ok now.
But I would like to clarify my mind.

Does it means that LANGUAGE is a reserved word somehow in .NEt and it
cannot be used as a table name?

Why if I just make a simple querry to LANGUAGE table only like SELECT *
FROM LANGUAGE it works ok?

thnaks for your answer
serge

 

[Paul Clement]

¤
¤ Hello carlos,
¤
¤ thnaks so much it works ok now.
¤ But I would like to clarify my mind.
¤
¤ Does it means that LANGUAGE is a reserved word somehow in .NEt and it
¤ cannot be used as a table name?
¤
¤ Why if I just make a simple querry to LANGUAGE table only like SELECT *
¤ FROM LANGUAGE it works ok?

Language is a reserved word in both Jet and ODBC, but apparently is being ignored in your ODBC
example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappcpr_17.asp

You can use Language as a table name but as Carlos indicated it must be enclosed within brackets for
every occurrence in the SQL statement. It's typically best not to use reserved words to avoid these
types of problems.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)