Automating my Query and Import Specs with a Single button. 4

[gbs01]

I have an Inventory database that I manually update each week with .csv & .txt files.

Here is my current procedure:
-----------------------------
1) Run "delQryAppaoo" - This deletes the old data first.
2) Import "edidb.txt" using Import Spec "APPAOO EDIDB Import Specification"
3) Import into existing "tblMasterReferenceDatabase"
4) Run "qryUpdateDistIDAppaoo" - This updates the Distributor field to the value "APPAOO"

What I want to do is make this a One Click update. I would like to add a button to my master search form that says "Update" , which when clicked runs my query, uses my Import Spec & then runs my update query. Also, it would be nice to have a status message that all went ok!

Thanks in advance for your help!
Jerry

 

[alr0]

Hi Jerry,

You can have a button activate a series of macros or run VBA code to automate these tasks.

Have you written code or algorithms in any other languages?

You may want to play with the macros and get them working. This is easy and may satisfy your needs. Then you can go further by translating each macro to code and then string them together. You could also dive right in with a few good books and the knowledge base and crank our some code.

What help would be most beneficial at first to get this started?

alr

 

[gbs01]

No, I have only pasted many lines of code in VB and made minor changes to make it work with my apps. I am still learning the VB terminology. I was just wondering about the proper syntax in VB which would convert my manual steps to one. As far as macros go, I have not created a single macro in Access yet! My instructor said that VB was the only way. So I have sought out VB solutions to customize as I slowly learn why it works.

I'll look in to the macros.
Thanks for your help!

 

[alr0]

Hi Jerry

If you are accustomed to code I would not advise using macros. I use them only for things that have no substitute. e.g. autoexec etc.

Perhaps a better route would be using the code wizard. You may need to load extra wizards if you did not select them on installation.

Create a button on a form with the wizards on and you will be walked through many common functions for existing objects; forms, reports, records, etc. Look for Miscellaneous to run queries.

Then you can examine the code Access created, make modifications, and string them together. I do not think you can do imports from the wizards but check the VBA help index under import and the knowledge base from microsoft.com. The syntax is not tough and there are often examples that make things very clear.

This is fairly easy, let us know if it doesn't go smoothly or if you need help making changes.

BOL,

alr

 

[SuicidED]

Hi
Try this in a Module

Function Appaoo()
On Error GoTo Appaoo_Err

DoCmd.OpenQuery "delQryAppaoo", acNormal, acEdit
DoCmd.TransferText acImportDelim, "APPAOO EDIDB Import Specification", "edidb.txt", "put here the path to where the file edidb.txt is held", False, ""
DoCmd.OpenQuery "qryUpdateDistIDAppaoo", acNormal, acEdit

Beep
MsgBox "IMPORT COMPLETE !!!!!!!!!!!", vbOKOnly, "FILE IMPORTED TO DATABASE"

Appaoo_Exit:
Exit Function

Appaoo_Err:
MsgBox Error$
Resume Appaoo_Exit

End Function


Assign the module to the on click event of your button.

Eddie

 

[PeteJohnston]

Eddie - does Error$ give you different/better info about the error than Err.Name or Err.Description ? I've never used it before.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[SuicidED]

To be honest I've never noticed the differance. I'm sure someone will tell us what is the best way to use the error command.

 

[gbs01]

Thanks to ARL & Eddie!

I have never used modules before? What's it's use? or difference than VB ?

but here goes...

I pasted the code into a new module named Module1 .

I then went to design view of my Form, and the properties of my button, but...

How do I use a module on the onclick event?

Also , in looking over the code, should there be a reference to my tblMasterReferenceDatabase?

In my import spec , it asks what table to import to , and I have to always say tblMasterReferenceDatabase from the drop-down box.

Thanks again!
Jerry

 

[SuicidED]

Hi Jerry

In the properties of the command button you are going to use to run the import go to the On Click, hit the little black dots and select code builder.
Paste this, Module1.Appaoo, so that it now looks like below.

Private Sub Command0_Click()'This bit will be the name of your button'
Module1.Appaoo
End Sub

The import bit should read like this
DoCmd.TransferText acImportDelim, "APPAOO EDIDB Import Specification","tblMasterReferenceDatabase", "put here the full path where the file edidb.txt is held", False, ""

Hopefully that will get you working.

Regards Eddie

 

[gbs01]

Eddie, Thanks again for the post!

I pasted in all the code, made the changes specific to my system, and clicked on the "Update" button.

The code ran each of my queries, asking "ok" to continue. Then the message IMPORT COMPLETE!!!!! came up.

Great! The only problem is the "APPAOO EDIDB Import Specification" .

When I checked the table, the data came in to the first field of that table, my SPEEDY field.

It seems that when I run that Import Spec automatically, it needs a bit more instruction?

When I'm going thru the manual process, I choose my Saved Spec from the window, then say ok. When I click Next, I see it place the Break Lines in my data. This must be the problem.

The data that was imported via the Module.Appaoo is not adding those Break Lines so it comes in as one long line of text. I know this is not your code's problem. But I wish I knew more about how/where Access stores & uses those Import Specs.

My original file (EDIDB.txt) is a Fixed Length Text File.

Thanks again for your help !
Jerry

 

[SuicidED]

Hi Jerry

Just a quick suggestion. When you created your import spec you tell it that the fields are fixed length and then next screen name each field and what type of field it is. Name the fields the same as your table field names. Save the spec and then alter the import part of the module to the spec name that works. Mess around with abit and I'm sure you'll get it working.

To switch the warnings off put this in the module after
On Error GoTo Appaoo_Err
DoCmd.SetWarnings False
and then before
Appaoo_Exit:
Exit Function
put this
DoCmd.SetWarnings True
to switch them back on again.

 

[gbs01]

Eddie, I'll mess around with the Import Spec. Thanks for all your instruction, I have learned a new skill!

ps: I do have my Import Spec already set to these:
1) where to put the Field Breaks
2) what to name each field

When I import my file, I choose the filename, click the Advanced button, click Specs, choose "APPAOO EDIDB Import Specification" , then say OK. Then I click Next twice & choose my tblMasterReferenceDatabase from the drop-down. Because of the import into an existing table, it bypasses the Field-Types window. Then I click OK, and the data is imported, and each field is properly filled with it's info.

If I could look at the structure of the Import Spec that the Module is actually using, I might see that it is somehow skipping my pre-defined fields and only seeing 1 long fixed-width line for each record...?

If possible, I will post a few screen samples of my fields. Or maybe email you a sample EDIDB.txt , Import Spec, & blank tblMasterReferenceDatabase. ?

Thanks again! Jerry

 

[SuicidED]

Hi Jerry

Holidays in London
Send me examples to eddiegreene@tiscali.co.uk and I'll try and get it working if I get time.

Eddie

 

[SuicidED]

Hi Jerry

Found it, change the line DoCmd.TransferText acImportDelim to DoCmd.TransferText acImportFixed as you are importing a fixed length file.

It should now work.

Happy New Year Eddie

 

[gbs01]

Eddie, You did it!
I had looked that code over & over but did not notice that Delim part. But now I fully understand what is happening.

Your code is trully a "One Click" solution ! Thats what I was looking for!
And it allows me to customize the prompts, as well apply the same code to my other "Import" needs.

Thanks so much for your time, talent & help!
Jerry

 

[ESquared]

Give stars where they are due!