VB, Access and text file manipulation

[Michael Thomas]

Hi everyone

Do any of you know if this is possible in Access either by using the import
tool or by writing a module in VB:

I have a database containing information for a retail chain which, amongst
others, has a table with fields for product description, quantity at
locationx, etc. The problem is that very often I need to update it after
making updates in an old back office application called Uniplus. I am,
however, capable of exporting the information from Uniplus to a text file
where the columns are seperated by equal width.

e.g.

Barcode Product Description Quantity on Hand

600233 Marlboro Filters 100

except for the fact that another 3000 or more items will follow after that
one. Because it's a retail chain, the quantities at the shops change daily,
so updating it by hand is very difficult. Uniplus reads information from a
barcode (SYMBOL SP1550) scanner and then prints out (to file or paper) stock
on hand list, so if I could import the text file into Access daily, without
overwriting values that are equal, changing quantities that have changed and
adding/deleting items that DON'T match, it would be perfect.

Is it possible for me to do this in Access or do I need to write something
that would manipulate a text file in VB?

Thank you for your assistance

Michael

[Don Leverton]

Hi Micheal,

Oh yeah, for sure this could be done in Access...
Have a look in help for "TransferText"
The whole process can be automated by creating, then using a saved "File
Import Specification"

You may have to install the Text file driver... if I recall, it is not
installed by default install.
To check this:
1.) Go to the database window, and select the "Tables" tab
2.) Right-Click, select "Import"
3.) When the Import dialog box appears, drop down the "Files of Type" and
scroll through to see if "Text Files" is one of your choices.

If it IS there, just continue on through the import wizard choosing "fixed
width" as your type of text file.

If the "Text Files" entry isn't there, reply to this stating which version
of Access you are using, and I, or someone else, will help you set this up.

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

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

"Michael Thomas" <mi*****@cocknbull.co.za> wrote in message
news:40********@news1.mweb.co.za...

Hi everyone

Do any of you know if this is possible in Access either by using the import tool or by writing a module in VB:

I have a database containing information for a retail chain which, amongst
others, has a table with fields for product description, quantity at
locationx, etc. The problem is that very often I need to update it after
making updates in an old back office application called Uniplus. I am,
however, capable of exporting the information from Uniplus to a text file
where the columns are seperated by equal width.

e.g.

Barcode Product Description Quantity on Hand

600233 Marlboro Filters 100

except for the fact that another 3000 or more items will follow after that
one. Because it's a retail chain, the quantities at the shops change daily, so updating it by hand is very difficult. Uniplus reads information from a barcode (SYMBOL SP1550) scanner and then prints out (to file or paper) stock on hand list, so if I could import the text file into Access daily, without overwriting values that are equal, changing quantities that have changed and adding/deleting items that DON'T match, it would be perfect.

Is it possible for me to do this in Access or do I need to write something
that would manipulate a text file in VB?

Thank you for your assistance

Michael

[Pieter Linden]

> > I have a database containing information for a retail chain which, amongst

others, has a table with fields for product description, quantity at
locationx, etc. The problem is that very often I need to update it after
making updates in an old back office application called Uniplus. I am,
however, capable of exporting the information from Uniplus to a text file
where the columns are seperated by equal width.

e.g.

Barcode Product Description Quantity on Hand

600233 Marlboro Filters 100

except for the fact that another 3000 or more items will follow after that
one. Because it's a retail chain, the quantities at the shops change

daily,

so updating it by hand is very difficult. Uniplus reads information from

a

barcode (SYMBOL SP1550) scanner and then prints out (to file or paper)

stock

on hand list, so if I could import the text file into Access daily,

without

overwriting values that are equal, changing quantities that have changed

and

adding/deleting items that DON'T match, it would be perfect.

Is it possible for me to do this in Access or do I need to write something
that would manipulate a text file in VB?

Thank you for your assistance

Michael

Simple. Create a "work" table with the same structure as your final
table. Then you can use an outer join to get all the values that are
in one table but not in the other. (The "find unmatched" wizard will
walk you through this part.) Once you have those new records, you can
append those to your final table.
You could also write an update query to update the prices or whatever
for the items you already have in your table. Finally, when you're
done, you can delete the data in the temporary table.

Do you need VB for this? Not unless a series of queries/macros won't
do it for you. And I don't see anything here you would be required to
use VB for. VB is definitely more flexible than macros, but if you
don't know how to use it, it's worthless to ya.