General :: Linked Pivot Tables Prevent Updates To Database
Jul 10, 2012
I have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
I have a table called tblDeliveryOverview and a table called tblPurchaseOverview in the relationship there is no link between each other but i've used a query to link the one field from tblDeliveryOverview to tblPurchaseOverview.
How can i prevent a record update from happening when i update a record in tbleDeliveryOverview and the same gets updated in tblPurchaseOverview, which i don't want to happen.
there are many threads about Table Linking but i coud not find one that answers my specific questions.
1. Can i either prevent database from linking to my tables? (or give permissins ) 2. If not, can i make the table read only when linking? 3. Is there a way to confirm what databases are linked to my tables?
I want to create a database that keeps track of contributions of members of an association. Every member can voluntarily give a standing order to contribute a fixed amount every month. A member can as well decide to review his/her contribute.
how I can come up with a database that can automatically add the contribution of a member monthly without manual input since the stated contribution is known. So that I can run a report for individual member and the report can capture the every monthly contribution that has been added automatically and shows when ever a member changes his/her contribution amount.
The front end has been saved in a accde format and distributed to 20+ users. They have all saved this onto there computers and this is used as an application.The users complete a form on a weekly basis on the application and then save. This then saves into the back end.For some reason I have noticed that a few users submission randomly do not save. For the life of me I cannot understand why.All users have saved them into the back end in the past but every now and then submission for users do not save.
If you are to sell a db. What have people done to stop one person copying it to their mates.
I have a way in which they send me their name,lastname and email. I then have a separate db that genertates a code based on a maths calculation. The users db does same calc. Then a comparison is done. if they match the code they entered goes green. if codes not right it goes red.
I also have macro that swap tables round when run. so by default its limted to 10 records. When swapped its unlimted.
I just cant link the 2 functions. Ideally some way it will see the green code and then run the macro to swap tables to a full db.
Then the customer has a full db and is also tied to their name etc...
Hi there, I've developed over the past 3-4 years a database holding data relating to workload figures for my place of work. It has grown to a large size (eg. one table holds 1-2 million records). I've been trying to run a particular pivot table based on the sql query below:
SELECT Tests.Test, Year([DAUTH])+(Month([DAUTH])<4) AS FYear FROM Sets INNER JOIN Tests ON Sets.SET = Tests.[Set Code] WHERE (((Sets.DAUTH) Between #4/1/2003# And #3/31/2007#));
When I try producing a pivot table using the above query it takes forever - has it running for over 8 hours last night - then access closed down with a runtime error. Does MS access have a problem with large databases? Is there any way I can improve the efficiency / speed of access with a download add-on? Or, will I have to resort to using something like MySQL on a LINUX OS which has fewer demands on system resources when using large databases.
Any help would be much appreciated. Thanks for reading.
Is there away in which I can protect an access 2007 database modification with a password.I'm doing this because I would like to prevent my client from modifying or having acces to the database's back end system (db), I would just like him to use forms and nothing else.
When I start my database normally, I'm not able to add fields, change chart type, etc, by right clicking in a pivot chart or table. It works when I open the database while holding down the shift key though, so I suppose there is an option in my database somewhere that I have changed, but which one.
I have a split database. I have secured the mde sufficiently but I need to secure the back end so that no one could link to tables in it. If I introduce a password to the back end is this going to require those using front end to supply the password? If not then this could be good solution.
As the database is on the network drive, is there any other way to prevent people to link to the db? Can I hide the db in the network drive? Or should I hide tables in the BE. Should these still be available to be linked?
Twice a year, a database of mine is accessed and put too use by various staff within a time range of 1 week. the database is on a shared drive and in a location which can be accessed by all.
The staff access the database from different workstations and in some instances at the same time.
This has only led to issues in the database being copied and then confusing staff on what database to click on thus i have 2 databases which i then have to sift through and copy/paste into the correct one.
I want to know the best way i can:
1) Prevent multiple users accessing the database at a time. 2) making a copy of the original and typing into a separate database.
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
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'm new to Access2007. I created 5 tables all related to each other in series with one-to-many joins (type 2 and all enforcements on).So an Id in table 1 can show up several times in table 2, and the same is true from tables 2 to 3, 3 to 4 and 4 to 5.I want to enter new records in such way that I can see all tables at once. I tried to do that with a query, but I can only modify records, not add new ones.I have no problem adding records to individual tables but have not figured out how to do it to this All-tables query.
Due to software licensing restrictions, my (Access 2007) development pc is NOT (may not be) connected to the network.The user pc's are all connected to the network.The network pc's only gets Access 2007 Runtime.The Front-end accde will be located at C:mydbFE heFE.accde on each user's pc.The Back-end will be located at X:mydbBE heBE.accdb (network share).
Currently (during development) the Front-end and Back-end is at C:mydbDEVFE and C:mydbDEVBE respectively - on my development pc, and the linked tables are pointing to C:mydebDEVBE.Now that I'm ready to distribute the database to the user pc's, I obviously need the Front-end's Linked Tables to point to X:mydbBE heBE.accdb
Where does Access2007 store the path to the Linked-Tables ? Is there a way I can change this path manually before making the accde ?I noticed that the path to the linked tables appear in the (hidden) MSysObjects table, but I do not want to mess with it until I know what the solution is.
I have a fe that has 5 tables linked to a backend db on the server.I want to add a new table to the fe that is linked to a second db on the server? What I have tried.I have created the tbl_called in the fe.when I right click on it and go to Linked Table Manager I do not see it on the list. The only tables in the list are the 5 I linked before. Why is tbl_called not showing up??
Our master database has linked tables with ODBC to our SQL database and linked tables to another access database. On the computer i'm running this from a System DSN data source has been created to connect to our SQL database.
The problem I'm having is when I run Macros to kick off Queries, there seems to be some kind of timeout issue and its not consistant. Sometimes in Access after a hour or two, the ODBC links somehow just fails. I have to go up to the menu and go to 'Get External Data' and then 'Link Tables' and relink the ODBC DSN connection. I make sure to hit save password and the end but that doesn't seem to do much. This also happens sometimes when I close and reopen the database.
But it seems erratic, sometimes it can stay up for hours but sometimes just a few mins. Sometimes I can close the Access database and reopen without losing connection. Being so erratic, this makes it very hard for me to run batch files nightly to kick off Macros. Is there some kind of inactivty timer for DSNs? Or maybe with the SQL database itself? Or is the way i'm restablishing the ODBC connection wrong ?
I'm about to move to a back end / front end system, from a single database file. Before I do so, I want to make sure I have all bases covered so to speak.
My back end file will reside on a server. I am aware that path names are sometimes changed by network administrators without warning.
From tests, I notice that, if the path to the back end can't be found, one cannot open the front end database file at all (Access 2003).
My question is: if my back end file gets moved, or the path changed, how can I get into my front end file to update the linked tables.
I recall reading somewhere (fairly recently) about a parameter or property in Access which is "On" by default and allows a form/query to identify when backend table field names or query arguments are changed and automatically looks for and "fixes" the renamed link. By leaving this switched ON, the database is slower than it could be. The db speed performance can be can be improved significantly by switching this 'feature' off. (i.e. Name of the property)
I'd like to secure my frontend by disabling access to the linked tables, queries etc. I thought converting to .accde would do this but apparently not. Is there a way that this can be achieved?
I am aware that I can disbale the ribbons etc but they can all be turned back on using the options menu.
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
Back at you guys with hopefully one more question (btw the command button code worked perfectly thanks)
Another thing my database has to be setup for is updates, Roughly 30 people will be using this database and to have them re-copy and paste the new database releases is out of the questions.
Does anyone have experience setting up code that automatically checks the version of the database created and if it differs downloads/installs the new version and the restarts the database so the associate will then be working in the current version?
I am looking to be pointed in the right direction to get this figured out, thanks.
