MS Access, ODBC, OLEDB and hair loss...

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"
--------------------

Re: MS Access, ODBC, OLEDB and hair loss...

Bob Milutinovic wrote:
> 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"
> --------------------
>

See this article for an impressive list of reserved words:
http://sqlserver2000.databases.aspfaq.com/what-are-reserved- access-odbc-and-sql-server-keywords.html

I'd guess that "Position" is a likely culprit.

This may also help:
http://databases.aspfaq.com/general/why-do-i-get-syntax-erro r-in-insert-into-statement-with-access.html

MC

Re: MS Access, ODBC, OLEDB and hair loss...

"MightyChaffinch" wrote in message
news:O2MSOgtNIHA.4176@TK2MSFTNGP02.phx.gbl...
> Bob Milutinovic wrote:
>>
>> 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.
>
> See this article for an impressive list of reserved words:
> http://sqlserver2000.databases.aspfaq.com/what-are-reserved- access-odbc-and-sql-server-keywords.html
>
> I'd guess that "Position" is a likely culprit.
>
> This may also help:
> http://databases.aspfaq.com/general/why-do-i-get-syntax-erro r-in-insert-into-statement-with-access.html

Many thanks for the heads-up. To confirm your suspicion, I went through and
enclosed each named field in brackets, and the problem disappeared when they
were applied to "Position."

Apart from the tunnel vision which usually develops when one's getting
ever-more frustrated by a problem, what threw me was the fact that the
connection method determined whether or not the query would work - and the
list of Keywords on Microsoft's site didn't include "Position."

Column renamed appropriately; thanks again for your help.

- Bob.

Re: MS Access, ODBC, OLEDB and hair loss...

"Bob Milutinovic" wrote in message
news:Oh94ettNIHA.5400@TK2MSFTNGP04.phx.gbl...
> "MightyChaffinch" wrote in message
> news:O2MSOgtNIHA.4176@TK2MSFTNGP02.phx.gbl...
>> Bob Milutinovic wrote:
>>>
>>> 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.
>>
>> See this article for an impressive list of reserved words:
>> http://sqlserver2000.databases.aspfaq.com/what-are-reserved- access-odbc-and-sql-server-keywords.html
>>
>> I'd guess that "Position" is a likely culprit.
>>
>> This may also help:
>> http://databases.aspfaq.com/general/why-do-i-get-syntax-erro r-in-insert-into-statement-with-access.html
>
> Many thanks for the heads-up. To confirm your suspicion, I went through
> and enclosed each named field in brackets, and the problem disappeared
> when they were applied to "Position."
>
> Apart from the tunnel vision which usually develops when one's getting
> ever-more frustrated by a problem, what threw me was the fact that the
> connection method determined whether or not the query would work - and the
> list of Keywords on Microsoft's site didn't include "Position."
>
> Column renamed appropriately; thanks again for your help.
>

Pay special attention to the list of reserved words in the Jet column. Or
learn them all off by heart, and never use any of them anywhere, ever :-)

--
Mike Brind