Using VBA to Create/Modify a Form in Another Database

  • Thread starter Thread starter Stephen C. Smith
  • Start date Start date
S

Stephen C. Smith

Hi there --
I need to give one database the ability to remotely link to and
manipulate the forms in another database. If the VBA code was working within
the same database, I could just use CreateForm and maniuplate the form
properties, but I can't find a way to do that with a remote database?
Any suggestions? We're working on Access 97.
Thanks in advance,
Stephen
 
Why don't you manipulate it from the current database and
once the form is created, export it to the 'remote' database?

Krgrds,
Perry
 
Um, how do I do that using VBA?
Stephen


Why don't you manipulate it from the current database and
once the form is created, export it to the 'remote' database?

Krgrds,
Perry
Click to expand...
 
If you want to create a new form in a remote database, I imagine this would
work:

(untested)

dim db as database, frm as form
set db = dbengine.opendatabase ("path to other database")
set frm = db.createform (...)
' now just work with the frm object.

But, why do you actually want to do this? If the purpose is to update the
remote db with new & changed forms, would it not be better to split the
database into a so-called "front-end/back-end" structure, & just supply them
with a new front-end?

HTH,
TC
 
Oops, maybe:

db.docmd.createform (...)

or whatever. See what object CreateForm is a method of. (I don't have Access
here to check.)

HTH,
TC
 
Unfortunately, Access 97 won't let you remotely manipulate forms. I
already tried what you suggested. CreateForm is not a valid method for a linked
database object. A couple books I have confirm that.
As for "why", it has to do with my company's culture and the backwards
way we do PC technology. This project involves users with laptops who will be
entering records in the field, disconnected from our network. Currently they
submit the records via floppy disk in interoffice mail. To update the
front-end, the designer (who is not a programmer by profession or training)
drives all over SoCal manually installing updates.
I'm trying to convince them that there are programmatic ways to do all
this. For openers, there's no reason to be sending records via floppy disc.
They should be using transaction processing via the WAN. We should have one
file server here at HQ where they all just automatically upload their records
for storage.
Yes, we could just replace the front-end over the network, but they dial
in at 56K and our connections are very unstable. So rather than trying to force
a downloaded upgrade via the WAN, I was hoping that I could built a database on
our file server that would manage versions. When the user logged in to upload
records, I wanted to have an upgrade table that checked for any changes to the
front-end and implemented them programmatically, rather than deleting the entire
front-end and replacing it with something new. With our unstable connections,
it's quite likely the front-end would get deleted and the connection would drop
in mid-transfer. The user would be left with nothing.
We have real problems with computing technology at our company because
executive management puts the brakes on any project that isn't mainframe-based.
That's why the current application is so backwards. The users have to design
their own databases without involvement by our IT people. As the only one in
the building who apparently knows anything about VBA (and I'm not even in IT!)
I'm trying to at least get them to design the databases properly.
Stephen
 
Unfortunately, Access 97 won't let you remotely manipulate forms. I
already tried what you suggested. CreateForm is not a valid method for a linked
database object. A couple books I have confirm that.
Click to expand...

Are you sure? I'll check that tonight (when I have access to Access!) &
reply tomorrow.

As for "why", it has to do with my company's culture and the backwards
way we do PC technology. This project involves users with laptops who will be
entering records in the field, disconnected from our network. Currently they
submit the records via floppy disk in interoffice mail.
Click to expand...

Ok, so >data< (not program changes) are distributed via diskette.

To update the
front-end, the designer (who is not a programmer by profession or training)
drives all over SoCal manually installing updates.
Click to expand...

Ouch! The best way to distribute program changes to an Access system, is as
follows. Split the db into a so-called front-end/back-end structure. The BE
contains all of the tables (but nothing else). The FE contains all of the
queries, forms, reports, macros & modules, and has links to the tables in
the BE. Then you can simply copy a new FE directly over the existing one.
The designer simply sends a copy of the new FE to each user - on diskette,
or maybe via zipped email - & each user copies it over their old FE. Or you
give them a batch file that will do the copy for them.

The only complication in this method, is that the FE may need to include
code to relink to the BE, if the location of the BE changes (relatively
speaking) on each PC. Google these groups for "front end/back end" or
similar terms, & you'll find lots more info.

I'm trying to convince them that there are programmatic ways to do all
Click to expand...
this.

If by "all this" you are including "program changes", then - don't! Do it as
described above. You will have waaaaaaay less pain than trying to do it
programatically.
For openers, there's no reason to be sending records via floppy disc.
They should be using transaction processing via the WAN. We should have one
file server here at HQ where they all just automatically upload their records
for storage.
Click to expand...

Well, that's back to the >data< issue. I see that as seperate to the
programs issue.

Yes, we could just replace the front-end over the network, but they dial
in at 56K and our connections are very unstable. So rather than trying to force
a downloaded upgrade via the WAN, I was hoping that I could built a database on
our file server that would manage versions. When the user logged in to upload
records, I wanted to have an upgrade table that checked for any changes to the
front-end and implemented them programmatically, rather than deleting the entire
front-end and replacing it with something new. With our unstable connections,
it's quite likely the front-end would get deleted and the connection would drop
in mid-transfer. The user would be left with nothing.
We have real problems with computing technology at our company because
executive management puts the brakes on any project that isn't mainframe-based.
That's why the current application is so backwards. The users have to design
their own databases without involvement by our IT people. As the only one in
the building who apparently knows anything about VBA (and I'm not even in IT!)
I'm trying to at least get them to design the databases properly.
Click to expand...

Good luck! :-)

HTH,
TC
 
Unfortunately, Access 97 won't let you remotely manipulate forms. I
already tried what you suggested. CreateForm is not a valid method for a linked
database object. A couple books I have confirm that.
Click to expand...

Not so! (as I suspected)

The trick is, CreateForm is a method of the Application object - not the
Database object. So you can't just OpenDatabase the other database, then
CreateForm on that database reference. You have to start another instance of
Access - then use OpenCurrentDatabase to open the database in that
instance - then do CreateForm on that other instance.

I don't have the code in front of me now, but something along the following
lines worked fine for me last night:

dim oApp as object, frm as form
set oapp = createobject ("Access.Application")
oapp.opencurrentdatabase ("path to other database")
set frm = oapp.createform
' ... now do things with frm ...
etc.

HTH,
TC
 
Back
Top