J
Jesse
In short - What is a good way to do daily updates from a webstore's
mysql database into an Access database?
I am a novice-level Access user.
I am working with a friend who has a webstore. The purchase
transactions are stored in two tables -- tbl_trans and tbl_details.
The first table is the order basics (who, when, total) and the second
table holds the details of what was ordered (one record per item
ordered). They are related via a unique order_id number.
Up to now, each day two downloads are done to get new order records
from each table and that data is then imported in two imports into
Access 2000.
Instead, what we would like to do is "connect" to the mysql database
each day and have all new records from tbl_trans and tbl_details added
automatically to the local Access database (which has lots of
additional tables and queries for store management and order
fulfillment). These new records would be appended to the local
transaction and detail tables.
(In a perfect world, this probably would be a single integrated
database, but the store will be using a new off-the-shelf shopping
cart and that is a limiting factor.)
So, some questions:
1. Is this approach generally practical -- using Access to connect to
the mysql db to get new records? If not, is there a "standard/good"
way to do this?
2. If this can be done, will Access lock the mysql database when it is
connected -- will it prevent the store from operating because the
shopping cart cannot connect to the db?
3. While I understand how to combine data from two tables in a single
db, here it _seems_ that we are trying to combine data from two
databases. How does/can that work?
4. The above steps anticipate a read-only connection to the mysql db.
Can Access also update a field in a mysql table?
5. Do any of these answers change if Access 2007 is being used? If
that would help a lot, I think I can inspire my friend to spring for
it.
Advice and/or suggestions would be appreciated. And, if you happen to
know of a good link describing the process involved, that would be
great. Thanks. -- Jesse
mysql database into an Access database?
I am a novice-level Access user.
I am working with a friend who has a webstore. The purchase
transactions are stored in two tables -- tbl_trans and tbl_details.
The first table is the order basics (who, when, total) and the second
table holds the details of what was ordered (one record per item
ordered). They are related via a unique order_id number.
Up to now, each day two downloads are done to get new order records
from each table and that data is then imported in two imports into
Access 2000.
Instead, what we would like to do is "connect" to the mysql database
each day and have all new records from tbl_trans and tbl_details added
automatically to the local Access database (which has lots of
additional tables and queries for store management and order
fulfillment). These new records would be appended to the local
transaction and detail tables.
(In a perfect world, this probably would be a single integrated
database, but the store will be using a new off-the-shelf shopping
cart and that is a limiting factor.)
So, some questions:
1. Is this approach generally practical -- using Access to connect to
the mysql db to get new records? If not, is there a "standard/good"
way to do this?
2. If this can be done, will Access lock the mysql database when it is
connected -- will it prevent the store from operating because the
shopping cart cannot connect to the db?
3. While I understand how to combine data from two tables in a single
db, here it _seems_ that we are trying to combine data from two
databases. How does/can that work?
4. The above steps anticipate a read-only connection to the mysql db.
Can Access also update a field in a mysql table?
5. Do any of these answers change if Access 2007 is being used? If
that would help a lot, I think I can inspire my friend to spring for
it.
Advice and/or suggestions would be appreciated. And, if you happen to
know of a good link describing the process involved, that would be
great. Thanks. -- Jesse