(Access/SQL Server) How to prevent Access changing my SQL?

[Hugo Kornelis]

Hi,

Using SQL Server 2000 as backend and Access 97 as frontend.

In my Access database, I set up a linked table to a (updatable) view in
SQL Server 2000. The name of the view in the backend is XXX (names have
been changed to protect the innocent) and the name of the linked table is
dbo_XXX. The columns in the view (and in the linked table) are KeyCol1,
KeyCol2, DataCol1 and DataCol2. KeyCol2 is integer, the other columns hold
character data. Both DataCol1 and DataCol2 alllow NULL values.

In one of my OnClick events, I use the data in the form to build an SQL
string and use "CurrentDb.Execute SQL" te execute it. Using the debugger,
I checked that the string variable SQL contains this text:
INSERT INTO dbo_XXX (KeyCol1, KeyCol2, DataCol1) VALUES('test3', 3, '')
Note: I use an empty string for DataCol1, not NULL!!

However, when I run a profiler trace to see what Access actually sends to
the SQL Server backend database, I see the following command:
exec sp_executesql N'INSERT INTO "dbo"."XXX" ("KeyCol1","KeyCol2")
VALUES (@P1,@P2)', N'@P1 varchar(40),@P2 int', 'test3', 3

As you can see, there is no DataCol1 and no empty string in the SQL. The
result is that the value inserted for DataCol1 is NULL instead of '' (the
empty string).

I already did some digging around, but I could not find any setting to
tell Access not to change empty strings to NULL without warning. Did I
miss the option or does it not exist? And if the latter, are there any
workarounds? I really want to be able to insert empty strings in the
table!

Best, Hugo

 

[Rick Brandt]

Hi,

Using SQL Server 2000 as backend and Access 97 as frontend.

In my Access database, I set up a linked table to a (updatable) view in
SQL Server 2000. The name of the view in the backend is XXX (names have
been changed to protect the innocent) and the name of the linked table is
dbo_XXX. The columns in the view (and in the linked table) are KeyCol1,
KeyCol2, DataCol1 and DataCol2. KeyCol2 is integer, the other columns hold
character data. Both DataCol1 and DataCol2 alllow NULL values.

In one of my OnClick events, I use the data in the form to build an SQL
string and use "CurrentDb.Execute SQL" te execute it. Using the debugger,
I checked that the string variable SQL contains this text:
INSERT INTO dbo_XXX (KeyCol1, KeyCol2, DataCol1) VALUES('test3', 3, '')
Note: I use an empty string for DataCol1, not NULL!!

However, when I run a profiler trace to see what Access actually sends to
the SQL Server backend database, I see the following command:
exec sp_executesql N'INSERT INTO "dbo"."XXX" ("KeyCol1","KeyCol2")
VALUES (@P1,@P2)', N'@P1 varchar(40),@P2 int', 'test3', 3

As you can see, there is no DataCol1 and no empty string in the SQL. The
result is that the value inserted for DataCol1 is NULL instead of '' (the
empty string).

I already did some digging around, but I could not find any setting to
tell Access not to change empty strings to NULL without warning. Did I
miss the option or does it not exist? And if the latter, are there any
workarounds? I really want to be able to insert empty strings in the
table!

Instead of running the query against the link, create a Pass-Through query and
have it processed directly on the server. Then Access/Jet never touches your
SQL at all.

 

[Hugo Kornelis]

Instead of running the query against the link, create a Pass-Through query and
have it processed directly on the server. Then Access/Jet never touches your
SQL at all.

Hi Rick,

Thanks! That should do the trick. At the price of periodic compressing the
database, but I can live with that.

Best, Hugo