Access to Access transfer of external data?

[steverob]

OK:

Our data comes in to us in Excel spreadsheets, we upload that data into
a local Sybase database, it's quick and presents no problems, but...we
also need to upload that data into an Access database located overseas
which means going across our network, which takes forever.

We're thinking we can get around this by uploading that data into a
local Access database during business hours and then transer it to the
overseas Access database during off hours. The transfer would have to
take into account whether an insert or update into the overseas
database would need to be done.

Is this type of Access db to Access db possible, practical or common?
One (possible) out of three wouldn't be bad as my managment demands
that we do it this way.

Any help, pointers, tips, fully-developed-debugged-and-already-working
code greatly appreciated.

-Steve

 

[John Nurick]

Hi Steve,

You don't even need to import the data into Access at your end. It's
easy to use a script running as a scheduled task on your server to
upload data direct from an Excel sheet to an table in an Access
database. (Any complications are likely to arise from glitches in the
Excel data, especially if it was keyed in manually, or glitches in the
WAN connection.)

One way is to use the VBScript DAO_Execute.vbs at
http://www.j.nurick.dial.pipex.com/Code/index.htm . This executes a Jet
SQL query against a local Access/Jet (mdb) database, but the the queyr
transfer data from one external source (e.g. Excel) to another (e.g. a
remote Access database, provided the connection is reliable).

Here's an example of a Windows command line using DAO_Execute to append
data from an Excel sheet to a table in an Access database:

DAO_Execute.vbs "C:\Temp\Local.mdb" "INSERT INTO tblT IN
'D:\Temp\Remote.mdb' SELECT * FROM [Excel
8.0;HDR=Yes;Database=C:\TEMP\Book1.xls;].[Sheet1$];"

The SQL statement (from "INSERT INTO onwards) is absolutely standard Jet
SQL: I pasted it from the SQL view of an Access query. It must be all
one line, but wraps round in the Windows command window. Note the syntax
for specifying the Excel workbook and worksheet.

....

If your WAN connection is not 100% reliable, working this way (or using
Access across it) risks corrupting the database. It would be better to
upload the data (in Excel or Access form) by ftp or email to the remote
system, and have a script running on that system insert the data into
the Access database's tables. (Or you could connect the remote system to
the Sybase database so it can access the latest data directly.

 

[steverob]

Hi Steve,

You don't even need to import the data into Access at your end. It's
easy to use a script running as a scheduled task on your server to
upload data direct from an Excel sheet to an table in an Access
database.

No good, have four tables in Access but data in spreads is all over the
place, there's no way to easily map it to these tables.

If your WAN connection is not 100% reliable, working this way (or using
Access across it) risks corrupting the database.

The problem is it's sloooow.

It would be better to
upload the data (in Excel or Access form) by ftp or email to the remote
system, and have a script running on that system insert the data into
the Access database's tables.

I'm leaning towards this but am thinking I'll need to use
synchronization since our business people will have modified data in
the Access db (they'll be modifying data while our spreads are being
processed elsewhere and every now and then, we'll need to upload data
from the spreads). But, it seems from what I've read that
synchronization can be something of a headache, especially over the net
or a WAN. To avoid these difficulties, I'm thinking that when we need
to do an update, we'll copy our temp Access db to the overseas server
and then run the synchronization locally. Will I need to split the db
for this? Db is currently not split.

-Steve

 

[John Nurick]

No good, have four tables in Access but data in spreads is all over the
place, there's no way to easily map it to these tables.

It's even simpler to use DAO_Execute to move data from tables in one MDB
file to tables in another than to use it with Excel, e.g.

DAO_Execute.vbs "C:\Temp\Local.mdb" "INSERT INTO tblT IN
'D:\Temp\Remote.mdb' SELECT * FROM tblT;"

The problem is it's sloooow.

I'm leaning towards this but am thinking I'll need to use
synchronization since our business people will have modified data in
the Access db (they'll be modifying data while our spreads are being
processed elsewhere and every now and then, we'll need to upload data
from the spreads). But, it seems from what I've read that
synchronization can be something of a headache, especially over the net
or a WAN. To avoid these difficulties, I'm thinking that when we need
to do an update, we'll copy our temp Access db to the overseas server
and then run the synchronization locally. Will I need to split the db
for this? Db is currently not split.

It's always a good idea to split a multi-user database, or one where the
functionality or user interface may need to be updated without
interrupting access to the data.

I'm not quite sure what you envisage by "synchronisation". This normally
implies a bidirectional processs, but surely that's not applicable if
the data at one end is arriving in Excel workbooks. Or do you mean
synchronising data in your local Sybase database and the remote Access
one? If the latter, you might consider replacing the relevant tables in
the remote Access DB with an ODBC connection to the Sybase DB, using
views and/or stored procedures on the latter to minimise the amount of
data being transferred.