Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

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
0
pabrann
Asked:
pabrann
  • 7
  • 4
  • 3
  • +1
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Potentially stupid question>

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

sSQL = "the update statement you have posted above"
0
 
pabrannPresidentAuthor Commented:
yes, exactly, I just pasted the value of sSql from the immediate pane.
0
 
pabrannPresidentAuthor Commented:
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 & "#"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  

<Another wild guess>  Perhaps this statement is being executed on the server (SQL Server) and not the client (Access).  Try replacing the pound signs, which are the Access delineator for dates, with single quote marks, which are the SQL Server delineator for dates, and execute.

If that fails, I'll step back, as I know there are Access experts extremely skilled in DAO and ADO, that can probably answer your question.
0
 
pabrannPresidentAuthor Commented:
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!!
0
 
Dale FyeCommented:
so, what does the line that executes this look like?

is this being run against a linked table?

Does the SQL Server table (tblInst_Timesheet) have  primary key?  You will not be able to run an update query against a SQL Server that does not have a primary key

How about a TimeStamp field?  I've found that it is a good habit to include one of these in every table as well as the PK, whenever I'm working in a multi-user environment.
0
 
pabrannPresidentAuthor Commented:
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
0
 
PatHartmanCommented:
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.
0
 
pabrannPresidentAuthor Commented:
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
0
 
PatHartmanCommented:
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.
0
 
Dale FyeCommented:
>>I tried executing the SQL statement in an Access query as suggested.

How did you try executing the SQL, from the SQL View, or via code.

If I wanted to update the values in a linked SQL Server table, I would normally do it like:

strSQL = "yada, yada, yada"
Currentdb.execute strsql, dbSeeChanges + dbFailonError

Another way to do it would be to do it as a pass-through query, but if you do that, as Patricia indicates, you would need to change your # characters to single quotes ( ' ).

What version of office are you using?
What is the connection string for the connection of that table to the SQL Server?
0
 
pabrannPresidentAuthor Commented:
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
0
 
Dale FyeCommented:
glad to help
0
 
PatHartmanCommented:
Access has a feature that tells Access to use SQL syntax in its queries rather than "Access" syntax.  Perhaps that box was checked.
0
 
pabrannPresidentAuthor Commented:
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?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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..
0
 
PatHartmanCommented:
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.
SQL Setting
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now