Importing multiple Access files into one Access destination file

[KrazyKT]

I have a large amount of access databases with four tables that need to be imported into one database with the same four tables. Is there a way using a module to import all the files in a directory into one access database? Append queries will not make this process quick enough. I am currently using bat files, macros, and queries. This process is still time consuming. Thanks,

KT [sig][/sig]

 

[kathryn]

Yes, you can. Use the Dir command to step through a directory and return, one by one, the names of every *.mdb file. Using that information, you could then import the table, using a saved import specification.

If that sounds like what you want, let me know. Are all the tables named the same in each table? That will make it much easier. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

K&K,

KraZy mentions &quot; ... a large ammount ...&quot;, just take a quick look and see how much data you will be putting into the one database/four tables. At some point Ms. Access just won't be able to do it. At some (MUCH) earlier point, 'She' will grow old, creaky, cranky and SLOW. My experience says that in the area of 1,000,000 records (record length ~ 260 bytes), any processing is == at least a lunch break and sometimes the rest of the day.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[kathryn]

Good point Michael. KT, how many records are we talking about? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[KrazyKT]

kathryn and MichaelRed,

Altogether about 11,000 plus database files. If we could append about 15 to 25 files at a time, that would be great. On some trial test of just normally importing 8 databases into the four destination tables, there was about 5,000 records in one table. I understand what Michael had to say. The macro craps up if you use more than 8 files. I get a warning message saying that a macro can only call itself 20 times. The databases contain four tables each and they are all standard named. EX: TblOne, TblTwo, TblThree, TblFour. So every time we receive a new database the four tables are all named the same with the same fields in the seperate tables. This atleast helps the process of appending into a destination file. Hopefully this will help you both understand the situation a little more.

If you have any questions, please ask. I'm getting desperate. I've had a few classes of Access training, but nothing compared to this current job. I've just started working a lot more with macros. Any suggestions on Access books? I would love to learn more about macros and modules.

Thanks for the help,

KT


[sig][/sig]

 

[MichaelRed]

KraZy,

I would heartily recommend that you abandon the use of Macros in favor of VBA (Code). As you note, there are limitations on Macros which do not apply to VBA, including the simple (but in my opinion VERY IMPORTANT) fact that MACROs cannot handle errors.

I am somewhat confused by your assertion that an append query is to slow, since my experience it that no one can write code which is as efficient as the Jet db enging generates. It would seem - to me - that four simple append queries, executed from code would be the most efficient (e.g. fastest) operation you could use to consolidate the data.

Another issue, is still the &quot;size&quot; of the resulting data set(s). You need to look at the record size as well as the number of records, since Ms. Access has limits on the total size of the data base.

It would also be useful to review the process a little. It is my impression that you have a &quot;Master&quot; db consisting of these four tables and you append the contents of the four &quot;New&quot; tables periodically. The New tables have approx. 11K records each. It this ~'correct'? If not, please clarify. Also, how often do you get &quot;New&quot; tables? Monthly? Weekly? Daily? Hourly? What is the Record Length of each of the New tables? Do you also &quot;purge&quot; the master data base of old data? As often as you receive new data? What is the current size of the Master database? Are there Any unique keys? Do any of the fields restrict the data (not allow nulls, include validation requirements ....?) all of these can make the transfer of data somewhat erratic/difficult.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[KrazyKT]

Well I suggested Append Queries. The boss man doesn't believe they are fast enough. Possibly this is because I originally didn't know how to pull more than one database file at a time. I figure they would work wonderfully if I learned how to pull from the multiple tables from the directory. Now to answer your questions. There are a total of 11,000 database files that have to processed in a short amount of time. The sizes of the .mdb files range from 250.kb to 1300.kb. Most of the tables have record counts varying between 2 and 1500. We will receive about 1,000 Access files a day, give or take. We are still using older software that requires the use of dbase databases. It would be to our best interest if we could process multiple files at a time. We convert them to text to append to a four generic dbase database. All the tables are set up the same. I've set the master.mdb to accomidate the format of the tables in the new databases. So it has the same indexes and field lengths. The master database doesn't need to keep the files after appending and exporting. The files will be saved out on the server and we'll have access to them if needed. I have no problem not using macros. I'm just trying to find the most efficient and quickest way to process these file correctly. It's hard trying to pull answers out of air. There are only a few people that use Access in our office. They are about as educated as I am on the software. We also have no Books for reference. So thanks for the help.

KT
[sig][/sig]

 

[MichaelRed]

KraZy,

Clartify. &quot;There are a total of 11,000 database files that have to processed in a short amount of time.&quot; Do you really mean &quot; ... database files ...&quot; or should this be records?

If you mean 11K files, then &quot; ... short amount of time ...&quot; becomes a crucial definition.

The 1K files per day of 11K records is probably doable, but will stress the I/O for most PC level systems. I think the overall problem will be difficult to deal with in this forum.

A possible improvement to the process would be to &quot;open&quot; each of the 'foregin' databases from within your master database. TGhis would allow you to 'process' each of the tables without the overhead of an actual append or import. Of course, for this to be efficient (and to avoid 'human intervention') the four tables would have to have the same name in each of the 'foregin' databases. I am assuming the naming convention is O.K., as this is also necessary for the append process to work.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[KrazyKT]

Sorry I haven't got back to you sooner. There will be a total of 11,000 Access files that we will receive over a three week period. We will get 1,000 roughly a day to process. The Access files that we have tested so far are varying from 250 kb to 1500 kb. The record count within the four tables have varied from 27 to 1,500 records. Sorry for any confusion. Hope this helps.

KT [sig][/sig]

 

[MichaelRed]

KraZy,

So, it looks &quot;doable&quot;, but not necessairly trivial.

Do you know how to 'open' the second (and other) databases from within Ms. Access? I think this will be an important step in the process, as it would bypass the time consuming import process.

Get the names of a MDB file (get this through the DIR function)

open the (A) foregin database from within the &quot;master&quot; db (one at a time!).

Do what ever processing you need on each of the MDB files (e.g. the tables therein)

Set the Foregin db = nothing.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[KrazyKT]

MichaelRed,

I am positive how to import the files within another file. I'm not really positive that I know how to open databases within one master. Do you do this by using querys or macros? Or do you use code? I looked up the Dir function. That will make my life much easier. I'm going to read through it. I've taken some Visual Basics, but it's been a year or so since I've used it. Also what is the Foregin db do and what does it mean? I'll remember to set that to nothing. Thanks for your help. I appreciate it greatly.

KT [sig][/sig]

 

[MichaelRed]

&quot;Foregin&quot; db is the one witht the data you need to manipulate. You get it's &quot;real name&quot; from the DIR() function.

You open the &quot;foregin&quot; db in code. You will also need to do all of the recordset processing in code. In general, you just dim another db (call it &quot;dbNew&quot;?). Reefer to tables therein w/ dbNew.operation(&quot;tablename&quot;, dbopendynaset), where operation is like &quot;OpenRecordset&quot;. Once opened in this manner, all of the standard operations on the recordset are available, just like thsy would be for a &quot;local&quot; table. You can write queries to operate on the tables as well, so in most ways, the tables in the &quot;foregin&quot; database are just like they are part of the local/master database.

One aspect of the 'foregin' database tables which is disconcerting to many database users when they initally use this process is that the table names DO NOT appear in the database 'window'. Since you already know the names and schema of the foregin databases/tables, this should not be a problem.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]