Hi Terry,
I've found that a lot of VBA books tell you "how" but not "why"
(if that makes any sense), ...
You can always post questions to the newsgroups for any clarification. If
you do, it is helpful to include the title & author, and page number. Someone
else who answers questions likely has a copy of the same book.
But it still leaves me with the problem of coming up with an "automated"
way for HQ to import the data...without manually copying the XLS file into
an "Import" folder, doing the import, identifying it, and then clearing out the
import folder.
Well, they're going to have to do some work at Headquarters! How much
depends on the amount of automation that you build into your solution.
Perhaps you can FTP a csv text file, or data in the XML format, instead of
using e-mail to transfer the data.
In late 2003, I helped another person with extracting text from the body of
Outlook e-mail messages. Perhaps you could use this technique. Here is a link
to that thread:
http://groups.google.com/group/micr..._frm/thread/2c49153e6857e48d/9dcc42b324adc81a
Using this method would allow you to write code in the master database at HQ
that automatically interrogates a dedicated Outlook folder for messages and
processes them. Just an alternate idea. I have a copy of the "fixed"
procedure, which I can send to you, if you send me a private e-mail message
with a valid reply address. I have posted an obscurred version of an e-mail
address that you can use to contact me. Whatever you do, please do not post
your read e-mail address to any newsgroup message, unless you wish to start
receiving loads of spam.
Tom
QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Thank you again Tom -
I'm self taught so have read a lot of Access/VBA books...but not the one you
mentioned. (I'm running 2003, but generally just leave everything in 2000
format.) I've found that a lot of VBA books tell you "how" but not "why"
(if that makes any sense), so I'm always looking for another angle. I'll be
sure to add this one to my collection.
The SendObject command was the missing link in my case. (It seems to me I
shoud've known that. It's not like I never heard of it before. Somehow, it
just didn't "register" with me that this was what I needed.)
All of the sample code you provided worked perfectly. I see what you mean
about the TXT format. The XLS format would be the way to go as far as
getting the data to HQ. But it still leaves me with the problem of coming up
with an "automated" way for HQ to import the data...without manually copying
the XLS file into an "Import" folder, doing the import, identifying it, and
then clearing out the import folder.
I've been wrestling with this for a couple of days, and for what it's worth,
here's what I came up with. I'm using TransferDatabase to move the results
of an "unsent" transactions query into a separate "Transfer" database on the
local machine. The local user will need to zip the transfer database and
send the zipped file by e-mail manually, but I think they can handle that all
right.
All the HQ user has to do is open the transfer database from the e-mail
message and click a command button to append the records to the master table.
(I built in a link from the transfer database to the master table on the HQ
server...and built in a "location" key in the transferred data.)
If you see any "holes" in this, your input would be appreciated.
Serendipitously, your advice has provided me with an answer to a few issues
on other projects that I have had on the "back burner" for a while. It's a
great help to me. Thank you again!
Terry
__________________________________________
:
Hi Terry,
I get by coding within Access okay, but I'm far from an expert...and am at
a loss when it comes to interoperability within the different Office programs.
Can you recommend a resource where I might find an example of how to do this?
There are several good books available that can help you in this area. I
don't know which version of Access that you are using, but some of the books
written for Access 2000 or 2002 are heavily discounted these days. A book
that I like is Beginning Access 2000 VBA by Sussman & Smith (Wrox Press). I
was able to purchase a brand new copy, 2+ years ago at a Half Price Books
store, for $16.95. It included a CD with lots of sample code. (This was
actually to replace my original copy purchased at full price--I made the
mistake of loaning it to a former student and never got it back). You might
want to search the used books section at Amazon.com for this title, or for
similar books.
(I'll be happy if I can get the basics to work..let alone trying to work
around any Outlook security issues.)
I just tried a quick experiment using DoCmd.SendObject, and specifying a
text file as the output. I usually use a similar command to send reports in
snapshot format:
DoCmd.SendObject acTable, "tblSales", _
OutputFormat:=acFormatTXT, _
To:=strReceipient, Subject:=strsubject, _
EditMessage:=True
where strReceipient and strSubject are strings that contain the e-mail
address and subject text, respectively. Note that you can also send a query
by substituting acQuery as the object type and the name of the query as the
object name.
However, I cannot say that I liked the format of the resulting text file. It
includes hyphens as rows in-between each row of data, and | character
separating the fields. That's more difficult than it needs to be for the
receiving end. An alternative might be to output the results to an Excel file
instead, as in this example:
DoCmd.SendObject acTable, "tblSales", _
OutputFormat:=acFormatXLS, _
To:=strReceipient, Subject:=strsubject, _
EditMessage:=True
This produces much cleaner output, which should be very easy to import into
another database. If you need to send more than one table or query, then you
can export the results first to text files. In this case, it's very easy to
produce a comma separated variable (csv) text file:
DoCmd.TransferText acExportDelim, , "tblSales", _
CurrentProject.Path & "\Sales.txt", True
You can even get fancy and give each exported text file a unique name,
instead of using a hard-coded name like "Sales.txt". For example, you might
want to include the name of the location in the output file name, so that it
would be easier to sort out at the receiving end. You'll need additional code
to create a new message in Outlook, attach the files, fill in the e-mail
address & subject, etc. I think I'd only go this route if you have a need to
send more than one recordset (table or query) in the e-mail message.
Otherwise, just use the simplier DoCmd.SendObject.
I haven't worked with XML at all (yet?), so the article you suggested was a
real strain on my brain.
I also have not worked much at all with XML. But, this task might provide
you with a good incentive to learn more about it! <smile>
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi Tom -
Thank you for your response. This is exactly along the lines of what I was
looking for.
I get by coding within Access okay, but I'm far from an expert...and am at a
loss when it comes to interoperability within the different Office programs.
Can you recommend a resource where I might find an example of how to do this?
(I'll be happy if I can get the basics to work..let alone trying to work
around any Outlook security issues.)
I haven't worked with XML at all (yet?), so the article you suggested was a
real strain on my brain. The concept sounds right, but I'm afraid my
learning curve would throw my estimated project completion time into the next
decade (okay...maybe that's a stretch).
Either one of your solutions sounds great, but right now doing something
simply, straightforward(ly?), and expeditiously is more important to me than
elegance. Thank you again for your assistance.
Terry Perkon
________________________________________
:
Hi Perkon,
Have you considered using VBA code to export the table to an e-mail message,
as an attached text file? You should be able to set it up to that the message
is automatically generated, with the data attached in a text file, and the
To:, CC:, BCC:, and Subject lines automatically filled in. All the sender
would need to do is click on the Send button. (You can even automate this
process further if you want, but you'll need to either work around the
Outlook security issues, or use an alternate SMTP engine).
Another alternative might be to use VBA code to export the table as XML and
automatically create a message ready for the user to hit the send button.
Here is an article that includes using VBA to export and import XML data:
http://msdn.microsoft.com/library/d...en-us/dnoffpro01/html/XMLinOfficeXPPartII.asp
Depending on your coding skills, you can automate much of the process on the
receiving end as well.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Thanks for the response. I had thought about that, but the structure of the
local databases will be different than the master. The master transaction
file will have some fields that that won't match the local transaction files
and there would be some extra tables, forms, reports etc that I wouldn't want
in the locals. Plus, after time the local databases could grow pretty large.
I'm hoping to find an automated way to generate a new local database each
day with only the day's transactions. Of course, the prior day's database
would need to somehow be deleted as part of the routine.
________________________________________
:
You may want to look into a Replicated database ...
You should be able to find more information on this in Access Help and on
the MS site ...
R. Hicks
________________________________________
:
I have about twenty identically structured but independent Access databases
at various company locations recording simple sales transactions into a
single table. I need to import daily transaction data into a master database
at company headquarters. Each location has internet access, but there is no
companywide intranet.
I am trying to provide the local users with a simple way to transmit the
data to HQ and HQ with a simple way to update the master database.
It would be easy enough for me to fill a temporary table with the necessary
data, but I don’t want the users to have to manually export the temp table
into a new database, zip it, attach it to an e-mail message and send it every
night…and then have HQ reverse the process for all of the locations to import
the data. Any suggestions on how at least a portion of this could be
automated somehow within Access?
