| dusleton@hotmail.com 2006-02-16, 6:55 pm |
| What is the syntax for concatenating fields within a SELECT statement
that's passed to an
Topspeed 2.0 ODBC driver via an ADO 2.7 Recordset object? Typical
Access approach does not work (as expected):
SELECT (TN.'FIELD1' & TN.'FIELD2') AS 'FIELD3' FROM TABLENAME
TN;
Background:
I'm currently importing 15 TPS files into 15 Access tables and into 15
SQLite3 tables using:
- a Topspeed 2.0 ODBC driver from an old Clarion/Topspeed database
system at work
- ADO 2.7 (Connection and Recordset objects)
Purpose:
I'm wanting to steamline the import process a tad more by merging
certain fields during the import, rather than using Access and SQLite 3
queries on the various tables once the data has been imported. Current
approach may already be best one?
What *Does* Work:
'An ODBC string is used through ADO to open a connection to the
Topspeed folder:
Dim rs As adodb.Recordset: Set rs = New adodb.Recordset
Dim cn As adodb.Connection: Set cn = New adodb.Connection
Dim sPath as String
sConn = "DRIVER={Topspeed ODBC Driver (Read-Only)};DBQ=" &
sPath &
" ;Datefield=%date%|%DOB%|%DOH%|%Pend%|CHA
NGED;" &
"Timefield=%START%|%STOP%"
cn.Open sConn
sql = "SELECT E.FULLNAME, E.SERIAL, E.PIN, " & _
"E.STATUS AS EMP_STATUS, H.DATE, H.STATUS AS
HIST_STATUS, " & _
"H.COMMENT, H.MEMO FROM " & _
"EMPLOYEE E, HISTORY H WHERE E.SERIAL=H.SERIAL " & _
"AND E.STATUS='A' AND H.DATE<{d '2006-01-01'} "
rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
What Does *Not* Work:
sql = "SELECT (E.'FULLNAME' & E.'SERIAL') AS 'NAMESERIAL',
E.PIN, " & _
"E.STATUS AS EMP_STATUS, H.DATE, H.STATUS AS
HIST_STATUS, " & _
"H.COMMENT, H.MEMO FROM " & _
"EMPLOYEE E, HISTORY H WHERE E.SERIAL=H.SERIAL " & _
"AND E.STATUS='A' AND H.DATE<{d '2006-01-01'} "
rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Resulting Error from What Does Not Work:
Run-time error '-2147217900 (80040e14)':
[Topspeed ODBC] Expected lexical element not found:
<identifier>
Notes:
* FULLNAME and SERIAL are both of Text/String datatype.
* Debug attempts include surrounding FULLNAME and SERIAL with
single quotes, and
using with and without parentheses.
|