Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

VB Code To Export Access Back End Table to .mdb file.

Hi Experts,

I need VB Code for User 1 to run in a Form from a Command Button to:
- Export a single Back End Table to a .mdb File.

The file will then be emailed to User 2.  I know how to do this.

I need VB Code for User 2 to run in a Form from a Command Button to:
- Import the data from the emailed .mdb File into a table identicalin structure to the one exported by User 1.

Thanks.  Bob Collison
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Bob,

 Do you envison the app opening the e-mail on it's own to get the data or will the user have already saved the attachement to disk for importing?

 Hopefully the latter (the former is a bit difficult), which can easily be accomplished and a few different ways.

 I would suggest haveing a linked table to the downloaded DB.  As long as you don't try to use the link, it doesn't matter if the DB is there or not.

 With the data as a linked table in your DB, doing a simple DoCmd.OpenQuery is all that is needed (which would be an append query to move the data from the linked table to your local table).

Note this is only one way to do this.  You could also open the exported DB in code and read the table using DAO or ADO and move the data yourself.

Jim.
Avatar of Bob Collison

ASKER

Hi Jim,
I planned that User 2 (Email Receiver) will save the table to a Folder as a .mdb.

I had anticipated that the Table File.mdb would not necessarily have the same Table Name as the table(s) that will be updated by User 2.  This will allow copies of the table containing different data to have different names.  So that may negate/ complicate the 'linking' option.

Base on the above, I also anticipated as you suggested 'Opening' the exported .mdb File and using VB Code to copy from it to User 2's table(s).  This seems like the most flexable option especially when the data in the Exported Table may end up in multiple (Back End) tables in User 2's system.

I'm open to suggestions.
Thanks.  Bob Collison
<No Points wanted>

<I know how to do this.>
...but have you done it already?
In most cases .mdb files are blocked by Outlook, you need to do something like this to allow them:
http://email.about.com/cs/outlooktips/qt/et101601.htm
Hi Boag2000,
I was aware of this (and have done it in the past) although I must admit I had forgotten about it while doing this.
Thanks for the reminder.
Bob Collison.
You can use TransferDatabase to move a table from one db to another:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\YourDestinationDatabase.mdb", acTable, "SourceTable",  "Destination Table in YourDestinationDatabase"

Assuming YourDestinationDatabase exists, this would move SourceTable into the database.
<<Base on the above, I also anticipated as you suggested 'Opening' the exported .mdb File and using VB Code to copy from it to User 2's table(s).  This seems like the most flexable option especially when the data in the Exported Table may end up in multiple (Back End) tables in User 2's system.>>

  TransferDatabase as LSM suggested or do it in code.   If you've worked with recordsets before, it's only a slight difference.  Instead of :

 Set db = CurrentDB()

 you do:

 Set db = dbEngine.Workspace(0).OpenDatabase("C:\somepath", False, True)

  at that point, you have a reference to the database and can open a recordset on a query or table in it:

Set rst = db.OpenRecordset(strTableName, DB_OPEN_TABLE)

  just as you normally would and then loop through the records and save them to whatever table(s) or database(s) you wish.

  You can open as many databases as you want within the current workspace (ie. if your user has more then one DB the data is being moved to, then you'd have the import DB, plus the others).

Does that get you started or do you need more?

Jim.
You can also use the IN keyword to move data to a remote table:

Currentdb.Execute "SELECT * INTO RemoteTable IN [C:\SomeFolder\YourRemoteDB.mdb] FROM YourLocalTable"

See this:

http://blogs.office.com/b/microsoft-access/archive/2009/03/27/accessing-external-data-using-the-in-clause.aspx

http://msdn.microsoft.com/en-us/library/bb177907.aspx
Hi Jim,
I beleive I have everything I need to try putting it together except fo one thing.

I'm not sure that a solution has been provided for code to simply copy a Backend Table to a NEW Database.  i.e. One not already existing.  Can this be done in one step and if so how?  I believe the code you provided is to copy to an 'Existing' database but intially won't exist, just the same as an export to a file like a .xls wouldn't exist.
Thanks.  Bob Collison.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Jim,
I have it working 99%.  The only thing not working is that when I create the empty table its not empty.  Here is the code.
Dim DbNew As DataBase
Set DbNew = DBEngine.Workspaces(0).CreateDatabase([DB_PATH_FILENAME], dbLangGeneral)
DoCmd.TransferDatabase acExport, "Microsoft Access", [DB_PATH_FILENAME], acTable, "08_SYSTEM_VENUE_MSTR", "08_SYSTEM_VENUE_MSTR_EMPTY", True

My uderstanding is the 'True' parameter should cause the table to be created empty.  i.e. No records.
Can you assist?
Thanks.  Bob Collison
Bob,

<<
My uderstanding is the 'True' parameter should cause the table to be created empty.  i.e. No records.
>>

 That is correct.  If the 7th argument is True, only the table structure should be copied.

 You got me...all your arguments are in the right place and I know this works, so I'm left scratching my head a bit.

 Let's see if we can't figure this out.  

 Put a breakpoint on the DoCmd line.  Execute and when you hit it, open the newly created DB with a second instance of Access.   It should be blank at this point.

 Now jump back to the first instance and hit F8 to execute the DoCmd.  Back to the second instance and refresh.  The table should now be there...is it empty or full?

 Let me know.
Jim.
Hi Jim,
I followed your instructions to the letter.

- Inserted Break Point.
- Ran to Break Point.
- At the Break Point there was no table (or entries) just an empty OUTPUT.mdb.
- Pressed F8.
- Closed the New .mdb.
- Opened the OUTPUT.mdb.  It containd the table plus the records from the original database / table.

I'v attached a complete copy of the code in case I have something else messing it up.
Thanks.  Bob Collison.
Post a DB with just the one table in it that your copying please.  And I don't need all the records.  Just a handful is fine.

 and what version of Access are you using?

Jim.