Topics Search

Truncating SQL Server table from Access

Truncating SQL Server table from Access
Views: 49
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:

More 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;

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.

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

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...

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.