Update an Online database programmatically

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Does anyone know how to use VBA / Access to copy data to a table in an .mdb
file that is located on a web server?

Also, is it possible to copy/replace a table that is located on a web
server, with a local table?
 
Access doesn't recognize the HTTP protocol (nor the FTP protocol, for that
matter).

The only way you can interact with the tables is if you can actually reach
the actual share where the file is located, in which case you treat it the
same way as any other network-connected database.
 
First, take it easy on the multi-newsgroup posting..it is considered quite
rude, and you miss out on people wanting to offer answers or advice....

If you setup a vpn, then you can link to that table, or even open it in
code.

ed:

set db = OpenDataBase("path name to server\dir name\mdb.name")

you can now use the db handle to work on that remote database....

dim rst as dao.database

set rst = db.openrecordSet("select * from tblcustomers")

The above would load up a table called tblcustomers on the remote system.

However, the problem with a vpn over the internet is that your internet
connection (a high speed t1) is VERY VERY slow.

I mention the pit falls, and some other workarounds here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

So, if you can setup a vpn, you can do this..but, you have to read the above
to make note of how slow your conneciton is....
 
Access MDB doesn't allow you to update the schema; it's an awful
database selection for web development

SQL Server is prefferred for ALL web applications.


-Charlie
 
Then is there a way to use transferdatabase method to copy a table to a mdb
on a web server?
 
They have all good suggestions.

If I was you I will try web services through IIS/.NET.

If you need schema information. Then that will be a bit complicated. You can
try to save data to xml them trigger a local .dll or .mdb to import that
data (xml) into access. I haven't try this myself. But I guess it should
work.

Say "replace table": If the schema is fixed, you can delete all records then
append all new data inot it. But make sure to do "compact" in Access once a
while.

Darren
 
I use the following technique:

1) Use EM to create a local copy of the Access data as SQL Server

2) Use EM again to upload to a web based SQL server, which is the source for
the website.

Obviously, if you don't need the local copy of the data, it would be
possible to combine both actions into one process. The benefit of the two
step approch is that is the data fails for any reason, the upload to the web
can be failed ensuring that this does not leave the web without data.

NEIL
 
Back
Top