Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-16-2018, 03:57 PM   #1
lemondrop9344
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lemondrop9344 is on a distinguished road
Access2010 & sqle 3.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.

lemondrop9344 is offline   Reply With Quote
Old 02-17-2018, 02:54 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,953
Thanks: 38
Thanked 3,224 Times in 3,124 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access2010 & sqle 3.5

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
lemondrop9344 (02-17-2018)
Old 02-17-2018, 06:32 AM   #3
lemondrop9344
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lemondrop9344 is on a distinguished road
Re: Access2010 & sqle 3.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.

lemondrop9344 is offline   Reply With Quote
Old 02-17-2018, 10:57 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,095
Thanks: 2
Thanked 1,891 Times in 1,849 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Access2010 & sqle 3.5

Is [SERIALNUMBERTYPE] a number field type or a text field type?
Then you're comparing it with a text type [SERIALNUMBERTYPE]="2"
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 02-18-2018, 01:35 AM   #5
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Access2010 & sqle 3.5

Quote:
Originally Posted by JHB View Post
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",([SERIALNUMBER]+2684354560),IIf([SERIALNUMBERTYPE]="3",([SERIALNUMBER]+2688876544),"OOOPS"))
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 02-18-2018, 07:30 AM   #6
lemondrop9344
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lemondrop9344 is on a distinguished road
Re: Access2010 & sqle 3.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.
lemondrop9344 is offline   Reply With Quote
Old 02-23-2018, 11:37 AM   #7
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,073
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Access2010 & sqle 3.5

lemondrop9344,

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

hth,
Wayne

__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 02-23-2018, 04:10 PM   #8
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 569
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: Access2010 & sqle 3.5

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.

kevlray is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pass a value to a combobox on load? (Access2010) Apc0243 Forms 9 02-18-2015 02:57 PM
Hello,access2010 is inconvenient? kinkon General 1 02-10-2014 03:15 AM
Access2010 to Sharepoint2010 via ODBC? bayman General 3 07-30-2013 05:39 PM
Access2007 VBA code in Access2010 Mist Forms 1 03-26-2013 09:38 AM
Autonumber is out of Sync after upgrading to access2010 magcsd General 4 08-17-2012 07:28 PM




All times are GMT -8. The time now is 11:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World