473,394 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Transfering access records between databases via email?

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
10 2150
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
"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
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
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
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
"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

"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: WalterR | last post by:
This is my first time here, so there may be earlier relevant threads of which I am unaware. Though my experience with DB2 is not extensive, such as it is was under OS/390 or equ. My main...
12
by: Mike MacSween | last post by:
Sorry if this is a bit off topic, but I can't seem to find a 'remote control' newsgroup on my news server. And anyway I know Tony has some experience of this. The app is split FE/BE. I'd like...
2
by: edo | last post by:
100 Day Analysis Following is a summary of articles spanning a 100 day period, beginning at 10 Sep 2003 06:41:30 GMT and ending at 19 Dec 2003 04:09:06 GMT. Notes ===== - A line in the...
2
by: Noloader | last post by:
Hello, Access XP, SQL Server 2000 Is it possible to hide a SP under Queries in Access, yet still be able to Execute it from Access? (Similar to hiding Tables, then using Views) We hooked...
1
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ......
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
5
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a...
0
by: colleen1980 | last post by:
Hi: I have oracle 8i for windows 2000 installed on one dedicated computer. I have different databases in microsoft access. The easiest step to create table in oracle i already did that. Hardest...
13
by: Charlotte | last post by:
Hi, i've googeled to find a asp-script that can compare all the records in two different access databases the mdb's have exactly the same tables what i want is that (the output) all the...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.