Tables :: Relinking Record Associations In Company Database
Jan 3, 2014
I'm building a database for my company who refurbish computers. I'm wanting to build in functionality for if a hard drive is damaged and it has to be replaced then for us to be able to trace (based on asset and tracking numbers) what hard drive has been removed from a PC and if it has been put into stock or destroyed and if a stock drive has been put into a PC.
Any item that is brought in whether it be a PC, laptop, server or hard drive etc is given it's own unique tracking number. Same is true for any stock we buy in for refurb purposes.
I'm thinking that this will be done by having a field for the original asset number and a field for the 'current' asset I.E. the one it's been put into.
I have a psychology project where we soon use a questionnaire on a daily basis (maybe 80 variables per day per subject). Currently, there is no data in the database.
I've managed to create a table called day1 (with 80 day1 variables) and a form that looks like the questionnaire we want to use (and linked apropriately to the variables in the table for day1).
What is an easy way of duplicating the table and form for successive days? That is, i want the structure of the day1 table copied to another table (call it day2). It has exactly the same variables except they are slightly different to differentiate them from day1. I would like the form for day2 to have exactly the same layout as the form for day1, but link to the variables in table 2.
I know i could just couple the day1 table, change the variable names slightly, and then copy the form for day1 and change the links to point to table2, but there are many assessments (18 days).
I have another problem. I am having troubles relinking a front end (secured) database to a back end database and I am not familiar with security so I don't know if it is done through security. The problem is when I copy the backend to backup the front end then using the new address as a link to the frontend.
I didn't design the frontend so I am unable to enter to change the relink if it is secured.
Is there any programs or code (and where do I put it) to change the links. There should be as this is a simple problem.
Does anyone have any db's that show how to relink tables automatically on start up and also remove the links on shutdown, i've been searching everywhere on the net and can't seem to find anything decent, any help would be appreciated.
Credit goes to FoFa for posting this code on this form.
Attached you will find code to relink tables. I manage I am doing to wrong, and need help in order for it to execute correctly. I would appreciate direction.
What I am doing: Copying the modules over to my unsplit database. Creating a form called ReLink. Setting a macro (AutoExec) to open the the form. On the form's On Open event placing the function =TestIt Splitting the database. Verifying by opening FE that connection exists Moving BE and reopen FE. All I get is unable to connect the BE message. Form continues to open.
I have developed a distributed database application in Access 2003 that links to local SQL Server databases in each district office. Recently I updated to Access 2007 on Vista. When I go to relink the application to a district office database I keep getting a ODBC Call Failed error. To relink I delete the old ODBC DSN and created a new one for the particular district office. Current work around involves restarting the whole computer and it only works once. Luckily I still have my XP/Office 2003 computer behind me for now but keen to find out if anyone wlse has experienced similar problems.
I have also uninstalled/reinstalled Office 2007 just in case but no luck.
I am having trouble relinking tables in my Front End database to my Back End database. My BE DB is on a network share and when I try using the link table manager to navigate to the correct folder I get an error message upon entering the network share folder that says:
The Microsoft Access database engine cannot open or write to the file "etworkfolder" It is already opened exclusively by another user, or you need permission to view and write its data.
I have permission to the folder and have ensured that this folder is a trusted location path.
We have a frontend/backend setup and one of the tables has almost 2 million records. I've used the SSMA tool for Access to SQLServer and upsized that table. Seems to have worked well.
Now I'm wondering if I should change the link directly on the frontend or link to the SQLServer from the backend and then update the links on the frontend?I'm mostly concerned about what happens to relationships if I rename the old table and then link to the new SQLServer table.
Result I get which I don't want What I want is the last table Order2.OrderQty Order2.Company Order# Order1.OrderQty Order1.Company 3 abc AAA 3 abc 3 abc AAA 12 abc 7 Oop BBS 8 Oop 12 abc AAA 3 abc 12 abc AAA 12 abc
I want to consider ways of sharing an Access database within our company. This could be web-based via our intranet or done some other way.
I understand that Microsoft's preferred way of sharing via an Intranet is to use SharePoint. However, as we don't use SharePoint (we use Atlassian Confluence) the cost of that route would be prohibitive.
Any methods of sharing an Access database across, say, 20 people? Simultaneous read/write access would be required but in reality the usage will be low, so performance is not really an issue. I would like to consider web-based or other methods.
I have chosen Access 2013 simply because we use Office Professional 2013 and Access will be familiar to some developers. We could use a different product. But I would certainly prefer a tool that supports RAD design of forms.
Just a question, I am developing a database for the company I work for and being new to Access, I have successfully made a login menu when the database starts up. The employee selects there name and begins data entry. Is there any way to log the activity of each employee, which records they inputted, date and time and so on?
I am trying to count the number of times a client has engaged with our company. I have a company table, a reservation table, a rapid prototyping table and a project table. There is a one to many from the company table to each of the other 3 tables as can be seen in the attached picture.
Is there an easy way, in a single query, to list unique company names that exist in 1 or all of the 3 tables?
I am trying to make a database that will keep track of invoice backlogs for different locations within a company.
Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:
Code: Location Backlog 00001 84 00002 53 00003 26
Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file.
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.
I have an equipment database, within which I am attempting to create associations between various items. The items are distinguished as level 1, 2 or 3 depending upon where they sit in the equipment hierarchy, level 2 being sub-equipments of level 1, and level 3 being sub-levels of 2.
So for example the level 1 item could be PC, level 2 items would be keyboard, mouse, monitor, base unit etc.
What is the best way to structure this relationship? I had considered generating a sub form, and have set up queries to generate lists of potential items with each level description - so that users can select the items they want to add to the associated list of equipment, but I'm not sure how/where to generate that associated equipment list.
I have a form that is used for recording test scores. The first selection that is made is a type of test. After the test is selected, some tests have a second sub-type of test that needs to be recorded. My quetion lies in how to hide or grey out this second combo box until a test is selected that requires it.
I don't want to "hard-code" it in the sense that I create an If Else statement that requires looking at values from the 1st test type combo box. I want to make sure that the DB is scalable and when additional tests are added, additional sub tests can be added if necessary.
If this makes sense and anyone has any ideas, I would appreciate it.
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL FROM [Mail List] WHERE ((([Mail List].[GENDER])="F")) GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now i want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record source dropdown.
We have been using an Access (Access 2013) program where we link the program to dataviews created in sql server.
Recently we had to re-link the data views because a change had been made to them. When we relinked the data we noticed that the field type for the field used as the primary key changed from "Short Text" to "Long Text". The programmer who created the dataview from sql server said that nothing was done at that end that would have caused the change
I know how to prevent the problem from recurring. We are changing the sql server spec from VARCHAR(8000) to CHAR(20).
My company rents equipment to our customers. I am looking for a db to track our stuff. There are three possible locations of everything. Warehouse, employee trucks, and Customers. I assume that warehouse can be added to the employee table. I can set up these 2 tables and one for our equipment list, but the hardest thing I am having with is with the table for transactions(I believe it should be a table), this needs to allow batch transations, because typically more than one piece of equipment is moved at a time.
I will need a report that will show each equipment id number in a customers home from date to date (date it was placed and date it was removed), one that will show a list of all equipment locations on a given date, and one more that will show equipment that has been in a customers house for 5 days or more so I can verify that it is still there, becuase equipment typically should never be with a customer for more than 5 days.
Right now I am using excel, but its not working well. I would greatly appreciate some help! The attachment only has the info I need for the 3 tables I mentioned above that I know how to do, and my best guess at what the transaction table should look like.
I have a database that controls donations for a Charity. There is to be a sister charity added to the database and a lot of the previously created objects can be used to provide for the new Charity. My idea was to create a "company" ID and label the categories of the donations. I could then use this company ID to identify to whom the data captured belongs.
I have used the switchboard manager to enable navigation, in this I have a ONload event procedure that requests the Company ID (1 or 2). That's as far as I have got.
How would I get Switchboard to open each form (some being based on queries - a couple direct on the tables) and filter the data on the company ID?
If there is a better way, please enlighten me!! :)
Hello All, I've searched and searched and can't find anything but it might be because I'm not sure what to search for.
Here is what I'm trying to do. (I'm simplifying this to make it easier to discuss) I have a table that looks like this: Company Date Sales 1 5/5/07 $1,200.00 1 6/5/07 $4,200.00 1 7/5/07 $2,200.00 1 8/5/07 $4,300.00 2 5/5/07 $1,200.00 2 6/5/07 $4,200.00 2 7/5/07 $2,200.00 2 8/5/07 $9,200.00 2 9/5/07 $1,200.00
I need to run a query that grabs the last 3 months for each company.
So the correct output from the above table would be: Company Date Sales 1 6/5/07 $4,200.00 1 7/5/07 $2,200.00 1 8/5/07 $4,300.00 2 7/5/07 $2,200.00 2 8/5/07 $9,200.00 2 9/5/07 $1,200.00
Any help would be appreciated. I'm sure this has been asked in the past so even if you could help me with a keyword to search for, I would be greatful.
