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

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.

Tom,

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

might help

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.

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

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.