Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with simple access sql statement

Posted on 2013-11-08
10
Medium Priority
?
473 Views
Last Modified: 2013-11-08
Hello, I'm trying to understand why the following does not work in Access SQL. Please forgive the basic crud. This would work in MS SQL, but when I try it in Access I get the following error:

Syntax error in from clause

What am I doing wrong?

DELETE *  FROM Table1

INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')

Select * from Table1;
0
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39633870
in access date/time field need to be wrap in # tags

try this

INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, #10/10/2012#,#10/10/2012#)
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 39633873
INSERT INTO Table1(email, productid, datecreated, datesend) values('adf', 5, '10/10/2012','10/10/2012')

You can not have a space after values
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 39633894
You need two queries:

DELETE *  FROM Table1

and

INSERT INTO
Table1 (email, productid, datecreated, datesend)
values ('adf', 5, #10/10/2012#, #10/10/2012#)

/gustav
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:gogetsome
ID: 39633907
This does not work as well, with same error


DELETE *  FROM Table1

INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, #10/10/2012#,#10/10/2012#)

Select * from Table1;

gustav, are you say in access sql you can't run multiple statements in one query?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39633919
Yes.
But you can easily execute one after the other.

/gustav
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39633935
Actually you need three:

DELETE *  FROM Table1

And

INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
 
And

Select * from Table1;

You can use single quotes to delimit dates in Access, but the documented method is the hashmark.

You don't need a space after VALUES.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39633942
you can run multiple separate sql statements using vba codes, this will run the queries in succession

docmd.runsql "DELETE *  FROM Table1"

docmd.runsql  "INSERT INTO Table1(email, productid, datecreated, datesend) values('adf', 5, #10/10/2012#,#10/10/2012#)"
0
 

Author Comment

by:gogetsome
ID: 39633947
Okay, making much more sense now... I suppose I need to do what I want in VBA to execute the various queries I need?

How should I divide up the points? :-)
0
 

Author Closing Comment

by:gogetsome
ID: 39634232
Thanks guys!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39634240
;-)
0

Featured Post

Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 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