Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 27th, 2004, 10:21 PM
Authorized User
 
Join Date: Jun 2003
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default Access, SQL Server - Stored Procedures / DTS

I am not sure this is the correct forum to talk about Access / SQL Server interoperability issues. If not please point me to the correct forum.

I have an MS Access front-end (not web based) and a linked SQL-Server back-end. At the recommendation of another programmer, we created an ODBC connection so that I can treat the backend tables as linked Access tables.

My process requires that I follow the following process:
   1) Delete all records from a temporary table in the backend.
   2) Import a CSV (comma separated values) file into the temp table.
   3) Open the table for the user to examine the records.
   4) Allow the user to delete (update) records of their choosing

First Issue: I learned how to build a DTS package to do steps 1 & 2. However, I don't know how to call the DTS package from the Access front-end.

Second Issue: after the DTS package does steps 1 & 2, the linked table doesn't update to show the new set of records.

Third Issue: for reasons I don't understand, this linked temp table is not updateable ... when I open it, I can't modify, delete, or add any records.

I'm open to suggestions ... I'm not sure if this is the most efficient way to accomplish the sequence of steps I have outlined, but it appears I am missing something.

Can anyone illuminate me on how to do this OR how to do this more efficiently?

Thanks ahead of time!:)





--- Tom
__________________
--- Tom
 
Old August 28th, 2004, 07:06 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Tom,

First Issue: I haven't done this for a while but I'm pretty sure DTS Packages can be called through VBA and using the SQL DTS Object Library. Search msdn.microsoft.com for DTS Object Library and you should get the results you need. You might have to install a SQL Client on the computer with Access on it or at the very least be able to link to the library through the network.

Second Issue: Did you refresh the link.

Third Issue: Do you have a unique index on the temp table. As I understand it Access needs a unique index on a linked table for insert/update/delete? Add the index to the temp table after you have imported the records using an SQL Task in the DTS package.

Let me know how you go.
 
Old August 28th, 2004, 07:30 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Tom,

found this link http://support.microsoft.com/default...b;EN-US;209807

might help

 
Old August 28th, 2004, 09:14 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Suggestion:

Use access ADP as your Front-End application. Have all your permission level set on the backend (SQL) you will have better control over access and permissions.

You can create stored procedures in the backend(SQL) to call your DTS package or you can schedule your dts packages to run at your desired interval.

What is access.ADP?
Access Database Project (ADP)
is a feature that came out with
Access2000. An Access Project is really a development environment
dedicated to MS SQL Server and MSDB (Microsoft Database
Engine). No other databases can be used in an Access
Project. In an Access Project, you have design privileges to SQL Server objects, such as Tables, Views and Stored Procedures. Of course, you also have the forms and reports and VBA code that exists in a normal MDB file. Also, there are no linked tables, and all data
access to the SQL Server is done through OLEDB, which is much faster than linked tables.
 
Old August 29th, 2004, 09:58 PM
Authorized User
 
Join Date: Jun 2003
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Chapster:

     Thanks for the tip on DTS. Answer to your questions: 1) I manually refreshed the link, but do not know how to do it programmatically. 2)No, don't have unique index ... seems I read something that says I must have a unique index to be able to update ... I will have to investigate.

  In the meantime, let me look at your link.

  Sorry for the delay ... haven't been able to get to my computer for several days.

Thanks!


--- Tom
 
Old September 1st, 2004, 05:17 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have a look for the 'RefreshLink' method of the 'Tabledef' object in the Access VBA help file. You should be able to find a code example.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures not working in Server Explorer hexOffender VB Databases Basics 2 October 13th, 2006 09:52 AM
RecordSets vs Stored Procedures in Access Roy0 SQL Language 0 December 28th, 2005 02:02 PM
the SQL stored procedures BenCh BOOK: ASP.NET Website Programming Problem-Design-Solution 0 March 11th, 2005 04:18 PM
stored procedures and MS Access madhukp Classic ASP Basics 5 August 26th, 2004 12:22 AM
Professional Sql Server 7 Stored Procedures Darrell Clary All Other Wrox Books 0 October 8th, 2003 09:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.