Help with importing...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

good day all..

I use my DB to track my checking account and I download and manually
import files from my checking company.

would it be possible to automate that process? I would like to be able to
press a command button and automatically import the file "export.csv"and
update my checking account table.

Is this possible?

Thanks in advance...

Brook
 
generally speaking, the same steps that you're taking manually - such as
import, append, update, delete - can be done automatically with a macro or
VBA procedure, run from a Command button's OnClick event.

first, if you're manually running the Import Wizard on the .csv file each
time, stop reinventing the wheel. set up the import in the Wizard, and then
save the settings as an Import Specification (see the Advanced button in the
lower left corner of the Wizard dialog box). once you have an Import
Specification saved, you can use the TransferText action to import the file
the same way every time.

then think about what you do next. did you import the data to a "temporary"
table, in order to clean it up? if so, how do you do that - by running an
Update query? a Delete query? multiple queries? do you delete data from the
checking account table? or update existing data? if you're running action
queries to manipulate the data (update, delete, append), you can use the
OpenQuery action to run them automatically.

bottom line is: anything you've been doing manually, like correcting
spelling or capitalization of names, etc, you may have to continue to do
manually. (however, if you describe specific manual actions, we *may* be
able to help you automate those, as well.) anything you've been doing by
running the Import Wizard, or action queries, you can set up to run in the
same sequence in a macro or VBA procedure.

hth
 
Good Day Tina,

Sorry for the late response,

I have never used the "import Wizard". Where is that found? Do the files
have to be named the same thing each time or can I set up to open / go to a
folder where my files are?

Thanks,

Brook
 
I use my DB to track my checking account and I download and manually
import files from my checking company.
Click to expand...

how are you manually importing the files without using the Import Wizard?
 
Yes,

I guess I do use the wizard, I thought it might have been a wizard in the
toolbar? But I'm still unclear on how to set it up.

Brook
 
start a manual import. go through each step of the wizard, right up to the
point where you would click the Finish button. STOP! click the Advanced
button in the lower left corner of the dialog box. in the Import
Specification dialog box, click the Save As... button on the right side.
name the specification anything you want, and then click OK.

now all the import settings that you've been doing manually each time, are
saved in that Import Specification. now you can use the TransferText action
to import the file, and refer to the saved Import Specification for the
proper setup. see the TransferText Action in Access Help for details on how
to use it in a macro or VBA procedure.

hth
 
Hello Tina,

Thanks and now I understand what to do, I have just a couple more questions..

1. where does the import specification save to?

2. when I perform the import, I have two columsn that have null values,
and am getting import errors, how do I prevent that?

Thanks again..

Brook
 
Hello Tina,

I did find the transfertotext files for setting up my macro, but how do I
set up the macro to search for the file for the user to choose? from what I
can see, you can only choose a specific file name to import?

Any suggestions?

Thanks,

Brook
 
well, there's code you can use at http://www.mvps.org/access/api/api0001.htm
but if you're not familiar with VBA (or even if you are) it can be pretty
intimidating. if you're doing this for your own personal use, it's be easier
for you to just save the file in the same folder on your hard drive each
time and give it the same name, then hard-code those values into your macro.

hth
 
Back
Top