transfertext

[Guest]

I can't get my code to work for importing textfile.

================
Function ImportWebData()

On Error Resume Next


Dim reportdate As Date
reportdate = DateAdd("d", 0, Date)


DoCmd.SetWarnings (False)

DoCmd.RunSQL ("Delete * From WebData")

DoCmd.TransferText acImportDelim, "specImportWeb", "WebData",
"\\NPH\DATA\class_count_" & Format(reportdate, "mmddyy") & ".txt", False

DoCmd.SetWarnings True

End Function

=====================

The WebData table is there, the path is correct, & i set up a manual import
& named it "specImportWeb". I get no data in my table after this is run (from
a macro)

Thanks for the help.

patti

 

[Allen Browne]

TransferText does the export, but what does the line immediately above that
do?

Perhaps after deleting all records from the table, there's no records left
to export?

 

[Alex Dybenko]

Hi,
comment On Error Resume Next line and check what error your code produce,
perhaps it can;t find the import file or so

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi Patti,
After your import is done in your DB do you have a table named paste errors
(or something like that)? In this case I think in your destination table
(i.e. WebData)
you have some fields with data type incompatible with the data in your
source file (e.g. you try to put text in a number field). Check that and
change the data type in your destination table. To be sure you can define all
fields in your destination as text.

HTH Paolo

 

[Guest]

Thanks for all the replies.

Allen- I am trying to import a text file. I believe i have the right
statement.

Alex- not sure how to comment On Error Resume Next line and check what
errors my code produces. Could you provide info on that step. I am a relative
newbie and not sure how to capture errors, which obviously i better learn to
do.

Paolo- I get nothing. The WebData table is empty of data and there is no
import error file. If the data was incompatible wouldn't there be the import
error file?

Any more advice would be greatly appreciated. Perhaps it's so simple i'm
missing it? I will continue to twist this around.

 

[Guest]

Well Patti, as I said, you can try to define the type of all the field in
your destination table as text (len 255) and try if it work. Check also that
the number of "fields" in your .txt isn't greater than the number of fields
in the dest table.
To rem a line of code just put an apostrophe ' in front of the line
' On Error Resume Next
So if an error is raised the program'll stop and you can see in the debug
the offending line
I hope you can understand my explanation 'cause english is not my native
language, and sometimes I puzzle myself if I'm 'nuff clear and
understandable...

Regards Paolo

 

[Guest]

Thanks Paolo.

I created a new test database. Redid import spec, rewrote vba import code
and then macro. The data was imported to table, but i had mistakenly written
my code for acdelimited when it was a fixed width. I corrected that and it
ran fine in my test db.
i redid importspec, copied code into active db and still nothing.

Ideas?

patti

 

[Guest]

Did you try to use your importspec manually?
I mean using the file menu, get external data, import, choosing your txt and
clicking the advanced button and then choosing your importspec and then
importing so if your importspec is correct you'll have a table containing
your data.
Then you can rename this newly created table for your code so surely the
data type of the table are correct.
Cheers Paolo

 

[Guest]

I did the import manually and renamed the spec w/ the same name - "overwrite
prompt" i said yes.

I will try giving spec different name.

thanks for helping.

patti

 

[Guest]

Did you get the data in the table importing manually?

 

[Guest]

yes i got the table manually. i'll try renaming spec.

 

[Alex Dybenko]

Hi,

Alex- not sure how to comment On Error Resume Next line and check what
errors my code produces. Could you provide info on that step. I am a
relative
newbie and not sure how to capture errors, which obviously i better learn
to
do.

just add ' (single quote) in front of this line:
On Error Resume Next

then when you run your code - access show you error message, if any occur

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Dear Patti,

Try running the import by itself and without the report date? You said it
isn't giving you any errors, so I am guessing it isn't canceling. Maybe it
just isn't selecting any data from your source, and yes you say there is data
in the source. But is the date parameters not correct or not being used
correctly. Can you run it without any date parameters and take all the data
in the source, just for a test. If that works, then add the date parameters.

Also have you tested the import specification manually to make sure it works
on the source data you are trying with the macro?

Good Luck,
Steve