Set up button to import an XML file

[Susan L]

I will be receiving data in an XML file regularly and would like to set up a
button to do the import. The file imports properly and appends to an existing
table when done manually through Get External Data.

I tried using the Transfer Text option, but of course it didn't work because
it did not recognize the extension. I am using Access 2003. Is there a way to
write the import of an XML file in code?

 

[Susan L]

I checked my original macro and couldn't find either Import or
GetExternalData on Action list, so I used RunCommand, then typed Import. But
couldn't figure out how to specify the file. I'm using Ac 2003.

 

[Russell]

Can you post an example of the XML file (or even better its structure spec.)
and say exactly what you need to do with it?



I would normally get the file into memory using "FileSystemObject" and you
can use Readline method to handle each line in the XML file within a while
loop to.. Search those in help if you're not familiar with them.



There might be an easier way though using the automation you were playing
with. Come back to me about the file.

 

[Susan L]

Hi. had another project earlier this week. Sorry for the delayed response.

I need to import three XML files into an Access database every month. They
import properly using Get External Data, but since there are other actions
that must be taken regarding the data that are done via a form with buttons,
it would be nice to be able to import the data via a button.

I am pretty "junior" in VBA -- I looked up what you suggested, but would
need help in setting up the code. Maybe there's a simpler way?

Here is a sample of one XML document's structure. I do not have the schema
if that was what you are referring to. Here is the beginning of the file and
the field names/data for one record.
<?xml version="1.0" ?>
- <Trans_Val_Inval_Doc>
- <Trans_Val_Inval>
<STDT>2008-06-01</STDT>
<ENDT>2008-06-30</ENDT>
<LOCL_FIPS_ST_CD>01</LOCL_FIPS_ST_CD>
<VLD_TXN_CNT>2646</VLD_TXN_CNT>
<IVLD_TXN_CNT>552</IVLD_TXN_CNT>
<ERR_CNT>1145</ERR_CNT>
<FL_CNT>19</FL_CNT>
</Trans_Val_Inval>
This file creates a table Trans_Val_Inval as you see. I then move the data
out of that table into another table and delete the Trans_Val_In table to be
ready for the next month's import.