code to link two tables in multiple databases in a folder

[bytes access nubie]

I have a macro that's able to pull all the db names from a folder into a table. the 2nd piece is to link to all of the tables in those databases.

The databases each have 1 table. the table & db name are the same (eg db name COST.accdb, table name COST).

any help is appreciated.

[Seth Schrock]

I don't know how to do it using a macro (or if it can be done that way), but here is a link to a method using VBA: Relinking Tables using VBA. Post #2 gives the method for Access tables. This link only shows how for one backend. You would need to create a loop around the files found in your macro to create the linked table using the code in the link.

Just curious, why all the different database files with (based on the same name) each having the same type of data? This seems like you would benefit from database normalization. See Database Normalization and Table Structures.

[twinnyfo]

bytes access nubie,

I am on board with Seth. If you have a bunch of DBs, all with the same name, each with one Table--all with the same name, you are setting yourself up for disaster with what you are trying to do.

You need to figure out a way to consolidate the data in one location, or at a minimum, have the DBs/Tables renamed so that you will be able to keep track of where the data is coming from.

It could be that this is really what you are trying to do, so renaming would help. But, the best design will be to normalize your data.

[bytes access nubie]

just to close this loop, the databases have different names, but each would only have 1 table. I was experimenting w/access but this was to address an issue where we have a great number of DBF tables that contain historical data which 2013 doesn't recognize. We wanted to convert the tables to individual access databases. some of these tables have a tremendous amount of info, they are data sources. We have the 2nd piece of the equation that incorporates a loop to export each linked table to it's own access db. Thank you for the replies.

[twinnyfo]

nubie,

That explains much! I can only presume that these DBF tables are no longer updated, but provide historical data only?

Regardless, once you identify a particular Access DB, you can get the list of tables in that DB. You should be able to either import or link thoes Tables to another DB--either programatically or manually. Depending upon the number of files you are talking about, the preferred method would be programatically .

Are there specifics with which we can assist?

[bytes access nubie]

No, thank you. I actually marked jforbes's response as the final answer (although i'm not seeing it in this thread). his code did the trick for me. Thank you!

[twinnyfo]

Great, glad the many experts on this forum were able to help! have a great day!

[bytes access nubie]

2 things: Whoever renamed this title is INCORRECT. I was linking to MULTIPLE tables, not two. Someone incorrectly changed TO to TWO. Also, not sure why the answer is not displayed in the post by jforbes as follows:

The picture is getting clearer. I would guess the File is being found since you are getting past line 13 which is testing for it's existence. I should have realized that if that was working, the file extension is included in rst!FolderName.

So since the Extension is in FolderName, it should be stripped out when referring to the tables. You might want to try this for line 14:

DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), False

[jforbes]

I think part of the confusion is that there are two post for this. Here is the other:http://bytes.com/topic/access/answer...ce#post3779415

[bytes access nubie]

oh yes, my apologies about the 2 posts. that is correct, thank you. however, renaming the post was not correct. thanks again.