TrasferText Method (with Variables)

[goldenvision]

Here's the problem.
I have a piece of code that runs through a master table and based on specific criteria writes results out to tables. (i.e. Glasgow - 123A321, Glasgow - 123A112, Glasgow - 321B159, Perth - 987A65, etc). In total there are 34,000 tables.
I need to export these tables from access in to excel. It is V E R Y slow to do it manually.

I have searched the forum and this post seems to hint at a solution but my VBA skills are not good enough to work out the changes I need to make in order to solve my problem. Any pointers would be greatly appreciated.

David

 

[Mile-O]

In total there are 34,000 tables.

Why on earth do you have that many tables?

 

[goldenvision]

The master table contains approx 750k customers. Each customer is serviced by one of our 34k collectors. The code runs through the master table and produces a table for each individual collector with their clients. These files are then being imported into GIS software to go to the Concession offices.

 

[goldenvision]

Export EVERY table from a database

Is it possible to export every table from a database?

 

[Mile-O]

Yes, it is.

Something like:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,tdf.name,[i]File's Path[/i],True
Next
Set tdf = Nothing
Set db = Nothing

 

[goldenvision]

Thanks I'll give that a go now and keep you posted

 

[ColinEssex]

I suspect you need to review your database structure I've never heard of a database having 34,000 tables

Col

 

[goldenvision]

its fallen over with the following error

Runtime error '13'

Type mismatch

and it is debugging at

For Each tdf In db.TableDefs

 

[maxmangion]

Dim tdf As DAO.Database ... that should read as

Dim tdf As DAO.Tabledef

 

[maxmangion]

btw, don't forget to set your reference for "Microsoft DAO 3.6 Object Library"

 

[Mile-O]

Dim tdf As DAO.Database ... that should read as

Dim tdf As DAO.Tabledef

Yes, be careful with whatever I type in - I type it all in usually off the top of my head.

 

[dopedealer]

34,000 tables

 

[goldenvision]

aarrgghh!!!! it's fallen down again.

The microsoft jet database engine cannot open 'C:\Projects\' It is already opened exclusively by another user, or you need permission to view its data.

and it debugs to

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tdf.Name, "C:\Projects\", True

This directory is definitely not open.

 

[Mile-O]

You won't be able to export that much information into one spreadsheet anyway. The code I gave was only a guideline.

I know nothing about the spreadsheets this is meant to go into. I know nothing about the data's destination - is it an another database? - if so then why can't you just pass the information directly into it and cut out the spreadsheet middleman?

 

[goldenvision]

sorry bit of a misunderstanding. I need the tables exported as seperate excel files not into the same spreadsheet.

I somehow need to specify a variable in the file path section of the transfer text method

 

[dopedealer]

So your wanting 34,000 excel sheets for the 34,000 tables?

 

[goldenvision]

correct (You are really struggling to get over this 34,000 thing aren't you!!)

 

[ColinEssex]

I need the tables exported as seperate excel files not into the same spreadsheet.

is this the same 34,000 tables mentioned elsewhere? if so, are you planning on having 34,000 spreadsheets?

Col

 

[goldenvision]

correct. It seems like a lot of spreadsheets but they are going to be grouped and distributed across the company network, so I can't get away from needing all 34,000

 

[Mile-O]

correct (You are really struggling to get over this 34,000 thing aren't you!!)

I'm not struggling at all - I'm not the one who has the problem with 34,000 tables.

I personally think you should revisit your table design more than anything else as your database is obviously not normalised, and is set up to generate lots of unnecessary data which is both too much to get to grips with, a waste of man hours (by the sounds of it), and a drain on server space.

Also, this is the same question - I'm merging the two threads.