Solved

Microsoft Access 2010, Update statement not updating SQL Server table

Posted on 2014-04-29
17
1,588 Views
Last Modified: 2014-05-05
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
Comment
Question by:pabrann
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40030520
<Potentially stupid question>

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

sSQL = "the update statement you have posted above"
0
 

Author Comment

by:pabrann
ID: 40030524
yes, exactly, I just pasted the value of sSql from the immediate pane.
0
 

Author Comment

by:pabrann
ID: 40030525
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40030545
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
 

Author Comment

by:pabrann
ID: 40030555
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 400 total points
ID: 40030689
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
 

Author Comment

by:pabrann
ID: 40030837
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40030930
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
 

Author Comment

by:pabrann
ID: 40030970
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40032026
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 40032153
>>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
 

Author Closing Comment

by:pabrann
ID: 40034300
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40034428
glad to help
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40035188
Access has a feature that tells Access to use SQL syntax in its queries rather than "Access" syntax.  Perhaps that box was checked.
0
 

Author Comment

by:pabrann
ID: 40035274
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40035293
>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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40042114
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question