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.
9 1478
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.
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.
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.
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?
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!
Great, glad the many experts on this forum were able to help! have a great day!
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
oh yes, my apologies about the 2 posts. that is correct, thank you. however, renaming the post was not correct. thanks again.
Sign in to post your reply or Sign up for a free account.
Similar topics
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......
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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
|
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...
| |