Tables :: Relinking Upsized Tables
Mar 3, 2014
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.
View Replies
ADVERTISEMENT
Aug 28, 2006
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.
Deesy.
View 1 Replies
View Related
Mar 24, 2006
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.
Thanks in advance.
View 5 Replies
View Related
Apr 13, 2006
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.
Thanks for insight.
View 1 Replies
View Related
Jun 5, 2007
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.
Barry
View 1 Replies
View Related
Aug 8, 2012
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.
View 2 Replies
View Related
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.
View 1 Replies
View Related
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
Sep 26, 2012
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
View 2 Replies
View Related
May 30, 2014
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
View 8 Replies
View Related
Dec 3, 2012
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
View 1 Replies
View Related
Feb 10, 2015
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
View 1 Replies
View Related
Jan 14, 2013
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
View 1 Replies
View Related
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Jul 19, 2014
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type
12345, 67890, 1
12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
View 2 Replies
View Related
May 13, 2015
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
View 5 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 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
Sep 26, 2012
I want to fill local tables in some application with disconnected recordset. The tables in the front end application having the same table structure as in the back end database. The front end application was linked with the back end password protected database tables. I want no connected linked tables in the front end application. How can I fill the local tables in the front end application with the back-end password protected tables?
View 8 Replies
View Related
May 5, 2013
I have 3 tables.
Table 1: contains staff names and contact numbers
Table 2: contains training above staff have been on or need to go on
Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
View 10 Replies
View Related
May 28, 2013
I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
View 1 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Mar 4, 2013
I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design
For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS
Is my design reasonably sensible?
View 6 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
