Importing/Exporting and Auto-Execution to end my Project

[mrfritz44]

You guys have been great in helping me make a transition from traditional SQL/ASP to Access SQL/VBA. I'm on the home stretch of my process and need some direction on the automation to make my solution practical. Your help would be greatly appreciated.

My task is to wrtie a module that does the following in sequence:

1. Import a fixed width text file (call the text file Import.txt) into an existing table using an import defintion (I have no experience using import/export definitions - I've only done manual imports and exports).
2. Execute "Module1"
3. Execute "Query1"
4. Execute "Query2"
5. Export the results from "Query2" to a another text file to a specified location. Call the export text file "Export.txt". Call the location "C:\Directory"

The execution of "Module1" can take up to 15 minutes and the queries run in a few seconds - if that matters.

Any help for someone who has only helped write a single module in Access would really be valuable. Hopefully I've provided enough information.

Thanks,

Fred

 

[JerryKlmns]

Take a look at the DoCmd for Import/Export (TransferText method) and execution of queries (OpenQuery method). In order to execute any procedures inside your module1 just use the Call statement to execute them in your desired order. You could place all this stuff in a new procedure and call only this on to execute everything!

 

[mrfritz44]

I'm a little shaky on syntax. Is this all done within a new module? How would the import/export defintions be used in that process?

 

[JerryKlmns]

In a sub type DoCmd.TransferText, place the cursor inside TransferText and then press F1 to get the syntax from the help. Do the same for OpenQuery.

A module is a place to write code. The ordinal position of functions, procedures etc inside the module has no meaning. So, yes you can place everything in a procedure and execute that procedure or have many separate procedures for a group of tasks to complete. The second is easily maintaned and debugged!

 

[mrfritz44]

I've gotten to this point, but the code is still red (meaning abug I suppose)

Function ImportTextFile()
DoCmd.TransferText(acImportFixed as AcTextTransferType,spomc,spomc,spomc.txt)
End Function

 

[JerryKlmns]

DoCmd.TransferText acImportFixed, "spomc", "spomc", "C:\spomc.txt"

 

[mrfritz44]

I actually beat you to it, but thanks. How would I delete the table contents before the import and reset the autonum counter to start at 1?

 

[JerryKlmns]

To delete all records from a table

CurrentProject.Connection.Execute "DELETE FROM YourTableNameHere",,129

Forget resetting autonum counter. It is supposed to mean nothing to anyone! (Many posts on this one)

 

[mrfritz44]

OK, so I'm getting the hang of this, but when I execute one of the queries in my function, it prompts me to update the table. How can I avoid this prompt?

 

[JerryKlmns]

Docmd.SetWarnings False to turn them off
Docmd.SetWarnings True to turn them on

 

[mrfritz44]

Thanks! Here's a question on the horizon. My queries are executing, but my Module does not seem to be. Is this correct:
DoCmd.OpenModule "Module1"

Can I safely assume that one DoCmd will go to the end before another begins?

 

[mrfritz44]

Another previously addressed question. Are you sure I can't reset that counter? Every time I run the module it adds 29000 records to the table. In real life this will be a daily occurance. It seems like good house keeping to me. Thoughts?

 

[JerryKlmns]

You can ´t execute a module! If you have a sub in that procedure it is executed like

Call ProcedureName

In case there are more then call them one after the other!

 

[mrfritz44]

Thank you. I added your code and it seems to have kicked off the "procedure". I have a slight bug in that procedure, but I'll get it working since I already know what it is.

Thanks an absolute ton for helping with this. It's been almost real time as if you were here physically pounding it into my head......hehe.

Thank you.

 

[JerryKlmns]

Enjoy Programming......

 

[mrfritz44]

Just to be clear as I arrange these functions, DCmd's and procedures from other functions:

I can paste a series of functions one after another and they will execute sequentially and to completion before beginning the next function in the series, correct?

 

[JerryKlmns]

Yes! Like

Call SubAlpha
Call SubOmega
Call SubBeta
Call SubTheta

It doesn ´t matter if those subs are listed in any order inside a module(s)!
They are executed in the order you call them one after the completetion of the other.

 

[mrfritz44]

Thanks for the clarification. I just chased my tail for ten minutes before I realized that I absolutely MUST reset that counter before I import the table. A query uses the autonum value to generate groupings of 66 records each. Any idea what that command is?

 

[JerryKlmns]

There were some posts about Ranking records in a query, but can´t find the post from PHV.

But I would forget that autonumber field and I would create the ranking using a loop for a recordset to mark whatever special needed.

 

[JerryKlmns]

Got one here thread700-543994