Is there any tool that will 'concatenate' say 300 ".mdb" files intoa single .mdb file?

S

ship

Hi

Is there any way to concatenate entire Microsoft ".mbd" files?

Background:
Every day or so we are downloading data from our site. This downloaded
data is in ms Access (2003?) format.
So we now have about a years worth of msAccess files (i.e. about
300 .mdb files) - each of which has about 12 tables in it.

Is there any utility that would be able to process all 300 files and
put all the data into a single new .mdb file containing all the data
for the year (in the 12 tables)

Ship
Shiperton Henethe
 
S

strive4peace

Hi Ship,

Does the data contained in your tables identify the data set or do you
need to add a field to identify which day the data came from?

There is no utility pre-built that I know of. It am estimating it will
take a few hours to possibly a day to write the code. If you are
proficient with VBA, we can get you on a path. If you are not, you
should get a good foundation so you can understand the advice we give
you. Read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Once you have Access Basics under your belt, tell us what kind of
information your tables contain

I am assuming you can just put all the databases in a single directory
-- so code could loop through the directory, reference each database,
link to each table, append records, then delete links and move to the
next db

Warm Regards,
Crystal
remote programming and training


*
:) have an awesome day :)
*
 
D

Dirk Goldgar

ship said:
Hi

Is there any way to concatenate entire Microsoft ".mbd" files?

Background:
Every day or so we are downloading data from our site. This downloaded
data is in ms Access (2003?) format.
So we now have about a years worth of msAccess files (i.e. about
300 .mdb files) - each of which has about 12 tables in it.

Is there any utility that would be able to process all 300 files and
put all the data into a single new .mdb file containing all the data
for the year (in the 12 tables)

Ship
Shiperton Henethe


I wouldn't use the word "concatenate", but I understand what you want to do.
I don't know of an existing tool to do that, but it wouldn't be hard to
program. The basic procedure of the code would be open a connection to each
database in turn and run a series of append queries to append the data from
each table to a the appropriate table in the target database.

Are all the source databases in a folder together? Do they have names that
are constructed according to a standard pattern? Is this a process that,
once done, will have to be carried out repeatedly with new source databases?

With more complete information, we may be able to give you some rudimentary
code to carry out the job.
 
J

John W. Vinson

Hi

Is there any way to concatenate entire Microsoft ".mbd" files?

Background:
Every day or so we are downloading data from our site. This downloaded
data is in ms Access (2003?) format.
So we now have about a years worth of msAccess files (i.e. about
300 .mdb files) - each of which has about 12 tables in it.

Is there any utility that would be able to process all 300 files and
put all the data into a single new .mdb file containing all the data
for the year (in the 12 tables)

Ship
Shiperton Henethe

Ow Ow Ow.

No specific utility; it's either going to be a big job, running 300x12 Append
queries, or a monstrously difficult one, running 300x12 Append queries each of
which must deal with duplicate data and conflicting autonumber assignments.
It's going to require some careful analysis and design to come up with a
custom solution for your particular database structure.

Whoever came up with the idea of treating .mdb files as if they were
spreadsheets/documents needs to learn a bit about Access.
 
S

ship

Does the data contained in your tables identify the data set or do you
need to add a field to identify which day the data came from?

To clarify, each .mdb file that has been downloaded contains the exact
same list of tables
with exactly the same field names.

i.e. Tables will exist and be of the correct structure irrespective of
whether they contain
any actual data.

Ship
 
S

ship

Are all the source databases in a folder together?  Do they have names that
are constructed according to a standard pattern?  Is this a process that,
once done, will have to be carried out repeatedly with new source databases?

We can put the database files anywhere you like!
At present they are have manually been renamed with the download date
burnt into the name, and look something like this:

"Web_Download_20090109.mdb"
Occassionally, where we have had to do two downloads per day they have
been
renamed accordingly to look something like this:
"Web_Download_20090109-1.mdb"
"Web_Download_20090109-2.mdb"


From a business perspective this is an extremely simple thing we are
trying
to do.

i.e. Take all the data in the .mdb files in THIS directory (all of
which have an IDENTICAL structure)
and aggregate it into a single .mbd in THAT directory.

Are you sure that noone has written anything that will do it ? (!)


Ship
 
S

strive4peace

Hi Ship,

I was asking about your data so I can make directions more clear. What
kinds of tables do you have? Do your table or field names use spaces or
special characters or are they just just alphanumeric? do any table or
field names start with a number?

A little idea about your data would be really helpful, thank you

Have you used VBA? If so, how much?

What version of Access are you using?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
D

David W. Fenton

Whoever came up with the idea of treating .mdb files as if they
were spreadsheets/documents needs to learn a bit about Access.

Actually, not Access, but databases in general.
 
D

David W. Fenton

I don't know of an existing tool to do that, but it wouldn't be
hard to program. The basic procedure of the code would be open a
connection to each database in turn and run a series of append
queries to append the data from each table to a the appropriate
table in the target database.

Er, maintaining referential integrity between 300 different data
sets is easy? I beg to differ. It seems to me that it would be
fairly complex to program and might involve some staging tables
before the data gets imported into combined database.

I don't see this as a simple process at all.

And whoever set up the 300 databases that need to be combined should
be fired for lack of foresight.
 
D

Dirk Goldgar

David W. Fenton said:
Er, maintaining referential integrity between 300 different data
sets is easy? I beg to differ. It seems to me that it would be
fairly complex to program and might involve some staging tables
before the data gets imported into combined database.

From the original question, I made the assumption that we are dealing with
unnormalized data, and that referential integrity will not be an issue. I
could well be mistaken, but the very fact of the request suggests a
spreadsheet-like approach to the data. We'll see.
 
T

Tony Toews [MVP]

John W. Vinson said:
Whoever came up with the idea of treating .mdb files as if they were
spreadsheets/documents needs to learn a bit about Access.

Hold on a sec here.

There could be very valid reasons for choosing to output the data in
MDB format from their website. For example maybe it's some kind of
Ecommerce site and these are the daily transactions. If so I'd far
sooner see these in a normalized Access MDB than I would in an Excel
spreadsheet or CSV file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

Dirk Goldgar

ship said:
We can put the database files anywhere you like!
At present they are have manually been renamed with the download date
burnt into the name, and look something like this:

"Web_Download_20090109.mdb"
Occassionally, where we have had to do two downloads per day they have
been renamed accordingly to look something like this:
"Web_Download_20090109-1.mdb"
"Web_Download_20090109-2.mdb"


From a business perspective this is an extremely simple thing we are
trying to do.

i.e. Take all the data in the .mdb files in THIS directory (all of which
have an IDENTICAL structure) and aggregate it into a single .mbd in THAT
directory.

Are you sure that noone has written anything that will do it ? (!)

Here's an example routine that I just put together for an import form having
text boxes to define a source folder and an archive folder (to which the
source databases will be moved after the import, preventing repeated imports
of the same data), and a command button to trigger the import:

'----- start of example code -----
Private Sub cmdImport_Click()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Dim strSourceFolder As String
Dim strArchiveFolder As String
Dim strSourceDatabase As String
Dim strTargetDatabase As String
Dim strTable As String
Dim strSQLTemplate As String
Dim strSQL As String

Dim lngDBCount As Long
Dim lngTableCount As Long
Dim lngRecordCount As Long

strSQLTemplate = "INSERT INTO [%d%].[%t%] SELECT * FROM [%t%];"

' Pick up folder paths from text boxes on form.
strSourceFolder = Me.txtSourceFolder
strArchiveFolder = Me.txtArchiveFolder

strSourceDatabase = Dir(strSourceFolder & "Web_Download_*.mdb")

strTargetDatabase = CurrentDb.Name

If Len(strSourceDatabase) = 0 Then
MsgBox "No databases were found to import."
Exit Sub
End If

Do While Len(strSourceDatabase) > 0

Set db = Application.DBEngine.OpenDatabase( _
strSourceFolder & strSourceDatabase, , True)

lngDBCount = lngDBCount + 1

' Import all the tables from the source database
' that match the (user) tables in this database.

For Each tdf In db.TableDefs

' Only process non-system tables.
If Left$(tdf.Name, 4) <> "MSys" Then

strTable = tdf.Name

' Construct SQL of append query.
strSQL = _
Replace( _
Replace(strSQLTemplate, "%d%", strTargetDatabase), _
"%t%", strTable)

' Execute the append query.
db.Execute strSQL, dbFailOnError

' Increment counts
lngRecordCount = lngRecordCount + db.RecordsAffected
lngTableCount = lngTableCount + 1

End If
NEXT_TABLE:
Next tdf

db.Close

' Move file from source folder to archive folder.
Name strSourceFolder & strSourceDatabase _
As strArchiveFolder & strSourceDatabase

' Get name of next database file in folder.
strSourceDatabase = Dir()

Loop

Set db = Nothing

MsgBox _
"Imported " & lngRecordCount & " records from " & _
lngTableCount & " tables in " & _
lngDBCount & " databases.", _
vbInformation, _
"Import Complete"

Exit_Point:
On Error Resume Next
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Sub

Err_Handler:
If Err.Number = 3192 Then
' Source table not found in target. Ignore it.
Resume NEXT_TABLE
End If

MsgBox _
Err.Description & vbCr & vbCr & _
"Database: " & strSourceDatabase & vbCr & _
"Table: " & strTable, _
vbExclamation, _
"Error " & Err.Number

Resume Exit_Point

End Sub
'----- end of example code -----
 
J

John W. Vinson

There could be very valid reasons for choosing to output the data in
MDB format from their website. For example maybe it's some kind of
Ecommerce site and these are the daily transactions. If so I'd far
sooner see these in a normalized Access MDB than I would in an Excel
spreadsheet or CSV file.

Well... maybe. IF there were a procedure in place, *before the very first file
was downloaded*, for consolidating the data after downloading. Not otherwise!
 
T

Tony Toews [MVP]

John W. Vinson said:
Well... maybe. IF there were a procedure in place, *before the very first file
was downloaded*, for consolidating the data after downloading. Not otherwise!

Business requirements change over time. Maybe the web devs figured
they'd give the users a daily transaction file even though the user
hadn't explicitly stated they needed it or even knew what they would
want to do with it. I can certainly see me doing such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

David W. Fenton

From the original question, I made the assumption that we are
dealing with unnormalized data, and that referential integrity
will not be an issue. I could well be mistaken, but the very fact
of the request suggests a spreadsheet-like approach to the data.
We'll see.

The original post says there are 12 tables. Sure, they could be
completely unrelated with no RI. Sure. Absolutely.
 
D

Dirk Goldgar

David W. Fenton said:
The original post says there are 12 tables. Sure, they could be
completely unrelated with no RI. Sure. Absolutely.


You could well be right -- to me, the number 12 suggested 12 months of
unnormalized data, but that is definitely broad-jumping to a conclusion.
We'll just have to see.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top