Truncating SQL Server table from Access

I have a customer who is using MS Access to import data into SQL Server. He is first deleting all the data from the SQL Server table. It is this delete that is taking an unacceptable amount of time. I told him (being a SQL Server person) that TRUNCATE is much faster and more efficient than DELETE, since the end result is to clear the table completely.

Is there a way to perform a TRUNCATE against a SQL Server table linked table in Access?

Or is there a more efficient way to remove the SQL data

Sponsored Links:

Related Topics

Access & SQL Server
I am considering using SQL Server for some of my applications that are at present in Access 03. I have downloaded SQL Server Express 8 and have linked an empty Access db via ODBC to a couple of tables that I created locally on SQL Server.

I just wanted to ask whether it is worth investing time in using Access + SQL Server, or whether I should move to a different FE environment? If the answer is yes can you please suggest some guidelines.

BTW, in linking the Access db to the SQL Server via ODBC I was able to create a form based on one of the tables. I am able to view the table records but I cannot add records, even though the form settings are set to enable additions

Linked SQL Server Table Problems
I currently have a linked table to MS SQL server 2008 that has an identity field that increments as records are added. Everything was working fine from Access to SQL server until I added a trigger event in the SQL server database for the linked table then I can no longer insert new records.
I get the error
"could not execute query; could not find linked table. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tblContacts'. (#208)"

Link MS Access to SQL Server Table-Valued function to pass parameters
I am linking Access to SQL Server to extract data from a table containing over 12,000,000 records. I would like to pass a parameter to SQL Server to be used on the table-valued function assuming that extracting the data in SQL Server would run faster than in Access.

I created a table valued function in SQL Server, however, when I go to Access to create the linked table, I only see tables and views.

Questions on SQL Server
I want to create a SQL Server, and I do not know how to get started. I have tried 2003 SQL Server Express, and it did not work for me due to the limit of database size at 4GB. I need a database with at least 100GB capacity.

My current plan is to buy a $500 desktop computer for the purpose of hosting the SQL Server. I will have SQL Server as a back-end database and Access as a front-end database. I also may want to access the SQL Server from Access FE in a laptop computer.

Linked Table and S1T00 timeout error
I have a client with an Access 2002 front end and a Sql Server 2000 database running on a Windows 2003 server. There is a linked table to the Sql Server db using the ODBC; type connection. After an update to the Server the connection times out with an error:

Conneciton fialed:
SQLState: 'S1T00'
Sql Server Error: 0
[Microsoft][ODBC SQL Server Driver]Login timeout expired

This occurs when the linked table is updated (well, when RefreshLink is executed).

I've tried recreating the linked table, but as long as this connection string is used it errors:

ODBC;Description=C3 SQL Server;DRIVER={SQL Server};SERVER=SQLSERV;UID=XXX;PWD=XXX;APP=Microso ft Data Access Components;

Import or link to SQL Server data
If your department or workgroup uses Microsoft SQL Server to store data, you might encounter scenarios where you need to work with some of the SQL Server data in Microsoft Office Access 2007.
This article describes the steps both for importing and for linking SQL Server data into Access 2007.
What do you want to do?
* Import SQL Server data
* Link to SQL Server data
* Learn how Access interprets SQL Server data types

Connecting MS Access to SQL Servers through ODBC
Once you got a DSN defined in the ODBC manager that connects to your SQL Server, you can connect a normal MS Access document to the Oracle server, and link an Access table to a SQL Server table.

Create temp tables in Access from SQL Server tables
If you’ve ever asked Access to do a join between a local Access table and SQL Server table (which is called a heterogeneous join) you may have experienced first hand how slow it can be to process results. The situation could be vastly superior if you can afford to download the SQL table as a temporary table to your Access front-end and then process the join. This post will provide you with an easy solution to download SQL Server data into Access using a subroutine you can call from code.

Migrating from Access 2000 to SQL Server 2000
In this article, we will simulate migrating a relatively small but established business from Microsoft Access 2000 to SQL Server 2000. There are a variety of reasons a business might wish to perform such a migration. Legacy queries, forms, and reports will be taken into consideration, as well as the appropriate way to handle database files.
Growing businesses often come to a stage where they need to migrate their MS Access database to a SQL Server. TOC: Migrating from Access 2000 to SQL Server 2000; MS Access: User Created Objects; Creating a SQL 2000 Server database; Exporting tables from MS Access 2000 to SQL 2000 Server; Establishing links to SQL 2000 Server database; Verifying functionality of existing objects.

Exporting Access Table to Sql Server
I have created a table (Tbl_XRef) in Access 2003 which is created on a click button (it performs various functions between old tables and new to create the above table).

Once it is completed I want to transfer it automatically across to SQL Server on the same click event (after having manually deleted the previous version of the table in SQLserver).

I do have an ODBC link called ODBC1 which goes to the SQL Server database...

Importing Data from MS Access to SQL Server 2000
I am currently working on a project that generates reports connected to an SQL Server Database. Every day I import the data from an MS Access Database into SQl to keep the SQL DB up to date.

I am able to manually import the data from Access to SQL Server without any difficulty.

To make my life a little easier I would like to set up an automated process in SQL Server to do this 'update' every night. (I believe I have to use a DTS package).

(The MS Access DB is modified/updated almost everyday and it is very important that the SQL server database is kept up to date).

Updating SQL server form Access form?
I have an access form. I want to add a submit button to the form that when clicked runs a sql insert statement that gets inserted into a SQL Server table. I will be connecting to the SQL server through an IP address similiar to how you would on an .asp page. Can anyone explain to me how to do this or show me some example code?

Link MS ACCESS 2010 Database uto SQL SERVER 2008 R2
I have a requirement. I have to link the MS Access 2010 web Database which I have created to sql server 2008 R2. My Requirement is, if I update the ms access database that should reflect the sql server database.

If I insert a record in the ms access database table it should insert in the sql server database table.

I have downloaded the SSMA for access and migrated the database. but when I insert new record in my ms access table. it is not reflecting in my sql server.

My Requirement is, whenever I insert/delete/update a record in ms access table, it should automatically do the same thing in sql server.

SQL Server 2005
When I first started working on Access 2010, I understood that I can only do networked access instead of online since I did not have database online to use. I was scrolling around in my computer (Vista 32 bit) and I noticed that I have SQL Server 2005. I do not even know what exactly it does but have basic understand that it can act as database for online access? But I am not sure if I can use version 2005? When I googled SQL Server and Access, it always mention SQL Server 2008 with Access. Does that mean I cannot use SQL Server 2005 as I have

Linked Table To SQL Server?
I've inherited an access application that consists of a number of .accdb files.

Depending on what your doing, 1 of the 2 accdb files contains most of the UI, and the other accdb files keep the data, and those files are kept on the network so everyone access the same data.

I want to convert these central accdb files (not the UI ones) to run on SQL Server. I know that there is an conversion wizard that upsizes the database to sql server, and from sql server Iknow I can import access data, but I don't see anywhere in the Linked Table Manager to connect to a SQL server database.

How do I do this?

Linked Table To SQL Server?
I've inherited an access application that consists of a number of .accdb files.

Depending on what your doing, 1 of the 2 accdb files contains most of the UI, and the other accdb files keep the data, and those files are kept on the network so everyone access the same data.

I want to convert these central accdb files (not the UI ones) to run on SQL Server. I know that there is an conversion wizard that upsizes the database to sql server, and from sql server I know I can import access data, but I don't see anywhere in the Linked Table Manager to connect to a SQL server database

Import into SQL Server from Access Error
I am attempting to import an Excel file into SQL Server 08, by using an Access front-end. When I attempt to import the file, I recieve the following error:

ODBC--insert on a linked table 'dbo_OLDDATA' failed.
[Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (#0)

I am not sure if it is a problem with SQL Server or Access. The error message makes it sound like a datatype in access isn't compatible with the one in SQL Server. The only thing is that I have no clue which of the 18 or so fields it has a problem with.

I have also tried importing into an access table, and then appending, with the exact same results. SQL Server formats dates as yyyy-mm-dd, so I reformatted fields to match, and made them text so it doesn't output incorrectly. (even if you format as yyyy-mm-dd, if you leave it as date/time it outputs as the original) Otherwise I am at a loss at to what may be causing this

SQL Server Backend is Read Only
I have upsized my access program backed to SQL Server Express 2008 using the SQL Server Migration Assistant. Everything seems to be working fine in query mode only. That is, my Access program can access the data in the database. However, if I try to do an Update or Add Record. I get an error message that the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server Express backend database not in a ReadOnly mode?

Linked SQL Table - can not add new record
A script is used for the Linked Tables - the ODBC setting for SQL Server Native Client 10.0 are exactly the same
One table is tblAPD_Fed_St_CO - it was copied into tblAPD_Fed_St_CO _Archive - this all worked before migrating to SQL Server 2010

see attachment: The top table has a (New) button.
it also won't allow any edits. Security on both is DBO.

On the SQL Server Server Manager Studio side - it can be appended. On the Access side, it can't. Copy a row in SQL Server view and insert it in the new row - refresh the linked table - and the new row shows up.

Everything is scripted - it is the only table in Access linked tables where the new record doesn't show up. Maybe because of the lack of a primary key?
its Friday the 13th and I thank my Lucky Stars that I am not Superstitious.

Passing permissions into ODBC SQL SERVER Connection with Query
I have a query that looks into a SQL Server table and deletes rows with certain criteria and inserts rows with certain criteria in access 2010. My problem is, as an Admin I have the permissions to delete and insert into the SQL server table.

they would need the same permissions as me. I don't want to do this due to security issues (iwork for a big organization)

My question is: is there a way to pass the credentials (server name, authentication, password, etc) into the sql server as part of the query? will this even work or is the only way to grant the users permissions.