Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Monday, December 10, 2012 3:36 PM
Hello,
We have multiple MS Access files currently we use linked tables to group them together.
Yesterday I saw a demo that someone could create one ODBC against one MS access file and be able to see all tables in multiple MS Access files. My understanding is that they did not use linked table. What I was told was to use the same schema in multiple MS Access databases then I could see them at a shot which doesn't make sense to me.
May I know what kind of set up allows me to point to one MS Access file and be able to see tables from several DBs.
Thanks in advance.
Terry
Friday, December 14, 2012 2:25 AM ✅Answered
Hi,
If you need to build a simple database for a home or small business, Microsoft Access is an ideal product that is reasonably easy to learn. Otherwise, SQL Server is more commonly used in business environments, with database applications produced by programmers or specialized companies.
Since your databases cannot combined into one due to size issue, then maybe SQL Server is more suitable.
Also, you can import or link data from ODBC databases, such as Microsoft SQL Server and Visual FoxPro, as well as other programs that provide drivers compliant with ODBC Level 1 to access their data files. To do this, you must be connected to the appropriate ODBC data source. To connect to an ODBC data source, you must have the correct ODBC driver installed and a data source name defined.
When you import a table into an Access project, Microsoft Access imports the table data, data definitions, and primary key but not its other properties (including constraints, relationships, and indexes).
For more information, please refer to the following link:
http://office.microsoft.com/en-us/access-help/administer-odbc-data-sources-HA010275550.aspx
If you do not want the users have the right to read the tables, then maybe we can split the database. The most common reason to split a database is that you are sharing the database with multiple users on a network. If you simply store the database on a network share, when your users open a form, query, macro, module, or report, these objects have to be sent across the network to each individual who uses the database. If you split the database, each user has their own copy of the forms, queries, macros, modules, and reports. Therefore, the only data that must be sent across the network is the data in the tables.
Refer to the following link:
http://support.microsoft.com/kb/304932
Jaynet Zhang
TechNet Community Support
Tuesday, December 11, 2012 9:31 AM
Hi,
I am not sure, what I understood is you want to combine multiple Access database into one.
Then how about importing other databases? It will show all the tables together.
Jaynet Zhang
TechNet Community Support
Tuesday, December 11, 2012 5:01 PM
Hi,
Importing tables is not something I am looking for. Let us say you have 3 Access DBs. One hosts customer info, one host product info and the last one host sales info. For some reasons, we can't combine those 3 DB files into one such as the file size issue. Is there any way we can group these 3 files logically? Let us say how I can access these 3 databases using one ODBC?
Thank you so much for your helps.
Terry
Monday, December 17, 2012 2:50 PM
Hi, Thank you for your reply. I am not sure whether we understand each other properly. My question was simply. I saw someone who used one ODBC and connected 3 individual Access database files. One ODBC allowed him to see tables under 3 Access database files. I just want to know how I can do it. I saw how he created ODBC. He did not specify 3 Access database files. He only specified one and somehow he could see three. Any ideas. Thanks. Terry
Tuesday, December 18, 2012 6:08 AM
Hi,
Do you sure "he" you mentioned linked to the data source is three Access databases? Didn't he combine multiple databases into one?
ODBC is a connection that is created to define a connection between a computer and a database stored on another system.
Jaynet Zhang
TechNet Community Support
Tuesday, December 18, 2012 6:08 AM
Hi,
Do you sure "he" you mentioned linked to the data source is three Access databases? Didn't he combine multiple databases into one?
ODBC is a connection that is created to define a connection between a computer and a database stored on another system.
Jaynet Zhang
TechNet Community Support
Tuesday, December 18, 2012 2:29 PM
Hi Jaynet,
I saw he created ODBC and only linked to one Access DB. Once he used the ODBC he just created, he could see 3 Access database files. I also checked physical 3 MS Access files.
He got those 3 MS Access files from someone else so he did not know how to set it up which allows him to do it. He thinks by using the same schema might allow him to do so which I disagree. That is why I post it here. I was wondering how to set up MS Access to allow one ODBC to see tables from three MS Access files.
Thanks.
Terry