How do I copy a database object to another (remote) database? 2

[wizarddrummer]

Greetings everyone,

I’ve been searching forums and MSDN for days and have not found anything that fits exactly what I need. I’ve literally scanned I think at least a gazillion pages of information. I think now that I have probably passed several key posts. (I’m tired and I’m OLD)
So here goes.

(currently using Access 2003)

I have two Microsoft Access databases A.mdb and B.mdb.

Database A.mdb is behind a firewall on a local area
network and connected to the internet using DSL.

(using an example url)
Database B.mdb is located at service provider

http://somedomain/databasefiles/A.mdb

I would like to programmatically, using Visual Basic, copy
a database object from database A.mdb to database B.mdb.

I'm using the term Database Object to define a:
Table, Query, Form, Report, or Module.


Is there a way to do this?
If so what would be the code to perform that action?

Please consider that my goal is to mimic this functionality:

If both databases resided on the same machine and I
had both open to where I could see both Database
Windows open and I dragged a report object from database
A.mdb’s Database Window into database B.mdb’s Database
Window a plus sign would appear indicating that I was about
to copy that Report Object. As soon as I release the mouse
button the object would be copied to the other database.

That’s exactly what I am trying to do programmatically.

I originally tried using the DoCmd.Transferdatabase method, a command that I have used before with great success with copying objects but unfortunately it does not allow for the database name to have http:// in it. I get runtime errors. Other’s have mentioned that you can’t do what I want to do with transferdatabase … at least from what I can been told.

Any help would be greatly appreciated.

Regards,
wiz

 

[jksmi]

Hi,

have you looked in to using replication? Having a design master on your PC and replica on your server you can syncronize all objects and data at the click of a button.

Also, I'd be wary of moving forms and reports across as in previous version of Access this could cause corruption (don't know with 2003 as I've not tried it)

HTH, Jamie
FAQ219-2884

 

[oharab]

The reason that you haven't found a way to do what you want to do is that there is no way to do it, this is a limitation of http more than anything else.

Think about when you open a word document from a web page, the document is opened on the web server, it is copied on to your PC, in the temporary internet files probably, then opened from their. You can't save any changes to the original document.
If you could create a VPN to the website and map it as a network folder, you would be able to use transferdatabase as normal.
Assuming you can't do that, then you have 2 possible options.
1: Make your changes to a local version of database B then automate the uploading of the file to the ISP server through FTP. There are loads of routines out there for that.

2: If you are really adventurous then you could create a text file version of the database object using the undocumented Application.SaveAsText method, then create an asp script that uploads that to the server and imports it into the remote database.

Option 1 is by far the easiest, but option 2 would be a fun project! You would need to have the Access application installed on the remote server, rather than just ADO, and you would need to work out how you are going to stop someone downloading whilst you are updating, etc, but it's not impossible.

If you want any help with the specifics, post back.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[wizarddrummer]

Greetings again,
thanks for the replys. very helpful.

"Where are all the stupid people from...
...And how'd they get so dumb?"
Answer: I don't know. But, consider the average Joe ... and understand half of entire worlds population is dummer than that!

OK! points well taken, I wish there would have been a simple solution.

2: If you are really adventurous then you could create a text file version of the database object using the undocumented Application.SaveAsText method, then create an asp script that uploads that to the server and imports it into the remote database.

I had seen a reference to that and was pondering cobbling somthing with that approach in my search for the easy way out until...

The dreaded

You would need to have the Access application installed on the remote server

words were mentioned.


There are two problems:
1) Currently for proof of concept, I'm using a free account at Brinkster.com which has a .NET framework. It might have Access, I'm not sure I'll have to check that out. Also in addition the free account does not allow for ftp transfers. I can upgrade but don't want to spend the money yet. But that's not a big issue there are plenty of places that I can find for ftp access unfortunately that's only half of the problem

2)There's another wrinkle to this situation, it being the most important, that I did not mention for simplicity's sake and that's machine C. It is located at yet another remote location behind a fire wall with the typical behind the firewall address (192.168.).

The idea was to be able to create a new report or query - upload that to machine B. machine C, (there are may C's)using a timer would periodically check B to see what changes it had to make and would then make changes to its environment to match C's.

For reasons too long to explain, having MS Access on machine C is not an option. Which kills option 2 on the second leg of the operation. C machines will have the latest Run-Time version installed.

Also the users of machine(s) C are not only computer illeterate but computer phobic as well. Just about everything has to be done in the background with almost no user pro-activity.


That still leaves me with other options.

Front-end Back-end. A moves stuff to B using normal ftp programs (A's users are not computer illiterate)

A background process on C looks at date/time stamp of Front-end on B and sees whether or not it needs to pull a new version using a background ftp process of the Front-end down.

It would be almost perfect if I could put a timer on a hidden form in the Access app to do that instead of externally. Otherwize I'm thinking an executable with a timer that resides on C that performs that process (is this doable?)

I'm not familiar with the newest language specific commands for connectivity and behind the scenes ftp-ing.
Is this even a decent approach? C or VB?

Next, can you, within access connect to databases that reside on remote machines? In other words is there some way I can connect to a database located at

http://foobar.com/mydatabase.mdb

?

After connecting to the database is there a way to open a recordset from the remote db and then simply create a table in my local database and populate the information that the remote one has? Can that be done with ADO and are there special requirements for the machines involved?

I've done all of these things and more on local machines and LAN's (same domain etc.) and functions that allowed this locally with no problem.

I just don't have the commands at my finger tips that talk / connect long distance, or that move files from one place to another.


And finally, what I am trying to do is fairly simple compared to a multitude of programs I have on my machine that automatically tell me that there's new versions, new lists, new updates, etc., etc., and yet these other programs are performing functions exactly like I want to do. How are they doing that? Did they have to write assembler or C (my old native language) prgrams to create their own proprietary communication protocols?

Are there some general purpose language specific functions out there that can look at machines and see directory structures and files and dates and move stuff around back and forth etc.

OK thanks again everyone for your help.

This is the short verion of a long story about how I am now faced with supporting an existing system that has serious design flaws. No opportunity for a full rewrite yet.

Let me know if what I have said is still to ambiguous and I will try state my case more clearly. I'm sorry about the length... and no I'm not COBOL programmer. LOL
Best regards,
wiz

 

[jksmi]

Hi,

If your users are basically PC illiterate I'd be tempted to provide a solution based on static or dynamic web pages (depending on application) as this would lessen the chances of them breaking it (i.e. deleting the .md*) but also reduces the need for them to have Access runtime installed and any other conflicts or missing dll's etc... (bearing in mind you'll probably have to support it).

There are a couple of things that you could look into, like static HTML, Active Server Pages (read only) or Data Access Pages (requires office activeX controls on client machine); all supported within Access.

Or if you know any other programming languages you could look at Access as your back end or .net, DHTML or JAVA as a front end hosted by the web browser or VB (or C, etc...)

HTH, Jamie
FAQ219-2884

 

[wizarddrummer]

Thanks for all of the replys.

We've gone moved forward and are using a combination of disciplines. Remote Access of computers and VPN's

All things are progressing smoothly and I didn't have to re-invent (do lots of superfluous coding) the wheel so to speak


Regards,
wiz