Export Tables Via Code

[sambo]

I have 9 Inner Joined Tables that I would like to export to a temporary database every night via code.

Here is the long and short. I'm doing a data dump once per day across networks. I don't want to dump the entire .mdb file (its too big), so I'm just going to get the data I need and store it in a new (temporary) .mdb that includes only the data tables (not the reference tables, forms, reports, or any other bells and whistles.) This should cut way down on the size of the file to be transfered.

I would like to export each of the nine tables from "old.mdb" to "new.mdb" via code.

Here is a look at the tables that I would like to export.

Any suggestions..

 

[Pat Hartman]

"Flattening" these tables will result in a larger storage requirement than would exporting the nine separate normalized tables because of all the text fields that would be duplicated. In addition, the query itself is invalid since it contains two unrelated 1-to-many joins. These unrelated joins (the last two tables in the picture) will result in the production of a cartesian product.

If these are the only tables in the database, you should split your application so that the data is in one database and all the other objects are in another. Then you can backup the entire "data" database. If there are more tables, then teh most space efficient method is to export the 9 tables to separate .csv files.

 

[sambo]

Sorry for the confusion. I just put the tables in the query builder as a visual guide. I don't actually want to export the query, just the tables that were shown. And no, these aren't the only tables. These are merely the tables that will recieve new data daily. All other tables are reference tables.

As for flattening.. That is not my intention at all. That is why I wanted to export the tables (in their entirety). I understand that encompassing all of the data into one query would, in essence, defeat the purpose of having a normalized table structure.

That leads me back to my original question. Is there a way to automate the table exports daily?
What about the .csv files? I'll need some guidance there.

 

[Jon K]

Try using the IN Clause in a make-table query. For example, the following will export the table to D:\AA\New.mdb.
--------------------------------
Dim SQL As String

SQL = "Select * into TableName IN 'D:\AA\New.mdb' from TableName"
DoCmd.RunSQL SQL
---------------------------------

The IN Clause can also be used in an Append Query, e.g.

SQL = "Insert into TableName IN 'D:\AA\New.mdb' Select * from TableName"
DoCmd.RunSQL SQL

 

[sambo]

Is there a way to Make the Table to a different network (through 2 firewalls, out of one and into the other)?

Right now I simply run a batch file that emails the entire "remote" database (after the tables have been made) to my email address. This process works, but is kind of cumbersome. Cutting out the middle man and Updating the tables automatically would be desirable.

 

[kaymc]

a suggestion...

Not an Access expert, but have you looked at:

DoCmd.TransferDatabase

It is my understanding that you can use ODBC datasources and move stuff just about anywhere you like.

 

[sambo]

Jon..

Is there a way to do an append query to a different network.

Similar to this:

SQL = "Insert into TableName IN 'HomeNetInOregon\D:\AA\New.mdb' Select * from TableName"

The part I am curious about is the HomeNetInOregon piece. Is there some sort of connection string or something that I could insert here in order to access a network at a remote location (server)?

 

[Jon K]

Sambo,

I don't have much experience in network. I think if network admin can assign a drive letter on your workstation for that remote server, then Access can access the server as if it were accessing one of the drives on your workstation.

Hope others in this forum who have network experience can offer some help.

Jon

 

[ghudson]

You can use the server name and the name of the partition that the folder exits on instead of a drive letter which is the best way to go since you never know what drive letter a person might assign on their computer.

Example:

DoCmd.TransferDatabase acImport, "Microsoft Access", "\\ServerName\PartitionName\Folder1\TestDatabase.mdb", acTable, "tblData", "tblUpdates", False

HTH

 

[sambo]

Don't I have to connect to this remote server or something? I will assume that just because I use the server named \\Blah... that doesn't mean that I am going to get into server \\Blah..

Keep in mind that \\Blah is in a completely different state, on a completely different network, at a completely different company.

 

[ghudson]

I guess you are right about that. I have never had to connect to an outside server before. Maybe somebody else with experience can jump in and help you. Good luck!

 

[sambo]

That seems to be a common theme. I think I may have found a way around it.

Is this method crazy??
1. Create tempDB
2. Export DataTables ONLY to tempDB
3. Compact tempDB
4. Execute a WZZIP Shell to Zip tempDB
5. Email tempDB.Zip from Remote Location to Local Location

I've managed to automate this entire process and put it on a timer that runs once a day. It has taken me almost 2 days to refine the thing how I want it, but it has really cut down on the size of the file being transfered.

Any alternative methods??