Transferring data with VBA

  • Thread starter Thread starter David G.
  • Start date Start date
D

David G.

Hi Folks:
I'm trying to write some CBA code that will transfer data between 2
Access databases. (It's part of code to install an updated back end
file.)

The functions I've looked at seem to copy the entire table, then
modify the imported table name because the table already exists.

I would appreciated any direction or comments.
THANKS!
David G.
 
If this is for installing a new version of an application (new front-end and
slightly modifgied back-end) the normal approach is to have code detect the
version of the back-end and then modify it via code and then add data to the
new fields if needed. Don't do a full "transfer the data process" to a new
back-end if you can avoid it. Just send out the new front-end and it
transforms the back-end to be appropriate and then links the tables etc....
Store the version in a table that has one record.

If you are trying to transfer data between two databases usually:
- import data into database into temp tables (that you know the structure)
- run queries to move data from temp tables to new tables
- delete temp tables

My two cents,
Mark

Hi Folks:
I'm trying to write some CBA code that will transfer data between 2
Access databases. (It's part of code to install an updated back end
file.)

The functions I've looked at seem to copy the entire table, then
modify the imported table name because the table already exists.

I would appreciated any direction or comments.
THANKS!
David G.
 
Thanks for commenting.

If the back end structure or any module code gets updated, your saying
the changes should be implemented by writing code in the front end to
make the changes to the back end - as opposed to modifying an empty
back end, then transferring the data.

Amy comments would be greatly appreciated.
If this is for installing a new version of an application (new front-endand
slightly modifgied back-end) the normal approach is to have code detect the
version of the back-end and then modify it via code and then add data tothe
new fields if needed. Don't do a full "transfer the data process" to a new
back-end if you can avoid it. Just send out the new front-end and it
transforms the back-end to be appropriate and then links the tables etc....
Store the version in a table that has one record.

If you are trying to transfer data between two databases usually:
- import data into database into temp tables (that you know the structure)
- run queries to move data from temp tables to new tables
- delete temp tables

My two cents,
Mark

Hi Folks:
I'm trying to write some CBA code that will transfer data between 2
Access databases. (It's part of code to install an updated back end
file.)

The functions I've looked at seem to copy the entire table, then
modify the imported table name because the table already exists.

I would appreciated any direction or comments.
THANKS!
David G.
Click to expand...
THANKS!
David G.
 
If the back end structure or any module code gets updated, your saying
the changes should be implemented by writing code in the front end to
make the changes to the back end - as opposed to modifying an empty
back end, then transferring the data.
Click to expand...

Either way works. The programmatic method is better if you are remote from the
database installation and can't (easily) directly open the backend exclusively
to make the changes using the UI - for example, if you're a consultant or
developer with databases at multiple customers' sites all over the world. If
you have the luxury of local access (or even remote terminal access) to the
machine containing the backend, and you don't need to worry about the labor of
doing the changes over and over again, then working directly with the backend
may be less work overall.
 
My case is that I do not have access to the back end, but I do have an
inside contact with access. My plan was to send the contact any
updated files (front end or back end).

As I think about your comments I am starting to see advantages of
using DAO to create/modify table defs and query defs. The code would
also act as a revision log.

What about modifying module code?

THX
u>developer with databases at multiple customers' sites all over the world. If
you have the luxury of local access (or even remote terminal access) to the
machine containing the backend, and you don't need to worry about the labor of
doing the changes over and over again, then working directly with the backend
may be less work overall.
Click to expand...
THANKS!
David G.
 
My case is that I do not have access to the back end, but I do have an
inside contact with access. My plan was to send the contact any
updated files (front end or back end).

As I think about your comments I am starting to see advantages of
using DAO to create/modify table defs and query defs. The code would
also act as a revision log.

What about modifying module code?
Click to expand...

Module code should exist only in the frontend, and will not impact the data.
You can just send the customer a new, working, tested frontend which will need
only to be relinked to their backend. The same would apply to forms and
reports. Only table structures will need any code to modify remotely.
 
As I think about your comments I am starting to see advantages of
using DAO to create/modify table defs and query defs. The code
would also act as a revision log.
Click to expand...

First off, a finished app shouldn't need much in the way of
revisions to the back end schema, so this should be a *very* rare
occurence. I've been programming Access professionally since 1996
and have never written a single line of code to do that (though I
have occasionally sent out a front end with temp table in it where a
query is run using data in the temp table, and then a query is run
to drop the temp table -- that's pretty easy to implement and test,
particularly compared to CREATE and ALTER DDL queries).

I would step back and ask you to review your process. It sounds to
me like you've gone into production use prematurely.
What about modifying module code?
Click to expand...

???

Why would you need to modify the code in your front end? Just send
out a new front end (that would apply to queries, too, BTW).

Of course, maybe you're not swift enough to have split your app into
front end and back end. If so, I hope you're not charging for your
services, as that would be completely beneath a professional level
of competence.
 
David G,

If you want to make changes to tables in a back-end database file then
have a look at the Back End Update Utility code at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

If you need to re-link the front end to the back end at the user's
site (which you probably will) then have a look at the Back End
Re-Linker code on the same site.

If the users are using a network and you need to replace a number of
front-end files on several computers then have a look at the Front End
Updater Utility which provides a VB program to do that automatically.

HTH

Peter Hibbs.
 
Thank everyone for their comments!

I could only think of one reason to modify the back end once
installed, and that was to accommodate future expansion of the
application's function. (Needing new tables to store new data.)

I also have a few queries stored in the back end that address orphaned
records. There is no code, but I'm toying with the idea of using code
in the back end to "distribute" new front end releases.

Scenario: User opens "out dated" front end which compares version info
with the back end. Front end discovers it is out dated and triggers
code in back end, then closes itself. Back end code copies new front
end to users machine overwriting the out dated front end.

If possible, I would like to cause the front end to reopen, but worst
case would be user has to reopen front end file manually.

(I omitted how I plan to get the new release so the back end can find
it, or how I will update the version info in the back end table, on
purpose. It's tedious, and also meaningless if I can't pass control to
the back end to do the file overwriting.)

Peter: Thanks for the link!

Regards,
David G.

David G,

If you want to make changes to tables in a back-end database file then
have a look at the Back End Update Utility code at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

If you need to re-link the front end to the back end at the user's
site (which you probably will) then have a look at the Back End
Re-Linker code on the same site.

If the users are using a network and you need to replace a number of
front-end files on several computers then have a look at the Front End
Updater Utility which provides a VB program to do that automatically.

HTH

Peter Hibbs.
Click to expand...
THANKS!
David G.
 
David G.

The three utilities I mentioned basically do all that (in a slightly
different way). Why re-invent the wheel, but then, it's your call.

Peter Hibbs.
 
That would be great. I've downloaded the utilities, but haven't gone
through them yet.

Thanks for your comments!
David G.

The three utilities I mentioned basically do all that (in a slightly
different way). Why re-invent the wheel, but then, it's your call.

Peter Hibbs.
Click to expand...
THANKS!
David G.
 
Back
Top