473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

code to link two tables in multiple databases in a folder

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.
Sep 12 '14 #1
9 1478
Seth Schrock
2,965 Expert 2GB
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.
Sep 12 '14 #2
twinnyfo
3,653 Expert Mod 2GB
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.
Sep 15 '14 #3
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.
Sep 15 '14 #4
twinnyfo
3,653 Expert Mod 2GB
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?
Sep 15 '14 #5
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!
Sep 15 '14 #6
twinnyfo
3,653 Expert Mod 2GB
Great, glad the many experts on this forum were able to help! have a great day!
Sep 15 '14 #7
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), False
Sep 18 '14 #8
jforbes
1,107 Expert 1GB
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
Sep 18 '14 #9
oh yes, my apologies about the 2 posts. that is correct, thank you. however, renaming the post was not correct. thanks again.
Sep 18 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: OhMyGaw | last post by:
Hey guys, Couldn't find this anywhere in google. I want a list of all database column names for a specific table/view from across database. I tried this......
2
by: David | last post by:
Hi, We have an internal network of 3 users. Myself & one other currently have individual copies of the front-end MS Access forms and via our individual ODBC links we have used the: File > Get...
1
by: RichardF | last post by:
Is it possible to run a single query that pulls data from multiple tables in multiple databases, perhaps also from multiple SQL Servers? Any input would be appreciated. Thanks! RichardF
3
by: DKode | last post by:
I am preparing to build an app that will pull data from multiple databases on the same sql server. For performance sake, is there a more "effecient" way to grab data from multiple databases at...
9
by: TC | last post by:
I need to design a system which represents multiple "projects" in SQL Server. Each project has the same data model, but is independent of all others. My inclination is to use one database to store...
1
by: foneman | last post by:
I'm running Sambar 7.0, PHP 5.2 and PostgreSQL 8.2 with WinXP. I built a database that's fed with form data, and created summaries with views and PHP scripts. I cloned the database two different...
1
by: richard.crosh | last post by:
What is the IBM recommendation for the number of DB2-LUW databases per instance on AIX? With Oracle, it is one-to-one. In DB2 multiple databases can co-exist in an instance but is this...
4
by: JoyceBabu | last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a...
20
by: Dan2kx | last post by:
Hi peeps, i have a current need to change the linked tables in my latest access project, and want to do it using VB. normally i like most people use the linked tables manager. What i am...
35
by: Mihail | last post by:
Hi ! Scenario: 3 front end databases based on the same back end database. All front end parts are in the same folder (=> I know their paths) and have the same structure of linked tables. One...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.