asked on

Microsoft Access 2010, Update statement not updating SQL Server table

The following code does not update my SQL Server table.
dim db as database
set db = currentdb

Update tblInst_TimeSheet Set Date1 =#4/18/2014#, Date2 =#4/19/2014#, Date3 =#4/20/2014#, Date4 =#4/21/2014#, Date5 =#4/22/2014#, Date6 =#4/23/2014#, Date7 =#4/24/2014#, Date8 =#4/25/2014#, Date9 =#4/26/2014#, Date10 =#4/27/2014#, Date11 =#4/28/2014#, Date12 =#4/29/2014#, Date13 =#4/30/2014#, Date14 =#5/1/2014# Where QOID=500000

db.Execute sSql

When I paste the statement into SQL Server and replace the # signs with single tic ', it executes perfectly in SQL Server

Jim Horn

<Potentially stupid question>

In your Access VBA code, do you have a line..

sSQL = "the update statement you have posted above"

Phil

ASKER

yes, exactly, I just pasted the value of sSql from the immediate pane.

Phil

ASKER

this is the actual code:

sSql = "Update tblInst_TimeSheet Set Date1 =#" & TD1 & "#, Date2 =#" & TD2 & "#, Date3 =#" & TD3 & "#, Date4 =#" & TD4 & "#, Date5 =#" & TD5 & "#, Date6 =#" & TD6 & "#, Date7 =#" & TD7 & "#, Date8 =#" & TD8 & "#, Date9 =#" & TD9 & "#, Date10 =#" & TD10 & "#, Date11 =#" & TD11 & "#, Date12 =#" & TD12 & "#, Date13 =#" & TD13 & "#, Date14 =#" & TD14 & "#"

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phil

ASKER

Yes, I have tried that as well and I'm getting "0 records affected". In the past on various databases, I've had it work both ways and I don't have a clue why it works or does not work. In this instance, it does not. Thanks so much for your help Jim!!

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phil

ASKER

DayID is the primary key and the table does have a timestamp field.

DayID      int      Unchecked
QOID      int      Checked
EmployeeID      int      Checked
LeadMan      bit      Checked
Day1      float      Checked
Day2      float      Checked
Day3      float      Checked
Day4      float      Checked
Day5      float      Checked
Day6      float      Checked
Day7      float      Checked
Day8      float      Checked
Day9      float      Checked
Day10      float      Checked
Day11      float      Checked
Day12      float      Checked
Day13      float      Checked
Day14      float      Checked
Date1      date      Checked
Date2      date      Checked
Date3      date      Checked
Date4      date      Checked
Date5      date      Checked
Date6      date      Checked
Date7      date      Checked
Date8      date      Checked
Date9      date      Checked
Date10      date      Checked
Date11      date      Checked
Date12      date      Checked
Date13      date      Checked
Date14      date      Checked
doDelete      bit      Checked
TimeStamp      timestamp      Unchecked
            Unchecked

PatHartman

Whenever you define DAO or ADO objects, you should disambiguate them by qualifying them. There are common object names and if you have both libraries loaded, there could be confusion as to which object you actually want.

Dim db as DAO.Database
Dim td as DAO.TableDef
Dim rs as DAO.Recordset

Print the sql string to the debug window. Copy it and paste it into a new query using the SQL View. Run the query. You will probably get an error message. Frequently running queries from VBA will not give you any error message even if they don't run. PS, I would also put a space between the "=" and "#" for each date set if you don't already have one.

Phil

ASKER

I tried executing the SQL statement in an Access query as suggested. You are correct, I received an error and have attached a file showing the error. Thanks...
SQL-Query-Execution-Error-in-Acc.JPG

PatHartman

Well, that's a new one. Are you trying to execute this as a pass-through query? If you are, the dates need to be delimited with single quotes.

Did you add spaces between the = and #?

Switch the query from SQL view to QBE view. Do you get an error message? What is it?

Please paste the string from the debug window so we can see the actual query.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phil

ASKER

The problem is solved as I was at my Client yesterday and established a pass through query which works great. Thank you all so much for your assistance!!

The interesting thing is that this code worked fine in my application 2 versions prior. We even pasted the code directly from the old version into the new version and it failed. The code was identical in both versions. I'm wondering if something is present in the new version as a result of some windows update that prevents it from working.

But all is solved now with the pass through so I'm not going to spend any more time trying to figure out why the code failed.

THANKS AGAIN!!! GREAT JOB

Dale Fye

glad to help

PatHartman

Access has a feature that tells Access to use SQL syntax in its queries rather than "Access" syntax. Perhaps that box was checked.

Phil

ASKER

Interesting and thanks for the info, I did find in Options "SQL Server Compatible Syntax (ANSI92) with two check boxes: "This Database" and "Default for new databases". "This Database was grayed out and disabled. Is this what you are referring to?

Jim Horn

>Access has a feature that tells Access to use SQL syntax in its queries rather than "Access" syntax.
Pat - Where's this at? Learn something new every day..

PatHartman

It is amazing isn't it. I've been using Access for close to 20 years and some of the stuff I stumble on has been there all along and I just never noticed. Like many things, I discovered this one because I inherited a database where my predecessor had set it and I couldn't figure out what was going on when I tried to add a new query using standard Access SQL syntax.