Webmaster Forum / ASP / Database Access / February 2008

Greetings, folks.

I'm pretty well at my wit's end and feel like setting fire to the server in
an effort to destroy all traces of legacy code (alas, backups would prevent
this solution from being feasible)...

I've a suspicion that OLEDB is having an issue with too long a string being
passed to it as a query, but I'm hoping someone here'll be in a better
position to offer a conclusive judgement.

The INSERT query below works fine when using an archaic ODBC connection to
MS Access, but fails when using an OLEDB connection, returning the error
"Microsoft JET Database Engine error '80040e14' - Syntax error in INSERT
INTO statement." It also works perfectly well when entered directly into the
Query window of MS Access.

Should I revert to the ODBC connection method? Will anything else break if I
do? Or is there a simple work-around to overcome this problem whilst
continuing with the existing OLEDB connection method?

Thanks in advance for any and all help.

The query (ignore the silly sample values in the text fields):
--------------------
INSERT INTO
 HRRequests
   (
  Employee,
  Requested,
  IP,
  Position,
  Department,
  Office,
  ReplacementFor,
  Budgeted,
  NewPosition,
  EmploymentType,
  EmploymentTenure,
  DaysRequired,
  Qualifications,
  Skills,
  Accountabilities,
  StartDate,
  HandoverPeriod,
  HandoverType,
  LaptopRequired,
  DesktopRequired,
  ComputerRequirements,
  NetworkRequired,
  EmailRequired,
  NetworkSecurity,
  MobilePhoneRequired,
  MiscRequirements,
  SalaryMin,
  SalaryMax,
  RecommendInternal,
  Active
 )
VALUES
 (
   '{E3417C2F-3CE9-462A-AEA7-70354D70A138}',
   #05-Dec-2007 9:57:15 AM#,
   '127.0.0.1',
   '{23C57CA5-2139-48C0-969A-193FF2946E1C}',
   '{5AB5099D-9A6D-4DFD-9A11-8C46339EA8AD}',
   '{AC0D8315-5A7A-400D-9EA4-D2E9A7032D9E}',
   '{65DE6A84-500D-486F-AA90-AE37756BA15D}',
   1,
   0,
   '{CC074A75-2F62-4496-AE4C-E19237690114}',
   '{9DC54BC9-5265-44A5-A192-93DF05FA18BA}',
   0,
   'NSW HSC',
   'Must be able to talk whilst walking, without tripping',
   'Replacing water cooler bottle',
   #10-Dec-2007#,
   0,
   1,
   0,
   1,
   'Needs left-handed computer',
   1,
   1,
   'Needs SuperUser access',
   1,
   'Pink cubicle',
   50000,
   60000,
   '{72A62ECB-4527-4736-88A9-D5C14C78EE4C}',
   1
 );
--------------------

The OLEDB connection:
--------------------
cDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxxxx;Jet
OLEDB:Database Password=yyyyy;"
--------------------

The ODBC connection:
--------------------
cDB.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=xxxxx;
password=yyyyy"
--------------------