Appending records from Access 97 tables in SQL tables

Appending records from Access 97 tables in SQL tables

Post by Bill Murph » Wed, 28 Nov 2001 10:37:30



I created a SQL 2000 database using an Access Project (ADP) in Access 2002.
In this ADP I linked (created views) to several Access 97 tables in an MDB
file located on a separate server.  I set up SQL tables containing the same
columns as the Access 97 views, to store the data imported from Access 97.
Each night I would like to run two jobs in SQL Server which would first
empty the SQL tables, then re-populate them by appending records from the
Access 97 views in SQL.

The stored procedure (delete query) that I created in SQL works well, both
manually and from a scheduled SQL job.  However, the append query only works
manually from the SQL query analyzer or from within Access 2002.  The SQL
job that I set up for the append fails every time.  The stored procedures
that I am using in the job steps in the two Transact-SQL jobs are:

For the delete:
    Execute StoredProcedure_Delete_StatusTypes
    Go

For the append:
    Execute StoredProcedure_Append_StatusTypes
    Go

I thought the problem might lie with permissions, but I have given full
rights to the user, including database owner rights.

I would appreciate any thoughts on why the append query is failing while in
job mode.

Bill

 
 
 

Appending records from Access 97 tables in SQL tables

Post by Vadim Rap » Wed, 28 Nov 2001 16:12:03


try to find the error messages related to the failure. I think you can see
those either in the job itself, or in sql server logs. Perhaps you can try
Profiler as well.

Vadim


Quote:> I created a SQL 2000 database using an Access Project (ADP) in Access
2002.
> In this ADP I linked (created views) to several Access 97 tables in an MDB
> file located on a separate server.  I set up SQL tables containing the
same
> columns as the Access 97 views, to store the data imported from Access 97.
> Each night I would like to run two jobs in SQL Server which would first
> empty the SQL tables, then re-populate them by appending records from the
> Access 97 views in SQL.

> The stored procedure (delete query) that I created in SQL works well, both
> manually and from a scheduled SQL job.  However, the append query only
works
> manually from the SQL query analyzer or from within Access 2002.  The SQL
> job that I set up for the append fails every time.  The stored procedures
> that I am using in the job steps in the two Transact-SQL jobs are:

> For the delete:
>     Execute StoredProcedure_Delete_StatusTypes
>     Go

> For the append:
>     Execute StoredProcedure_Append_StatusTypes
>     Go

> I thought the problem might lie with permissions, but I have given full
> rights to the user, including database owner rights.

> I would appreciate any thoughts on why the append query is failing while
in
> job mode.

> Bill


 
 
 

Appending records from Access 97 tables in SQL tables

Post by Bill Murph » Thu, 29 Nov 2001 02:20:16


I tried this same thing by setting up a Data Transformation Services (DTS)
in SQL server and got similar results.  Apparently SQL Server is having
trouble opening the mdb file when running automated (scheduled), yet can
open it when run manually.

The DTS first drops the SQL table being imported into, then recreates it,
then appends the data from the mdb table.  The drop and recreate table steps
work as expected.  Shown below is the error text I get with the DTS (for the
append step) when run as a scheduled task:

Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft JET Database Engine
Step Error Description:The Microsoft Jet database engine cannot open the
file '\\bmurphy\drive_c\melody\pipeline\Pipeapp4.mdb'.  It is already opened
exclusively by another user, or you need permission to view its data.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003051

The above mdb file is not password protected.  When I run the DTS manually,
no login or password is requested when the mdb is opened.  The mdb is set to
open shared.  I get this same error whether the mdb is already open or not
when the DTS is run as a schedule.

I would appreciate any further thoughts.

Bill


Quote:> I created a SQL 2000 database using an Access Project (ADP) in Access
2002.
> In this ADP I linked (created views) to several Access 97 tables in an MDB
> file located on a separate server.  I set up SQL tables containing the
same
> columns as the Access 97 views, to store the data imported from Access 97.
> Each night I would like to run two jobs in SQL Server which would first
> empty the SQL tables, then re-populate them by appending records from the
> Access 97 views in SQL.

> The stored procedure (delete query) that I created in SQL works well, both
> manually and from a scheduled SQL job.  However, the append query only
works
> manually from the SQL query analyzer or from within Access 2002.  The SQL
> job that I set up for the append fails every time.  The stored procedures
> that I am using in the job steps in the two Transact-SQL jobs are:

> For the delete:
>     Execute StoredProcedure_Delete_StatusTypes
>     Go

> For the append:
>     Execute StoredProcedure_Append_StatusTypes
>     Go

> I thought the problem might lie with permissions, but I have given full
> rights to the user, including database owner rights.

> I would appreciate any thoughts on why the append query is failing while
in
> job mode.

> Bill

 
 
 

1. Access 97/SqlServer 7: Appending Access Tables into Sql Server tables doesnt work for big tables

Hello,

I am trying to append the data from the Access'97 tables into the linked SQL
server 7.0  tables. Although this process seems to work for smaller tables,
its fails to work for relatively bigger tables(9000 records).  I have tried
to append 500 records at one time, but then the same problem persists. I
find it difficult to digest the fact that the same process worked fine with
6.5 when i tried to append 1000 records at one time.

Is there something that i am missing?
Please advice. This is really urgent!!!

Rgds
Nsn.

2. Example program

3. Access97/Sqlserver7: Appending Access tables into Sql sever tables doesnt work for big tables

4. tables to excel?

5. MS Access 97 SQL to MS SQL SQL SQL SQL

6. Oracle Database Administration & Applications Support - SF, CA (Rec)

7. Appending data from Access table to SQL table

8. rundbapp call in windows4gl

9. Linked Table between SQL 6.5 and Access 2.0 or Access 97 through ODBC

10. MS-Access 97 to SQL Server to MS-Access 97

11. Joining tables from a table variable with SQL tables

12. SQL Server 7.0 tables Linked into Access 97 DB

13. How to link temporary tables of SQL Server from Access 97