July 1, 2009 at 3:00 pm
I'm new to SQL Server 2005. I have successfully linked tables in Access 2007 with my SQL Server. I am running into problems when I try to copy the queries from access to SQL, for example here is a sample of a query and the error message.
DELETE [002 Shipments Table].*
FROM [002 Shipments Table];
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.
This error message was from a query that worked in Access 07 that deleted all of the records out of a table. My goal is to create a stored procedure that will update tables with information from the data warehouse, and delete outdated info in tables.
I was under the initial impression that you could cut and paste data from Access if you had the same tables linked to SQL Server am I way off on this????
July 1, 2009 at 3:43 pm
I would take off the [002 Shipments Table]. it isn't really needed, since the from clause only specifies a single table.
As far as cut and paste, wellllllll, I don't know about that, unless you are using passthru connectivity for the queries. In that case the query is sent to the ODBC connection effectively unchanged. The syntax is close though but some of the rules will be different..
Also, you REALLY should not have spaces in table names or any other object name, its nice for a human to read but extra typing and hassles for programmers.
CEWII
July 2, 2009 at 4:55 am
DELETE [002 Shipments Table].*
FROM [002 Shipments Table];
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.
This error message was from a query that worked in Access 07 that deleted all of the records out of a table.
Hi.
SQL server is right! This is not standard SQL. The correct syntax for delete statemente is
"DELETE FROM ".
I think Access allows you to specify also "tablename.*" as an extension to the language (maybe this makes it easier to cut and paste from a SELECT statement?)
ciao
Giacomo
July 2, 2009 at 8:19 am
Thanks for the helpful info 🙂
July 2, 2009 at 8:19 am
Thanks for the helpful info:-)
July 3, 2009 at 7:46 am
You don't need the FROM, all you need is this:
DELETE [002 Shipments Table]
In which case the deletions are written to the transaction log. Depending on the permissions, you might also use this:
TRUNCATE TABLE [002 Shipments Table]
which does not log the deletions.
July 6, 2009 at 1:50 pm
William,
I typed in the delete statement and got the following:
delete tblrwtest;
(0 row(s) affected)
I can run a similar query in access 07 and clear all records in a table. I must have not exported my tables to SQL Server 05 correctly because when I run a query on the SQL Server it doesn not update my tables in the access db? I spent a considerable amount of time trying to find a book...wasted money on SQL Server 2005 for dummies. All I need to do is have the ability to move tables from access to SQL server (I've done this). Part 2 is write queries that update the info in the access tables (this part is not going very well).
July 6, 2009 at 3:37 pm
If you're really stuck, I would suggest starting over with the SSMA which makes things much simpler. Just Google for
"SQL Server Migration Assistant 2005 for Access V4.0"
which is the updated version for SQL 2005 and Access 2007. It will move all your tables & data into SQL Server, creates links from Access to the SQL tables. It will also warn you of issues such as missing primary keys, missing default values etc.
I've used the previous version & it works quite well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply