The following code is stored in a procedure on SQL Server 2008 and is executed using ADODB in MS Access; I am receiving the following error when trying to execute it in Access...
Run-time error -2147217913(80040e07)
[Microsoft][ODBC SQL Server Driver][SQL Server]
Conversion failed when converting the nvarchar value 'fldNumericData' to data type int.
fldNumeric
holds only the values 0,1,2,3,4, and 5 but the following statement fails...
SELECT Order#, MachineID, Addr1,
Addr2, City, [State], Country, ZIP, Contact, Phone, Fax, email,
Product, CAST([fldNumeric] AS INT) AS [fldNumeric],
CAST([AutoSub] AS INT) AS [AutoSub], Mode, [FileName]
INTO #sRst1
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\database.mdb';
'admin';'',tempTable) LEFT JOIN FN_qryOne() ON fldMatch =
FN_qryOne.fldMatch AND fldCutOff = FN_qryOne.fldCutOff
ugly that you have to join to a function but try this: