I am using an Excel (2013) table in one workbook (Book A) and I am using SQL INSERT INTO to transfer a whole row of data from Workbook A to an identical Excel table in Workbook B. The workbook will usually be closed during this operation, so using db- connections (read and write) and SQL are the best way to do this. I am using native SQL engine in Excel, not external db engine.

The Excel spreadsheet in Workbook A has 73 fields and they contain a mix of text, numbers and dates (DMYHMS), although the spreadsheet is mostly set up for common formats. Workbook B is the central database for the collection of workbook A that connects to it. I am currently testing only one compound from Book A to Book B.

In Book A, when I execute SQL INSERT INTO

, I get an error:

"Data type mismatch in criteria expression". (Err = -2147217913)

After a thorough troubleshooting process, I can now conclude that one field (71st field) is emerging from workbook A that is causing the problem and that is "LASTMOD_BY" which is the user id, that is, "12345678", but which, by what For some unknown reason, Workbook B expects to be a date. This is especially odd since I have a SUB_BY (Submitted By) field which is also user id 12345678 and this is accepted as a text string. Why does the Excel spreadsheet then think that the same data for LASTMOD_BY must be a date ?!

Reading many posts here, most users seem to want the string to be a date: I want the string to stay as a string!

For the life of me, I've researched and tried everything to get around this problem, but Im out of ideas!

For the record, I've tried the following, with no success:

• Make sure Workbook A and Workbook B LASTMOD_BY are set to General
• Make sure Workbook A and Workbook B LASTMOD_BY are set to Text
• Execute the data in the LASTMOD_BY field in the Numeric workbook and then later on the text field (as a number, LASTMOD_BY throws out "Overflow error because the user ID is too large to be a date value).
• Copy another table field that is accepted as a text field into the LASTMOD_BY column and rename it as LASTMOD_BY and delete the old LASTMOD_BY column.
• Insert into workbook A and workbook B in another field called LASTMODBY in the column before LASTMOD_BY and supplying LASTMODBY with the user ID and excluding LASTMOD_BY from the SQL statement (Excel was still expecting LASTMODBY to become a date!)
• Testing all values ​​in table cells to ensure correct data type is found, and then make sure it is correctly reflected in SQL syntax (i.e. if the date is then formatted as "date", if the text format is as "text"; if the number format is as number (no single quotes))
• Missing LASTMOD_BY from INSERT INTO statement (which DID works) and then adding a separate UPDATE statement to set LASTMOD_BY (which DIDNT works).

How do I get the Excel table in workbook B to accept the user ID ('LASTMOD_BY) as text and not date?

This is driving me crazy and I am starting to conclude that Excel tables, when used with embedded SQL, have an error.

For those who need to see SQL, here it is:

INSERT INTO [CQDB$] (ProductQ, Version, QID_1, QID_2, QID_3, QID_4, QID_5, QID_6, QID_7, QID_8, QID_9, QID_10, QID_11, QID_12, QID_13, QID_14, QID_15, QID_16, QID_17, QID_18, QID_19, QID_20, QID_21, QID_22, QID_23, QID_24, QID_25, QID_26, QID_27, QID_28, QID_29, QID_30, QID_31, QID_32, QID_33, QID_34, QID_35, QID_36, QID_37, QID_38, QID_39, QID_40, QID_41, QID_42, QID_43, QID_44, QID_45, QID_46, QID_47, QID_48, QID_49, QID_50, QID_51, QID_52, QID_53, QID_54, QID_55, QID_56, QID_57, QID_58, QID_59, QID_60,  QID_61, QID_62, QID_63, QID_64, QID_65, SUB_DMYHM, SUB_BY, LASTMOD_DMYHM, LASTMOD_BY, RECSTATUS, SubMonth)
VALUES('NonBank', 6, 98765432, ‘Mr Smith',12348765,’My Insurance plan','0',’My Local Branch','0','0','29-Sep-16','30-Dec-09’, '0', '0', 'No', '0', '0', '0', '0', '0', '0', '0', 'No', 'Yes', '0', '0', '0', '0', '0', '0', 'N/A', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 12345678, ‘28-Sep-16 11:09:00', '0', '0', ‘28-Sep-16 11:09:29', 12345678, ‘28-Sep-16 11:09:29', 12345678, 'FAIL: To Follow-up',’01-Sep-16')

      

        
        
        
      

    

FYI SQL is built with VBA. What you see above is my edited version (to remove any real data). If you notice a missing / extra comma, etc., this is probably from my manual edit, not the generated SQL!

I really appreciate if anyone can work it out for me!

Greetings