lemondrop9344
New member
- Local time
- Today, 14:03
- 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:
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: