Access2010 & sqle 3.5 (1 Viewer)

lemondrop9344

New member
Local time
Today, 15:29
Joined
Feb 11, 2018
Messages
5
I'm trying to learn while doing, please humor me.

I have a SQLE 3.5 database. Presently I go through a process of exporting the tables into .xlsx format, link to the tables in Access 2010 and generate the results. Access 2010 does not have an OBDC driver for SQLE 3.5 built in. I have not yet succeeded in getting the MS Server & Visual Studio things to work. Consequently, I'm using another after market product to export the data to xlxs format.

In so much as Access utilizes SQL in the background, I thought I would be clever and cut & past the Access generated SQL query and use that to create the results. The Access generated SQL is reflected below.

SELECT PROPERTY.Code, PROPERTY.Name, BUILDING.Name, UNIT.Name, PROPERTY.LastSynchronizedOn,
PROPERTY.SerialNumberType, METER.CountFactor, TRANSMITTER.SerialNumber,
IIf([SERIALNUMBERTYPE]="2",([SERIALNUMBER]+2684354560),IIf([SERIALNUMBERTYPE]="3",([SERIALNU
MBER]+2688876544),"OOOPS")) AS TXIDVAL, CStr([TXIDVAL]) AS TXID, CHECKINSTATE.Received
FROM PROPERTY INNER JOIN ((((BUILDING INNER JOIN UNIT ON BUILDING.ID = UNIT.BuildingID) INNER
JOIN METER ON UNIT.ID = METER.UnitID) INNER JOIN TRANSMITTER ON METER.ID =
TRANSMITTER.MeterID) INNER JOIN CHECKINSTATE ON TRANSMITTER.ID = CHECKINSTATE.TransmitterID)
ON PROPERTY.ID = BUILDING.PropertyID
ORDER BY PROPERTY.Name, BUILDING.Name, UNIT.Name;

When I execute the query, this is the error message I receive.

[SQL Server Compact ADO.NET Data Provider]
HResult: -2147217900, NativeError: 25501
ErrorMessage: There was an error parsing the query. [ Token line number = 3,Token line offset = 23,Token in error = = ]

My research seems to indicate there is some sort of translation error from what ever Access is using as SQL & the 3.5 version of the data base. Trying to find a document which outlines 3.5 compact SQL syntax provides a variety of links which do not exist any longer.

Again, I ask for your understanding and patience as I just started down this SQL path a few days ago.:banghead:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:29
Joined
Feb 19, 2013
Messages
16,553
your error message is telling you where the error is - line 3, 23 characters in and an = sign.

My guess is it is the iif which is cause the problem. iif is a vba function and although available in transact sql from version 2012 it is unlikely to be in sqle. so you need to change it to the sqle equivalent.

I'm not familiar with sqle so cannot advise, but in sql you can also use the case function. here is a link https://www.w3schools.com/sql/func_mysql_case.asp.
 

lemondrop9344

New member
Local time
Today, 15:29
Joined
Feb 11, 2018
Messages
5
CJ, appreciate the feedback.
As you indicated, I need to find the sqle equivalent.... which has been somewhat challenging.
The applications generating the data I'm trying to get at were developed years ago.
 

JHB

Have been here a while
Local time
Today, 23:29
Joined
Jun 17, 2012
Messages
7,732
Is [SERIALNUMBERTYPE] a number field type or a text field type?
Then you're comparing it with a text type [SERIALNUMBERTYPE]="2"
 

isladogs

MVP / VIP
Local time
Today, 22:29
Joined
Jan 14, 2017
Messages
18,186
Is [SERIALNUMBERTYPE] a number field type or a text field type?
Then you're comparing it with a text type [SERIALNUMBERTYPE]="2"

Good point
If it's a number then it should be [SERIALNUMBERTYPE]=2.

In which case, it will then error at [SERIALNUMBERTYPE]="3"
Change both and it may be solved

The sums in the IIf expression certainly suggests it is a number
Code:
IIf([SERIALNUMBERTYPE]="2",([COLOR="DarkRed"][SERIALNUMBER]+2684354560[/COLOR]),IIf([SERIALNUMBERTYPE]="3",([COLOR="darkred"][SERIALNUMBER]+2688876544[/COLOR]),"OOOPS"))
 

lemondrop9344

New member
Local time
Today, 15:29
Joined
Feb 11, 2018
Messages
5
Gentlemen,

Appreciate your help. SerialNumberType is a string value & not a numeric value (it's text not a number).

The query works in Access 2010 & produces the desired results. I was trying to find a 'quick & easy' way to generate the query in SQL CE 3.5. I was hoping I could cut and paste the Access generated SQL syntax into SQL CE 3.5 & run the query there. NOT! Trying to find information on commands for SQL CE 3.5 has been an exercise in frustration. Any link I have found, seems to no longer exist. The product is no longer supported & it appears any links Microsoft had on line, have been removed. Microsoft, in all its infinite wisdom, has also removed the ODBC driver from Access.

As CJ pointed out, the issue appears to be associated with SQL CE 3.5 not supporting the IIF statement (there are probably others too).

What would really help at this point in time would be a viable link to the SQL CE 3.5 commands. Due to my 'newness' to SQL, I have created a convoluted way of extracting the data I need in Access.

Again, thanks for your input.
 

WayneRyan

AWF VIP
Local time
Today, 22:29
Joined
Nov 19, 2002
Messages
7,122
lemondrop9344,

If you're still around look up the documentation for the Case statement.

hth,
Wayne
 

kevlray

Registered User.
Local time
Today, 15:29
Joined
Apr 5, 2010
Messages
1,046
Also, I am not sure with SQLE, MS-SQL does like using double quotes for strings, instead use single quotes. The double quotes can be used for aliasing column names (i.e., "First Name") to overcome normal naming rules.
 

Users who are viewing this thread

Top Bottom