By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Automated Export and Import routines from within Access via command button

P: 3
Hello ...

I have an Access 2007 app, and I need a routine to export all table data, and then import this table data into a new version of my compiled stand alone executable Access app.

I came across a situation that halted using spreadsheets as an intermediary. My customer table contains a field called "attachments" which holds.... yeah you guessed it.... attachments ! : - )
.
I lose the attachments when exporting to the spreadsheet.... so I can not use spreadsheets in my export/import for at least my customer table...
.
I do not want to split my db into a front end and a back end.....
.
My desire and hope is for anyone that would care to share their knowledge and a bit of VBA code:
.
... Is there a way to export the data to another db say, containing identical tables... (with the db under a diff name obviously.)
.
Actually what I am asking is...Is there a way to code in VBA (behind a command button) the ability to send data to another db... Say, my original db app: DB_old and the intermediary db : DB_temp.

All I would want is to copy the data from my old version db (DB_old) to another (DB_temp), table1 in DB_old to table1 in DB_temp.

Then I would also need to code a way to get data from DB_temp to my new db app, DB_new. So from within DB_new, I would click a button and it would import (copy) data from table1 in DB_temp to table1 in DB_new
.
How would I code that ? all VBA? create macros that I would call in VBA?
.
As I mentioned, I would like to have buttons that my user would click to transfer data from an old version of my compiled app to a new version of the db app via the intermediary db, DB_temp.
.
Thank you in advance....
.
Regards,
.
Bruce V. aka OASys1
Feb 4 '12 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
You're almost there already Bruce. Just have some linked tables to your intermediary database. The export would consist of appending the data from your main database into the linked tables, and the import would consist of appending the data from the linked tables into your main database. It's an even easier, and more straightforward, approach than using Excel.
Feb 4 '12 #2

P: 3
Thank you NeoPa,

I am going to try what you suggest....

I am curious... presently I have my db app locked tighter than .... well let's say its tight... No way the user can get to the db objects. I compiled it to an 'accde' and changed the extension to 'accdr'.

Can I do the same for my intermediary db to keep it tight and secure... make it an 'accde' ? and still do the importing and exporting with the linked tables method you talk of ?

Regards,

Bruce V. aka.. OASys1
Feb 4 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
If you set up a databse password for your transfer database then the linked tables will need to be set up with that password. This will be visible as part of the linked tables' .Connect string, but that requires knowledge of said .Connect string, first of all, and also access to it, which I expect you are already restricting for other users.

I expect you can ensure those ducks just keep on a-waddling ;-)
Feb 4 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.