Dual tables in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an access DB that is maintained by a third party program. I want to
be able to tap in to the database and have all the data saved to a remote DB
as well as the local tables.

Is there a way to have this done automatically? Essentially I want the
tables mirrored to a mySQL DB for access through a website.

Thanks.
 
Daiv said:
I have an access DB that is maintained by a third party program. I want to
be able to tap in to the database and have all the data saved to a remote
DB
as well as the local tables.
Click to expand...

Well, the mdb file is just a regular file. So, your question is no different
then how do I tap into a word file???

The answer is simply to just copy the file!!. So, I don't see you can't use
windows FTP, and have a schedule process to simply copy (upload) the file.
So, you can't really "tap" into a word file, but you can windows "send" that
file some where else. I suppose you could setup a VPN, and that would allow
you to "browse the directory on the computer..and see the word file..or in
your case the mdb file. Remember, a horse is a horse is horse, and a file is
a file is a file. Since a mdb file is NOT a server based system, then you
can't connect to this file any differently then any other windows file. If
you can view the file in a directory..then you can open it. This is COMPLETE
different concept then that of using a server based database were you in
fact connector the DATABASE server/system, and NOT to the actual file. So,
you can setup a VPN that would allow you to connect to that computer, and
browse files just like the network neighborhood lets you (VPN lets you do
this over the internet).

So, this approach would also work for power point, or a word document.
Remember, we are talking about a plane Jane windows file. So, whatever you
are trying to do to this file applies to any windows file...be it a jpeg, or
a mdb file.

Essentially I want the
tables mirrored to a mySQL DB for access through a website.
Click to expand...

That is a complete different problem here now. You are talking about
connecting to a server based system. when you connect to a database server,
you are NOT connecting to a file, or opening a simple windows file like
word, or PowerPoint, or in our case mdb file. As I said before, we are
talking about simple windows file system here..and grabbing a word file..or
a mdb file is not different. We don't "connect" to a word file anymore then
we connect to a mdb file.

You would get better luck asking in a mySQL group for your options in
connecting to a server base system. You certainly can connect to a server
based system, and the process of connecting to Oracle, sql server, or mySql
is really much the same concept..but you would have to talk to the
particular vendor as for the actual connection details. All of the major
database server systems do support ODBC, and ms-access can establish a ODBC
connect to one of these server systems..and transfer data.
 
This link contains the code for a backup module.
http://allenbrowne.com/unlinked/Backup.txt

It loops through all the tables in the database (whether local or
attached), ignores the sytem tables and any where you set a "NoBackup"
property, and copies theme into a new database (mdb), logging any errors
(e.g. if a table did not get copied, because it was open in design view at
the time.)

Hopefully you can adapt the concept to write to your other database.
 
It sounds like I was confusing in my post. (cheers to edmonton. Oilers
pulled it off again tonight! yeah!) anyway,

What I am looking for is when the information in the access database
changes, it saves not only to the local tables, as it is set up now, but as
well as to the mySQL DB. I know how to do one or the other, but not both at
the same time.

I am still looking for other possible solutions as well, but I don't have
real control over my linux server so I am limited on what I can do.
 
Daiv said:
It sounds like I was confusing in my post. (cheers to edmonton. Oilers
pulled it off again tonight! yeah!) anyway,
Click to expand...

Yes...they did win!!
What I am looking for is when the information in the access database
changes, it saves not only to the local tables, as it is set up now, but
as
well as to the mySQL DB. I know how to do one or the other, but not both
at
the same time.
Click to expand...

You can't have ms-access send "just" the changes as they occur (well, at
least automatically). I suppose if you timestamp the records, then you could
have ms-access *connect* to the database server..and send the data (but, you
would have to code this). So, I suppose you could cobble together some code
in ms-access that sends the data to the server based system (as a server
based system does allow a remote connection over the internet).

On the other hand, perhaps you might just link the mdb application to the
server based system, and then NEVER transfer data. You can use linked tables
in ms-access that actually "point" to your server based tables. In this
case, there would actually be NO local data in the mdb application, and you
would never need to transfer the data...as the mdb applation would be
editing the tables on the server directly anyway. This kind of setup can
work well if the designer of the mdb appcation PAID CAREFUL attention to
bandwidth requirements. I talk about remote use of ms-access here, and the
bandwidth requited:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
 
You can replace the Access DB with an identical file which contains
ODBC links instead of Access tables.

Depending how complex it is, the "third party program" may never
know the difference.

(david)
 
Back
Top