Solved

Microsoft Access 2010, Update statement not updating SQL Server table

Posted on 2014-04-29
17
1,573 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
 
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 34

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 34

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 34

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 34

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now