Hi,
I have an experience for running queries.
When I use query to run the linked tables. It is faster.
If I use query to run the tables (not linked), it is slowly.
I did both ways.
Can anybody tell me why running the linked tables by queries is faster than the no linked tables?
Thanks.
I am trying to create a query based on two tables with a many to many relationship.
The basic sketch of this relationship is one table has our employee information and the other has the phone information. Employees share some phone numbers so it has to be many to many and I have an employee_phone linking table.
When I create a query, I have no records in my phone or my employee_phone tables, but my employee table has a full list of employees. I want to use the query to enter the phone numbers in for existing employees, however when I put in the employee's name and then add the phone information, it creates a new record in the employee field. I just want it to tie new phone information to existing employee records.
If anyone can help me with this, I would be very greatful! Thanks
Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.
View 1 Replies View RelatedCan we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?
View 4 Replies View RelatedI have two linked tables (Unique_ID)
Table1 has a record of unique tasks.
Table2 has a list of people to do those tasks
They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.
I have a select query that can show me these like so.
table2 table1
Unique_ID Unique_ID
Like * Not Like *
I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.
I have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
View 3 Replies View RelatedI have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;
I have two tables,
Table A:
Year Type Count
1992 Walk 82
1992 Bike 43
1992 Vechicle 298
1992 Ship 3
1992 flight 3478
1993 Walk 47
1993 Bike 13
1993 Vechicle 638
1993 Ship 24
1993 flight 3111
1994 Walk 0
1994 Bike 8
1994 Vechicle 1298
1994 Ship 24
1994 flight 3234
Table B:
ID Type
T1 Walk
T2 Bike
T3 Vechicle
T4 Ship
T5 flight
The two table linked by the [type]
I need a query expression like: SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2',[TableA].[Count])
FastWay: IIf([TableB].[ID]= 'T3' AND [TableB].[ID]= 'T4' AND [TableB].[ID]= 'T5',[TableA].[Count])
My expression keep gives me the error.
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.
Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script
Code:
If Me.Dirty Then Me.Dirty = False
on "On Close" form event, does not work.
B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function
How to Update ODBC linked tables on ms access?
View 5 Replies View RelatedI have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code I've found doesn't appear to be working.
I've removed server specific details where i felt necessary, but when running the code i have it in place.
Code:
Public Sub RefreshODBCLinks()
Dim connString As String
Dim db As DAO.Database
Dim tb As DAO.TableDef
connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<
[Code] .....
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW
DoCmd.Rename "Appointments_OLD", acTable, "Appointments"
DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
Is this possible using vba in the frontend?
Hello All,
I currently have a bunch of tables that I have linked to a SQL database. For development purposes (easy of use), I'd like to turn all those tables into regular tables rather than linked so if I take the db offsite, all the data is with me. I realize there will be no updates, etc. but that really doesn't matter for my needs in this case.
Is there a script or an easy way to do this? (besides doing a manual import for a LOT of tables)
I've tried searching but I might not be searching for the correct terminology.
Thanks in advance!
Randy
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
I am taking over a database someone else created but is no longer here. The tables are linked to an external oracle database. I know the links exist because when I run a query it prompts me for my user name and password. However, the tables look like regular tables. There is no icon showing it's a linked table so I don't know which tables are actually linked and pulling from the Oracle database.
View 6 Replies View RelatedI have a database with the tables linked from another database. The problem is that i need to copy the database to and from another machine in order to work on it. As this involves putting it in a different location each time, i have to relink the tables.
Is there a way to link the tables reletively so that as long as they are in the same Directory they will be linked?
I have a frontend and a backend to my database. Actually, I have two backends....one with real data and one with sample data.
Currently when I am switching between databases, I am using the Linked Table Manager, which I am finding to be a bit of a pain. If I am already linked to one of the two backends, in order to switch to the other I have to rename the one currently in use so that it will prompt me for the new source.
I think MS Access looks for the current link before asking you for a new one. If the current link is still active it won't ask you for the new one. Renaming the current link effectively makes the link 'inactive' and allows me to link to the other database.
Does anyone know a better way to do this?? Can I change the link in code?? That way I could just make myself a form that I could use to switch between the two different backends.
Any other ways to resolve this would also be appreciated.
Thanks
Brian
Hi,
How can I prevent the user to link tables from SQL Server?
In SQL Server, I give out read and insert permission on specific table in SQL Server, I create one MS Access database which linked tables, I hided the database windows, create the forms. I want the user to input/read the data via the forms.
However, if the user create the new MS Access file, and then use ODBC to link tables himself if he knows server name and database name. He can see and input the data into the table directly.
So, how can I prevent the user to link tables to their files?
Hi there people.
Just wondered if anyone had linked an .mdb backend to a .accdb frontend and wondered if they encountered any problems. Or indeed, if it's do-able.
Oh, the frontend is Access2007 and the backend is Access2003
The data tables are exactly the same it's just that the forms are prettier in 2007 and the records in reports can be edited individually.
Hi folks,
I have a table and I want most of the fields available to others. I also want the other users to be able to add fields to the table (not the master but theirs). Here's an example to explain it better:
MasterTable contains the following fields:
Name,Address,Phone,Training,Checks.
I want the Users to have a linked table with access to:
Name,Address,Phone
I also want the users to be able to add their own fields like:
Date of Birth etc. to have a table that is linked to Master but they can change some fields.
Any suggestion would be much appreciated.
Thanks,
Colin.