Topics Search

Truncating SQL Server table from Access

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

Similar posts...


Access & SQL Server

Access & SQL Server Icon
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

Link MS Access to SQL Server Table-Valued function to pass parameters

Link MS Access to SQL Server Table-Valued function to pass parameters Icon
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.

Linked SQL Server Table Problems

Linked SQL Server Table Problems Icon
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)"

Linked Table and S1T00 timeout error

Linked Table and S1T00 timeout error Icon
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;

Create temp tables in Access from SQL Server tables

Create temp tables in Access from SQL Server tables Icon
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.

Connecting MS Access to SQL Servers through ODBC

Connecting MS Access to SQL Servers through ODBC Icon
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.

Exporting Access Table to Sql Server

Exporting Access Table to Sql Server Icon
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...

Questions on SQL Server

Questions on SQL Server Icon
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.

Link MS ACCESS 2010 Database uto SQL SERVER 2008 R2

Link MS ACCESS 2010 Database uto SQL SERVER 2008 R2 Icon
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.

Link SQL Database to new Access front end

Link SQL Database to new Access front end Icon
I have an old database that was running on Access 2000-2003 that had one table linked to the SQL server. The main part that I needed the sql server for was the OLE data attached to a individual documents.

Now I have updated the database into a 2007-2010 version, reports, queries and macros. The one main table that is on the sql server I only added two new fields which is to become the _local.

My problem is do I need to upsize to "use database" to attach it to the sql table, or the linked table manager? The linked table manager is greyed out and I have not had to use it before, so I am unfamiliar with it.