Reporting On Un-linked Tables
May 4, 2008
Hi Everyone,
I have an Access Database with an Appointments and Expences table which are unlinked and have no common fields. I want to create a profit and loss report using these unlinked tables. The Appointments table has the fields "apptdate" and "apptcharge", The expence table has the fields "expdate" and "expcharge". I would like to create a report that shows a summed total by month and a Appt minus exp to show the profit per month.
I am having trouble as no fields are linked, the dates of the expences are usually different to those of the appointments. I have even tried with Crystal Reports but this only lets me do it in sub-reports and I can't use the combined data for any totals.
How do other financial databases do this simple function? is my table design flawed?
thanks for your help
Peter
View Replies
ADVERTISEMENT
Feb 2, 2012
I 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.
View 5 Replies
View Related
Jul 24, 2014
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,
View 3 Replies
View Related
Jun 19, 2015
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.
View 9 Replies
View Related
Jul 22, 2015
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
View 2 Replies
View Related
Apr 30, 2015
How to Update ODBC linked tables on ms access?
View 5 Replies
View Related
Jul 16, 2013
I 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] .....
View 3 Replies
View Related
Nov 11, 2014
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?
View 1 Replies
View Related
Dec 20, 2007
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
View 3 Replies
View Related
Sep 19, 2013
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..
View 3 Replies
View Related
May 3, 2013
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.
View 7 Replies
View Related
Oct 29, 2013
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 Related
May 6, 2005
I 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?
View 7 Replies
View Related
Jun 6, 2005
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
View 4 Replies
View Related
Feb 14, 2007
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?
View 1 Replies
View Related
Nov 10, 2007
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.
View 1 Replies
View Related
Jan 27, 2005
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.
View 3 Replies
View Related
Feb 16, 2005
I am connecting linked tables in access 97 , my database is splitted into frontend and backend and i am linking tables to backend but i gets an erros application or B]object definition [/B] error. whats the way out?
View 1 Replies
View Related
Dec 7, 2005
I have a database that is really just a bunch of reports based off of one excel sheet. I am using the excel sheet as a linked table.
My problem is that there is going to be a number of copies of this database in use, and i want the user to be able to say where this excel sheet is located, but not every time the database starts up, only when they change location.
the database is not opening up with the database window displaying so i cant teach them how to go to the properties and linked table manager.
what can i do that will detect if the database cant find the table, the linked table manager will open up automatically?
View 8 Replies
View Related
Dec 20, 2005
I have 7 tables to track client data. The 1st table uses AutoNumber to create the 'Client ID' number. The other 6 tables are linked to this table, and in turn each other, through the 'Client ID' field.
All information entered in all 7 tables is done so on one form. This form has a tab control to separate the input fields.
The problem is this. If I enter information in the form, but no field in one or more of the tables gets filled (table gets left completely empty because the fields were not relevant to that record), the record does not save itself to where it can be viewed in the form anymore.
I have programmed a command button to copy the 'Client ID' from the 1st table to the rest, and so far this works, but I would like to have this done automatically without any action required by the user. I tried the 'OnDirty' for the ClientID field on the form, but I do not think that works. Mybe 'OnUpdate'?
Any suggestions?
View 6 Replies
View Related
Feb 2, 2006
Help please.
I have a text file containing approx. 45000 rows of data. I've created a linked table to upload the file into access keeping the seperator's in place. The file has date and time recorded as e.g. 20050123 for 23/01/2005 and 91527 for 09:15:27.
Therefore I'm trying to run a macro on the linked table to delete all records from an existing backup table, append new records to it, convert dates, convert times.
However, every time I change the text file to another set of data, the append query falls over. If I go into design view of the query, delete the fields and recreate the fields the query will then run.
It appears as though because the data being linked has changed, the query doesn't recognise the linked table anymore.
Any ideas?
Cheers
D
View 2 Replies
View Related
Jul 3, 2006
Can anyone help me change a link to attached tables relative rather than absolute?
View 2 Replies
View Related
Nov 22, 2006
Hi,
I use a number of tables as data for Combo boxes (i.e. Equipment List, Site Lists etc.).
Using these directly linked to the BE DB was quite slow so therefore I run queries on startip of the DB to copy the data from the BE to the FE and base the queries on the FE. This all works fine and I have no issues with this.
My questions is mainly in regard to the redundant linked tables. Do these have an effect both memory and performance wise after the initial update?
If so, am I better using a recordset update to populate the FE tables on startup and deleting the linked table and append query?
What is the recommended best way to deal with this kind of issue?
Regards
JC
View 2 Replies
View Related
Nov 24, 2006
Hello,
I've inherited someone elses databases and I'm tryi ng to update some tables to add new fields. When I look at these in design view I get this message:
"Table ... is a linked table with some properties that can't be modified" And it won't let me add these new fields.
How do I get access to change this to allow me to add these fields?
[BTWI'm using Access 2003.]
Thanks for any help
View 2 Replies
View Related
Jan 31, 2007
Ok, here's my issue -
We need access to generate a unique number for RMA's. Then we take that RMA# and put it into our MRP system to finish the RMA process. I am trying to figure out a way to get the RMA# and some other data into our MRP without having to go to both applications.
Here is what I have so far - I have a linked table (Customer_Order) which is from our MRP application. I have a form that generates an RMA# and type. The RMA# needs to go into the OrderID field in the Customer_Order table. I tried using an append query to get the data from the RMA table into customer_order but I get key violations. I ran a query to see if duplicate data existed but none does. Any ideas how to set something like this up?
Thanks
View 1 Replies
View Related
Mar 23, 2007
Be gentle...I'ma newbie.
I am linking an Excel spreadsheet to my db and I want to cut down the time it takes to run queries. I thought that Make Table Query would do that and on the first time it works but then when I re run it, the query tells me it is going to delete the made Table(which is fine but) then it gives me a Numeric Field Overflow error. I have formatted the spreadsheet to Text for the first ten columns. Really what I am trying to do is make a hardcoded table from the linked table...or refresh the table. Please help.:confused:
View 5 Replies
View Related
