"KyssMe143" wrote in message
> Thanks so much for your assistance... I had found similar code in the help
> file as what you posted, but for some reason it just wouldn't cooperate
> with me and kept giving me an error message. I copied and pasted the code
> directly from your response into a public function and tested it, and IT
> WORKS GREAT! :)
> Of course the scenario I gave in my help request is a lot simpler than
> what I actually need to do, but it encompasses the actions I need to
> perform. The reason I need to create and modify the tables like this is
> because I am dealing with home insurance policies, where I am importing
> over 800,000 records from multiple delimited text files directly into an
> access 2007 database, and then combining about 46 fields spread across the
> newly imported tables into one table. The employee before me just used
> make table queries, but they don't allow you to set the field data type,
> size (for text fields), turn unicode compression on (text fields), or
> index certain fields... it seems the only way to do that is
> programmatically or manually, and the goal is to automate this process
> entirely and optimize the performance of the database at the same time. I
> found out the hard way that once a table has say 800,000 records in it,
> you cannot change anything about the existing fields because access gives
> you an error message saying that it doesn't have enough resources (and my
> computer has 8gb of ram with a quad-core processor). So the solution is
> in creating and setting up the table with code before any records are
> inserted into it.
> Which leads me to the next question: I want to automate the import
> process also, where I am importing the delimited text files into access
> initially. I'm sure this can be done with code, just don't know how and
> am still looking. I would like to write code in an host access database
> that takes the delimited text files (always the same name and always the
> same location), creates a new database with the name of the new database
> being the same always except that the current date be added to the end of
> the name, imports those delimited text files (with whatever settings I
> choose) into the new database, then creates my actual working table and
> inserts the data from my five delimited text file tables into the working
> table. So in effect I would have a separate database that I use every
> month that totally automates the database processes for my company and
> creates my new database for me... that would be the sole function of said
> separate database. If you can point me in the right direction then that
> would be great.
Hmm ... I don't think I have the full picture, but I do have a couple of
comments on things you've said. Maybe they'll be helpful, maybe not.
First, when I want to import text files into a database, and particularly
when I have to combine information from multiple text files into one table,
I don't import the text files and then move data from the imported tables
into the final table(s). Instead, I link to the text files and run append
queries based on those linked tables. Doing it that way vastly decreases
the database bloating -- and I can see from your comments about the "not
enough resopurces" message that bloating is a problem in this case.
Second, any process that requires me to dynamically create tables and fields
is suspect. I tend to do that only when I am running an upgrade process on
an installed database application. If your process does require you to
create tables dynamically, but the structure of those tables can be known in
advance, why not create template tables (empty, maybe hidden) with the exact
field definitions and properties you need, and copy those tables using
DoCmd.CopyObject or DoCmd.TransferDatabase?
Third, are you talking about creating one or more new databases every month,
to contain the same sort of data? If I were in your position, I'd be
looking hard at whether I can keep all months' data in a single database.
Is there never any need to compare data across months? Is there no shared
data between the months?
If there's too much data for an Access database to hold multiple months,
maybe you ought to be looking at a SQL Server back-end to store it all. But
before deciding that, I would make sure I minimize bloating in the import
process (as described above), delete any unneeded data from the import
process, and compact the final database, to see just how big the optimized
database really is.