vb/access

[jbrown99]

Any ideas on inserting a text file
into an access table?

example "insert into table [file.name]"???

[ernmeister]

J,

Are you wanting to put all the data into one field, or are you breaking up the lines of text?

ern

[jbrown99]

well i was hoping that access would break it up
how ever the fields are set in the table


but i can re do this to input a , to break it
up such as this

"name, addy, blah, ablh"



so can i now access this file as a table
and select all data and input it into the
table??

or do i have to go one line at a time? this takes way to
long to process where talking 20,000 + lines

[ernmeister]

J,

Maybe this info will help.

You can import or link data from a delimited text file or fixed-width text file. Before you import or link, make sure that the file has the same type of data in each field and the same fields in every row. Although you usually create a new table in Microsoft Access for the data, you can append the data to an existing table as long as the first row of your text file contains matching field names, or its column order is the same.

1 Open a database, or switch to the Database window for the open database.
2 To import data, on the File menu, point to Get External Data, and then click Import.

To link data, on the File menu, point to Get External Data, and then click Link Tables.

3 In the Import (or Link) dialog box, in the Files Of Type box, select Text Files.
4 Click the arrow to the right of the Look In box, select the drive and folder where the file is located, and then double-click its icon.
5 Follow the directions in the Import Text Wizard dialog boxes.

Notes

· If importing a text file takes an unexpectedly long time, it might be because many errors are occurring. To cancel importing, press CTRL+BREAK.
· In a fixed-width text file, you can ignore fields at the end of a record that contain no data. In addition, the last field with data in the record can be less than the maximum width.
· If all the records in a fixed-width text file are the same length, there can be an embedded row separator (such as a carriage return and linefeed) in the middle of a record. If the records aren't all the same length, embedded row separators should not be used because Microsoft Access will treat the embedded row separator as the end of the record.ns before you import.

[jbrown99]

ya read that article i
need to do all that from
code any ideas?

[ernmeister]

Since I don't know exactly what you're wanting to import I can't get too specific on how to do it. Will you be importing the text file multiple times? Will the text file be in the same format each time (ie; columns are the same type/size)? I have a couple of programs where I import text files on a weekly basis. Once I created the import specifications initially, I then set up a macro that runs when I click on a form button. As long as the text file is the same format each time you want to import it, this process works great.

[jbrown99]

ya thats what it will be the format will
be the same i just need to import it
in code from vb

if u can give me an example of how you
import that file

thanks

[ernmeister]

Does the first line of you text file contain the field names?

[jbrown99]

no but i could put those in if i have to

[ernmeister]

Sorry to keep asking questions. Is the text file fixed-width or delimited?

[jbrown99]

i made it delimited with a ,


between each field

[ernmeister]

Ok, if you can put the field names in the first line delimited with a , I think I can tell you how to do it via VB code.

[jbrown99]

lol ok thats how it will go

do u have inst. message or anything?

[ernmeister]

Don't have IM, just email: [email protected]

Ok, here goes.

DoCmd.TransferText acImportDelim, , "tablename", "full path of file being imported", True

The table name and the path should both be in "".

This isn't how I do mine, but it did work on a test file I created. Let me know if you have any luck.

ern

[jbrown99]

when i put the names of my field in the text file
it should be like this

field1,field2,field3,field4 ...ect...


i'll give it a try tom.

thanks a million hope it works

a little faster then what i'm doing now

again thanks

jb

[jbrown99]

I must be really stupid becasue
i can't get that to work

[ernmeister]

Show me the code you used. Maybe I can spot the problem.

[jbrown99]

ok it worked in access

can u put this in vb and use it
or just access?

and if so can u call access to run this code
like a stored procedure??

this works just great but i really need to
do this in visual basic 6.0

[ernmeister]

I only work with VB in Access. I'm not familiar with VB 6.0. Sorry.

[jbrown99]

Thanks for the info you help a lot

anyone else think they can do this vb???