Multiple mdb Files

[one2this]

Hi,

Just wondering if anyone can assist in helping me extract data from multiple .mdb tables of different files names but each contain a table that has same name (accessories). I would ideally like to extract these to a separate table.



Thanks

[Welshgasman]

Are you saying you cannot open the mdbs yourself, or looking for code to do it?

[one2this]

Hiya, thanks for reply. I am looking for code to do this please?

[Welshgasman]

I have none.
However Google is always my first point of call.
https://www.google.com/search?q=link...hrome&ie=UTF-8

From that I found the first link https://p2p.wrox.com/access/32446-ma...ess%20database.

I would use that code with a DIR() for getting each file in turn in your folder.
Then run a query on that linked table to populate your table, perhaps with an extra field to indicate where the data came from, if you need to know that.

[Micron]

Why not just use the linked table manager to link the tables in one common database and run a make table query once? If editing/appending is required on the new table afterwards, have separate queries for that. The original post is kind of vague as to what's going on, so I might be missing something.

[one2this]

Hi, Sorry I think you have misunderstood. I have numerous mdb files that are stored in folders by month. e.g. File Name 001.mdb, File Name 002.mdb etc in January Folder and File Name 0023.mdb, File Name 0024.mdb etc in February. Within each of these files is a table called Accessories which has numerous records in each. What I need is to be able to extract this data automatically based on a selected date range and output it to a table, query or report?

[Micron]

I understood. However if the number of files/tables is large, then while that could still work, you might be wanting some other type of automated solution. I think the big problem is that the data is split up into so many files, which may be somewhat normal for Excel workbooks, not so much for relational databases. Unless a back end db that would hold all this data encroaches the 2 GB limit, then I see no reason to split it all up based on dates. Anything you do to get around that issue would be a hack, and I'd rather devote time to practical solutions. I'm confident you'll get someone to help you with that since a couple of usual responders here tend to do that sort of thing.

[CJ_London]

in VBA you can write a query along the lines of

dim fpathandname as string
dim sqlstr as string

sqlstr="SELECT * FROM accessories IN '" fpathandname & "'"

Note this is the select query, you haven't said what 'extract data' means, but can be converted to a maketable or append query or applied to a querydef to open as a query or as a recordsource to a report

you will need your own code to calculate fpathandname - might be something like

fpathandname ="C:\January" & "001" & ".mdb"

but depends on what 'a selected date range' means and how you determine what dates are in file 001 etc

If you need multiple files for a month, modify the above to be a union query

[davegri]

Here's a start. A db to list all MDB files in a folder (with subfolders option)
The results are displayed in an extended listbox, pending additional code to process the MDBs selected.

one2-davegri-v01.zip

[one2this]

Davegri, Thanks very much, yes that initial bit lets me drill down into all subfolders to list all mdbs. Is there a way to select within each of these mdbs listed the accessories table within them? Please note there is a global table that contains Job Number and Date Range that links into these but the accessories within each of these jobs is qithin a standalone mdb file called accessories?

[davegri]

Please take into consideration that I know nothing of your database(s) or their internal and external relationships and nothing at all of their table structure.
Perhaps an explanation of those concepts and an idea of how the db you are attempting to develop fits into the overall picture. An image of table relationships would be essential.

Is there a way to select within each of these mdbs listed the accessories table within them?

Selected how? To what end? After 'Selected' what happens? Do you simply mean add code to restrict the listbox to only MDBs that contain a table named accessories?

Please note there is a global table that contains Job Number and Date Range that links into these but the accessories within each of these jobs is within a standalone mdb file called accessories?

What are jobs? Where is the global table? In a db named accessories.mdb?
What db would contain the code I supplied in post#9?

[moke123]

How many backends are there? How many years worth? How big is each file?
Never a good idea to save backends AS data (ie.months)

[orange]

one2this,
In #11 davegri makes excellent points showing that readers are basically guessing the issue and offering advice.

We know so little of you, your environment, your business....
Perhaps you could step back and describe the situation in detail. A starting point may be along this scenario.
Suppose you had to give this "problem/opportunity/project" to a hired contractor/developer. What would be the requirement(s) for such?
That's what readers are facing -- no complete picture of the issue in context of you, your environment, business, priority etc.

[one2this]

Hi,


Apologies for not properly explaining the issue


Basically here is scenario.


There is a global table that stores basic job details. Eg Customer Name, Reference, Job Number and Production Date, Delivery Date etc.


From manufacturing software it then generates an individual mdb file which contains all the specific Job Details within numerous tables (including Prices, Accessories, Manufacturing Sizes etc)


For every job generated it creates an mdb file and populates it into a year / month folder on Server.


What I require it to be able to extract accessories from within a Table in these individual mdb files based on a delivery date that is held on the global table.


I can of course do it individually but that is of no use when there are numerous jobs each day / week.

[Welshgasman]

Break it down into steps.
Get a list of the files or process one at a time. You have already been given code for that step.
Link to each db in turn.
Run query to obtain data.

Start with a hard coded file to check it works, then add code to find one file, then add loop for each file in folder.
You will need to account for what has already been processed and what has not as well.