By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Transfering access records between databases via email?

P: n/a
Hi all,

I'm a relative newby to access and VBA etc. My forms and tables etc are
working great and saving lots of time...However...

I have two databases with the exact same table format. I want db1 to be
used to input data by a remote operator, when she is ready I want her
to be able to click a control button to email her new data to db2. This
new data will be added to the table in db2 ready for further
processing.

This is basically a telemarketing database where the user of db1 enters
the contact details for sales leads, these are then sent to db2 where
the user follows up on these leads and processes the data. I want as
much automation as possible, particularly in the first instance (db1).

Any help at all would be great! Please ask for more specific details if
needed...(It's just as hard describing what I want!!)

Thank you

Walshi UK

Nov 24 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Is it just data from one table? Then why not just export as a csv text
file...you can easily import that file on the other end.

Further, you also just generate a mdb file with the new records, and have
the person email that.....

If the data involves multiple tables of data, then you either

email a separate csv text file for each table, or
just email a mdb with the data (that your "export/email" routines
makes).

and, you could even consider using xml for the data export/import, as that
allows you to easily email a single record that represents a complex data
structure (this would be more orientated to need to email ONE piece of
information that is to be represented by more then one table).

If the other person would have *occasional* connection to YOUR network, then
replication could also be a consideration. (eg: traveling sales people on
the road..and when they come back to the office, then they *replicate* their
data. (this assumet that your users will have a good connection to the
*same* office network)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Nov 24 '06 #2

P: n/a
"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:QsJ9h.363900$5R2.160610@pd7urf3no:
If the other person would have *occasional* connection to YOUR
network, then replication could also be a consideration. (eg:
traveling sales people on the road..and when they come back to the
office, then they *replicate*
You mean "synchhronize" their data.
their
data. (this assumet that your users will have a good connection
to the *same* office network)
The simplest form of replication works reliably over a LAN
connection, and if your remote users come into the office, is a
perfectly suitable way to synch. However, the complexity is that the
UI provided in Access does not deal with front end/back end, so you
have to open the back end to do your synch (there is no situation
where your app should not be split, and in terms of replication,
it's even more essential, as front end objects cannot be reliably
replicated).

If you don't have that reliable high-speed connection to the LAN
(i.e., not wireless, and at least 10Mbps), then there are other
forms of replication. But those are more complicated to set up and
maintain and depend on components outside Access itself (though it
can be programmed to be self-contained).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 25 '06 #3

P: n/a
Walshi

I assume the remote person,db1, is basically doing data entry/data creation
for the person who works with db2. Further asume that for illustration
purposes that there are three live data tables, tbl1, tbl2 and tbl3
(disregard any lookup tables). Copy their structures to tbl1x, tbl2x and
tble3x.

When you are ready to send data, have your program first empty tbl1x, tbl2x
and tbl3x with delete queries.

Next fill those tables with append queries that add the CURRENT data from
tbl1 to tbl1x, etc.

Export the tbl1x, tbl2x and tbl3x to a Transport.MDB overwriting the prior
tables.

Have Access email the Transport.MDB to the person with db2.

Db2's program will link to Transport.MDB's tables and execute append queries
to add the new data to its tables.

Kevin C
"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi all,

I'm a relative newby to access and VBA etc. My forms and tables etc are
working great and saving lots of time...However...

I have two databases with the exact same table format. I want db1 to be
used to input data by a remote operator, when she is ready I want her
to be able to click a control button to email her new data to db2. This
new data will be added to the table in db2 ready for further
processing.

This is basically a telemarketing database where the user of db1 enters
the contact details for sales leads, these are then sent to db2 where
the user follows up on these leads and processes the data. I want as
much automation as possible, particularly in the first instance (db1).

Any help at all would be great! Please ask for more specific details if
needed...(It's just as hard describing what I want!!)

Thank you

Walshi UK

Nov 25 '06 #4

P: n/a
Cheers Guys some good pointers for me there.

Replication isn't really an option as db1 user and db2 user aren't on a
network, they are both seperate individual remote users.

I like both the CSV idea and the transfer.mdb options, any thoughts on
which of these two is the easiest to implement? I want as much
automation as possible.

Cheers Walshi

Nov 25 '06 #5

P: n/a
Hi Kevin, I'm liking this idea thank you!

Do you have any code examples for carrying out the above or know where
I can find some?

BTW it's Access 2002
Kc-Mass wrote:
Walshi

I assume the remote person,db1, is basically doing data entry/data creation
for the person who works with db2. Further asume that for illustration
purposes that there are three live data tables, tbl1, tbl2 and tbl3
(disregard any lookup tables). Copy their structures to tbl1x, tbl2x and
tble3x.

When you are ready to send data, have your program first empty tbl1x, tbl2x
and tbl3x with delete queries.

Next fill those tables with append queries that add the CURRENT data from
tbl1 to tbl1x, etc.

Export the tbl1x, tbl2x and tbl3x to a Transport.MDB overwriting the prior
tables.

Have Access email the Transport.MDB to the person with db2.

Db2's program will link to Transport.MDB's tables and execute append queries
to add the new data to its tables.

Kevin C
"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi all,

I'm a relative newby to access and VBA etc. My forms and tables etc are
working great and saving lots of time...However...

I have two databases with the exact same table format. I want db1 to be
used to input data by a remote operator, when she is ready I want her
to be able to click a control button to email her new data to db2. This
new data will be added to the table in db2 ready for further
processing.

This is basically a telemarketing database where the user of db1 enters
the contact details for sales leads, these are then sent to db2 where
the user follows up on these leads and processes the data. I want as
much automation as possible, particularly in the first instance (db1).

Any help at all would be great! Please ask for more specific details if
needed...(It's just as hard describing what I want!!)

Thank you

Walshi UK
Nov 25 '06 #6

P: n/a
"Walshi" <pw****@pwalsh.plus.comwrote in
news:11**********************@14g2000cws.googlegro ups.com:
Replication isn't really an option as db1 user and db2 user aren't
on a network, they are both seperate individual remote users.
Replication needs only an occasional connection, and that connection
could be over a VPN over the Intenret. If they have email, they can
use replication.

However, I wouldn't recommend it for your scenario.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 25 '06 #7

P: n/a

"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11*********************@f16g2000cwb.googlegro ups.com...
See the insertions below for the examples (untested code) you may need.

----- Original Message -----

From: "Walshi" <pw****@pwalsh.plus.com>

Newsgroups: comp.databases.ms-access

Sent: Saturday, November 25, 2006 12:31 PM

Subject: Re: Transfering access records between databases via email?
Hi Kevin, I'm liking this idea thank you!

Do you have any code examples for carrying out the above or know where
I can find some?

BTW it's Access 2002
Kc-Mass wrote:
>Walshi

I assume the remote person,db1, is basically doing data entry/data
creation
for the person who works with db2. Further asume that for illustration
purposes that there are three live data tables, tbl1, tbl2 and tbl3
(disregard any lookup tables). Copy their structures to tbl1x, tbl2x and
tble3x.

When you are ready to send data, have your program first empty tbl1x,
tbl2x
and tbl3x with delete queries.
DoCmd.Set Warnings False

strSQL = "DELETE * FROM tbl1
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tbl2
DOCmd.RunSQL strSQL
' Etc.
>>
Next fill those tables with append queries that add the CURRENT data from
tbl1 to tbl1x, etc.
strSQL = "INSERT INTO tbl1x(Field1,Field2,Field3) " _
& "SELECT Field1, Field2, Field3 " _
& "FROM tbl1 ;"
DoCmd.RunSQL (strSQL)
>Export the tbl1x, tbl2x and tbl3x to a Transport.MDB overwriting the
prior
tables.
DoCmd.TransferDatabase acExport, "Microsoft Access", _
"C:\AccessDbs\Transport.MDB", acTable, "tbl1", _
"tbl1x"
>>
Db2's program will link to Transport.MDB's tables and execute append
queries
to add the new data to its tables.

Kevin C
"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11**********************@j44g2000cwa.googleg roups.com...
Hi all,

I'm a relative newby to access and VBA etc. My forms and tables etc are
working great and saving lots of time...However...

I have two databases with the exact same table format. I want db1 to be
used to input data by a remote operator, when she is ready I want her
to be able to click a control button to email her new data to db2. This
new data will be added to the table in db2 ready for further
processing.

This is basically a telemarketing database where the user of db1 enters
the contact details for sales leads, these are then sent to db2 where
the user follows up on these leads and processes the data. I want as
much automation as possible, particularly in the first instance (db1).

Any help at all would be great! Please ask for more specific details if
needed...(It's just as hard describing what I want!!)

Thank you

Walshi UK

Nov 27 '06 #8

P: n/a
That's fantastic! Thank you so much, I will have a play later on and
see what happens.

Kc-Mass wrote:
"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11*********************@f16g2000cwb.googlegro ups.com...
See the insertions below for the examples (untested code) you may need.

----- Original Message -----

From: "Walshi" <pw****@pwalsh.plus.com>

Newsgroups: comp.databases.ms-access

Sent: Saturday, November 25, 2006 12:31 PM

Subject: Re: Transfering access records between databases via email?
Hi Kevin, I'm liking this idea thank you!

Do you have any code examples for carrying out the above or know where
I can find some?

BTW it's Access 2002
Kc-Mass wrote:
Walshi

I assume the remote person,db1, is basically doing data entry/data
creation
for the person who works with db2. Further asume that for illustration
purposes that there are three live data tables, tbl1, tbl2 and tbl3
(disregard any lookup tables). Copy their structures to tbl1x, tbl2x and
tble3x.

When you are ready to send data, have your program first empty tbl1x,
tbl2x
and tbl3x with delete queries.
DoCmd.Set Warnings False

strSQL = "DELETE * FROM tbl1
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tbl2
DOCmd.RunSQL strSQL
' Etc.
>
Next fill those tables with append queries that add the CURRENT data from
tbl1 to tbl1x, etc.

strSQL = "INSERT INTO tbl1x(Field1,Field2,Field3) " _
& "SELECT Field1, Field2, Field3 " _
& "FROM tbl1 ;"
DoCmd.RunSQL (strSQL)
Export the tbl1x, tbl2x and tbl3x to a Transport.MDB overwriting the
prior
tables.

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"C:\AccessDbs\Transport.MDB", acTable, "tbl1", _
"tbl1x"
>
Db2's program will link to Transport.MDB's tables and execute append
queries
to add the new data to its tables.

Kevin C
"Walshi" <pw****@pwalsh.plus.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi all,

I'm a relative newby to access and VBA etc. My forms and tables etc are
working great and saving lots of time...However...

I have two databases with the exact same table format. I want db1 to be
used to input data by a remote operator, when she is ready I want her
to be able to click a control button to email her new data to db2. This
new data will be added to the table in db2 ready for further
processing.

This is basically a telemarketing database where the user of db1 enters
the contact details for sales leads, these are then sent to db2 where
the user follows up on these leads and processes the data. I want as
much automation as possible, particularly in the first instance (db1).

Any help at all would be great! Please ask for more specific details if
needed...(It's just as hard describing what I want!!)

Thank you

Walshi UK
Nov 27 '06 #9

P: n/a
Just a further note.

Given a choice of CSV vs MDB, The CSV would probably be better because
some email programs (outlook included) will not let you receive MDB
files.

You could do it BUT it would entail another step involving renaming the
files before sending and saving/renaming the saved the file back to an
MDB. In general the fewer the steps the better.

Ron

Nov 27 '06 #10

P: n/a
If you are dealing with MDBs as part of your system and you know who they
are coming from
Look here:
Outlook 2003, Outlook 2002 and Outlook 2000 SP3 (but not Outlook 98 or
earlier Outlook 2000 versions) allow the user to use a registry key to open
up access to blocked attachments. (Always make a backup before editing the
registry.) To use this key:
1.. Run Regedit, and go to this key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\O utlook\Security (change
10.0 to 9.0 for Outlook 2000 SP3 or to 11.0 for Outlook 2003)
2.. Under that key, add a new string value named Level1Remove.
3.. For the value for Level1Remove, enter a semicolon-delimited list of
file extensions. For example, entering this:

.mdb;.url

would unblock Microsoft Access files and Internet shortcuts. Note that the
use of a leading dot was not previously required, however, new security
patches may require it. If you are using "mdb;url" format and extensions
are blocked, add a dot to each extension. Note also that there is not a
space between extensions.
If you are using this registry entry, a glance at Help | About Microsoft
Outlook will show Security Mode: User Controlled above the license
information. See OL2002 You Cannot Open Attachments for more information on
this registry entry.

To force users to save *.zip files to the hard drive before opening, add
..zip to the extensions step 3. See How to configure Outlook to block
additional attachment file name extensions for more information.

If you prefer not to edit the registry directly, you can use one of these
tools to make the change; not all support both Outlook 2002 and 2000:

Attachment Options COM add-in by Outlook MVP Ken Slovak that adds an
Attachment Security Options page to the Tools | Options dialog in Outlook
2002 to allow you to manage which file attachments are blocked. Only for
Outlook 2000 SP3, Outlook 2002, and Outlook 2003. For Also provides a user
interface for setting two other Outlook options -- Read as Plain Text
(Outlook 2002, SP-1 or later) and Minimize to System Tray. Available in
English and German. Shareware.
Attachments Zip Compressor
Automatically compresses outgoing attachments into .zip files. Can
extract files from incoming .zip attachments or compress attachments in an
incoming message. Can handle incoming files that Outlook blocks and put them
into .zip files.
DetachOL Standalone utility to change the list of blocked file
attachments according to the level of risk you're willing to take. Works
with Outlook 2000, 2002, and 2003. Free, donations accepted.
Outlook Permissions Add-in Add-in for adjusting which attachments you
can open under Outlook's increased security, for Outlook 2003, 2002 and
Outlook 2000 SP3 or later. Free
TweakOL Modifies the Windows registry to change the list of blocked
file attachments according to the level of risk you're willing to take,
always show the Bcc field, minimize Outlook 2002 to the system tray, and
disable or enable MSN Messenger. Free. Available for Outlook 2002 and
Outlook 2003.
Xenos Outlook Security Extension Utility to manage file attachment
blocking in Outlook 2000 SP3, Outlook 2002, and later. Also manages optional
settings such as minimize to tray and purge data file on exit. New settings
added in new Outlook updates are supported through online updates to the
software. (Formerly Xenos Outlook Security Extension)

After applying this registry fix or using one of the above tools, the user
still has to save the attached file to a system drive before opening it. In
effect, the fix rolls the attachment behavior back to Outlook 2000 SR-1,
with its included Attachment Security Fix. An end-user cannot bypass this
"save to disk" behavior and open the file directly from the mail message,
though an Exchange administrator can.

"Ron2006" <ro******@hotmail.comwrote in message
news:11********************@14g2000cws.googlegroup s.com...
Just a further note.

Given a choice of CSV vs MDB, The CSV would probably be better because
some email programs (outlook included) will not let you receive MDB
files.

You could do it BUT it would entail another step involving renaming the
files before sending and saving/renaming the saved the file back to an
MDB. In general the fewer the steps the better.

Ron

Nov 27 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.