DAP problems when linking to .txt vs .xls file

[Marcus123]

I've encountered a strange problem with Data Access Pages in Access 2003. I
am attempting to automate some data linking by sending data directly from SAP
(batch job) to a file server as a .txt file. From Access, I have linked to
that .txt file (creating a table) and am running a DAP off several queries
that use that table. The strange thing is, if the file comes directly from
SAP as .txt you can only run the DAP once. If you then leave the current DAP
open or try to run another (related) DAP, you get the following errors:
"Data provider failed while executing a provider command." and "The text file
specification 'FG Link Specification' does not exist. You cannot import,
export, or link using the specification." However, if close all DAPs and then
try to open the DAP again, it works fine.

The above problem only occurs if the linked file is in the ".txt" format. If
I manually open the file, and save as ".xls" and relink the Access database
the .xls file, none of the above problems occur. The problem is that SAP can
only send the data as .txt and not as .xls, which is preventing the
automation process. Any suggestions would be greatly appreciated.

 

[Guest]

As I recall, txt files are always opened in exclusive mode.
xls files may be opened in a shared read-only mode.

Can you automate Access to import the txt file into an mdb table?

(david)

 

[Marcus123 via AccessMonster.com]

Hi David,
Thanks for the information, which clarifies exactly why we were having the
problem. I wrote a macro in Access to run a 'make table' query and convert
the linked .txt table into a true database table. However, I would need to
automate the macro to run at specific intervals. When you mentioned
automating Access to import, were you referring to a macro? From what I can
tell, if you use the 'transfer text' macro action in Access, and the table
already exists, the new data is appended to existing data in the table. This
won't work for our needs (we need to replace existing data with imported data)
Any other suggestions would be appreciated. Thanks again,

As I recall, txt files are always opened in exclusive mode.
xls files may be opened in a shared read-only mode.

Can you automate Access to import the txt file into an mdb table?

(david)

I've encountered a strange problem with Data Access Pages in Access 2003. I
am attempting to automate some data linking by sending data directly from SAP

[quoted text clipped - 13 lines]

only send the data as .txt and not as .xls, which is preventing the
automation process. Any suggestions would be greatly appreciated.

 

[Guest]

TransferText is a thin wrapper over an ordinary Append Query.
You can use an Append query or a Make Table query as you wish.

You can put your Append query or Make Table query into
an Access macro, or VBA, or VB.Net, or Excel, or Word
or VBscript or anything as you prefer.

I mostly use DAO in VBscript or Access VBA, but I've
used lots of other things like Word and C++ to automate
database actions. It depends what you are running anyway,
and what you are comfortable with.

But I wouldn't use Access directly for automation of an
action that could be done with a DAO or ADO object
unless you already had Access open -- and then for text
import into Access I prefer to use a VBA or VB read loop
- I can do much better error reporting and recovery writing
a specific import routine instead of using generic SQL.

(david)

Hi David,
Thanks for the information, which clarifies exactly why we were having the
problem. I wrote a macro in Access to run a 'make table' query and convert
the linked .txt table into a true database table. However, I would need to
automate the macro to run at specific intervals. When you mentioned
automating Access to import, were you referring to a macro? From what I can
tell, if you use the 'transfer text' macro action in Access, and the table
already exists, the new data is appended to existing data in the table. This
won't work for our needs (we need to replace existing data with imported data)
Any other suggestions would be appreciated. Thanks again,

As I recall, txt files are always opened in exclusive mode.
xls files may be opened in a shared read-only mode.

Can you automate Access to import the txt file into an mdb table?

(david)

I've encountered a strange problem with Data Access Pages in Access 2003. I
am attempting to automate some data linking by sending data directly

from SAP
[quoted text clipped - 13 lines]

only send the data as .txt and not as .xls, which is preventing the
automation process. Any suggestions would be greatly appreciated.