append records to existing access db from text file via access via

[cirugio]

New to Access coding. Hoping someone can help with the coding in Access VBA. Just don't know how to start this. Any help is greatly appreciated! Thank you in advance.

Here is pseudocode for what i trying to do:

1. prompt user for date of file to open

2. open file in a specific path (c:\2015_12_05 Cust.txt) if file not found, display message 'no file found'

3. append data records from the opened file (data records are found between the words START-OF-FIELDS and END-OF-FIELDS) to existing access table called 'customer'

4. once file is processed move the c:\2015_12_05 Cust.txt file to a different folder c:\history\2015_12_05 Cust.txt


sample data file
-------------------

START-OF-FILE
RUNDATE=20151205
PGM=oneshot
FIRM=xxxxx
COMPRESS=xxx
FILETYPE=disk
FILENAME=xxx
SOURCE=data
DATERANGE=20161205|20161205
FORMAT=horizontal


# London
# England

START-OF-FIELDS
name
address
state
zip
END-OF-FIELDS

START-OF-DATA
000000001|0|99|12/05/2015|MAC SHAW|654 CLARA ST.|NEW JERSY|90004|
000000001|0|99|12/05/2015|MARY PETERS|1343 LANDINGS|NEW MEXICO|45321|
000000001|0|99|12/05/2015|JACKLYN MCDONALD|4341 SUPERIOR ST.|NORTH CAROLINA|83721|
000000001|0|99|12/05/2015|LAURA CUMMINGS|982 5TH AVENUE|NEW YORK|73821|
000000001|0|99|12/05/2015|SARAH MCPHERSON|4214 HOLLYWOOD|CALIFORNIA|93830|
END-OF-DATA
TIMEFINISHED=TUES DEC 6 13:27:24 2015
END-OF-FILE

 

[JHB]

Is the data always organized in the order, Name, Address, State, Zip?
Or does the amount and order of the fields variate?
If the data and amount of fields always are the same, then the easiest would be to make a copy of the file, cut out the unnecessary stuff, (everything before and included the 'START-OF-DATA' and everything after and included the 'END-OF-DATA'), save it, use an import specification created in MS-Access, and then delete the copy of the file.

 

[cirugio]

This process needs to be done daily for 44 files. I was hoping not to have to open and edit each one to remove unwanted rows. Was hoping there was a way to load the data between the START-OF-DATA and END-OF-DATA indicators in the file within a vba module.

 

[JHB]

This process needs to be done daily for 44 files. I was hoping not to have to open and edit each one to remove unwanted rows. Was hoping there was a way to load the data between the START-OF-DATA and END-OF-DATA indicators in the file within a vba module.

It shouldn't be done manually, but be code.

 

[cirugio]

I totally agree. I just don't know how to code this. That is why I posted.

 

[sneuberg]

To allow the user to pick a file you may want to use the filedialog. Note that you will need to add the Microsoft Office Object library reference to use that. Since you need to get to the data between START-OF-DATA and END-OF-DATA I suggest just using the VBA Standard Text File I/O Line Input # Statement to read the file line by line and when your loop is reading the lines with the data you can using the split funcion to separate the data into variables which you can insert into you table with the INSERT TO SQL statement. To move the files I suggest you look into the FileSystemObject.

If you are very new to Access VBA code I suggest watch this video series. before you start working on this.

 

[cirugio]

Am as new as they come. My head is spinning at the moment.

 

[JHB]

.. I just don't know how to code this. That is why I posted.

I am aware of that, therefore I asked some questions in my first post, I would like to have answers to before I gave some more specific code examples.

I see sneuberg have given you a lots of hints.

 

[cirugio]

Hi JBH,

Yes, lots of hints. I read through them but still very confused. I literally just started a few days ago with Access & vba. I was hoping someone might of had some logic i could have recycled. I have been at this for the last 8 hrs and getting now where. Any code samples you can provide would be appreciated. I will continue to read on in the meanwhile.

 

[sneuberg]

Am as new as they come. My head is spinning at the moment.

I suggest just doing this in baby steps. Read the information on the File I/O and then watch the video series until you have enough to start working. Make sure you understand the tools you have for debugging. Then you could start by writing a short program that just loops through a file and outputs the lines to the Immediate window using Debug.Print. Then throw in an If-Then clause and see if you detect the line with START-OF-DATA. Then just keep building on that.

I have a couple of suggestion concerning the VBA editor. In Tool-Option in the Editor table uncheck the Auto Syntax check or it will drive you nuts with message boxes. Also check the Require Variable Declaration. This will insert OPTION EXPLICIT into your modules and require you to declare variable. You really want to do that or you will go nuts tracking down simple typos.

 

[cirugio]

Thanks. I need to get it done by Sunday. Will see how it goes.