Saving A Linked Table Locally
Jun 1, 2005
Hello,
I just got set up with access to a bunch of linked tables with information from our catalog--I work at a library. Most of the library will be using this to run reports, mostly premade reports. I, however, want to take the information from the tables to fill in the gaps in my own access database. I really couldn't care less about their premade reports.
I have been unable, however, to figure out how to save those linked tables locally. Because they're linked (with the green globe icon), I can't delete the records I don't want. I want to delete them because otherwise the file includes all of our books--I only want our journals--a MUCH smaller number. I have tried copying the table and pasting it, but it simply pastes another linked table. I also tried to copy the structure of the table only thinking I could cut and paste the records I wanted into the new table, but that didn't work either--the full data is still there.
I wrote our IT people, and I got a response suggesting that I not delete anything, because deleting records would mean the reports I run would be inaccurate. That is exceedingly frustrating, because I've talked to him several times about my wanting to just take the information to make my own database complete. I don't care about running reports. I don't need to run their reports for my job--and when I do need reports, I make them myself from my local files.
The reason I need to have my own local copy is because I need to match up fields with my local database, and the fields need some minor editing first in order to match up. A simple matter--if only I could get a local copy.
So, does anyone know how to do this?
Thanks!
-Siena
View Replies
ADVERTISEMENT
Oct 6, 2005
I have just split my multi-user DB into Front end and back end. All appears fine apart from there is code in the autoexec module that checks to see if a particular table has been updated today. Basically the first person to login to the DB each day will run this procedure without realising. Now I have FE & BE however, because the query used by the autoexec module is a "Make table" query rather than delete all data and use an append query, every person logging in is running the update each day if it is the first time they have logged in on their PC and they are getting a local table on each of their computers. Is there any code that will create the table in the networked location (when the first person of the day logs in), rather than me having to change all my "delete table" queries to cleardown data and then re-append updated data?
There are quite a lot of delete queries in my DB and I am now worried that I shouldn't have split the DB as I may have caused myself loads of work which I don't have time to do.
I have looked on other threads but cannot find the answer and would greatly appreciate some help
View 11 Replies
View Related
Mar 17, 2005
Hi,
I have built a form without a record source as i need to mess around with the fields before i save.
On save, i would like the form to save to an ODBC linked table in my database.
I save the record through a save button which has the following code attached to its on_Click event.
' NOTE: DAO 3.6 code
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)
With rs
.AddNew
.Fields("Country") = Me.txtCountry
.Fields("ID") = Me.ID
.Fields("Creation Date") = Now()
.Update
End With
rs.Close
Set rs = Nothing
I have no problem saving to a non-linked table with the above coding but always seems to fall over if as soon as i try to open the linked table. Ive debugged and the problem seems to be with opeing the table.
Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)
Thanks in Advance for any suggestions
Access 97 / Windows NT / Full Read Write access. Not a permissions issue
View 4 Replies
View Related
Jul 8, 2014
I do not have full time internet so I cannot sign in to office 365. I like to know for creating web application locally on my computer what software I have to install and how?There is special procedure for installing software's?
View 1 Replies
View Related
Aug 31, 2007
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
View 10 Replies
View Related
Oct 4, 2005
Hi,
I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.
I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.
If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.
I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.
Can anyone point me in the right direction??
Thanks
View 4 Replies
View Related
Nov 15, 2005
Hi. I've been trying to create a link between two different tables which i've put on the Relationships screen. Any type of link made between RepairScheduleID on the one table and RepairScheduleID on the other table disappear when I reopen the database after closing access. This happens if I save, if i don't save it requests me to save then doesn't store the changes anyway.
Ideas? What on earth is wrong?
View 1 Replies
View Related
Oct 26, 2005
Hi,
I've put a button on a form (lets say form 1), that when clicked opens up form 2.
Both forms are connected to ONE table each.
There is a sub table on form 2 that displays records with the same Id as on form 2.
The Problem is when I open The table that feeds the subtable. If I change the A to Z ordering on this table, whenever I close form 2 (not from design view, from user view), it asks me whether I want to save the changes to the design of the table feeding the sub table (the subtable that is on form 2, that is)?
When form 2 is closed there are calculations that are made and data is updated on form 1. I don't know if this is part of the problem? or perhaps I have set up the subtable incorrectly?
Please help!
Thanks
Peter
View 5 Replies
View Related
May 22, 2007
I have a form that stores its data in a table called Participants. Within that table is a field called Payment Due Date. On the form, this field is populated via the following:
Private Sub PaymentDueDate_Exit(Cancel As Integer)
If Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 14)
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 30)
ElseIf Me![PaymentSchedule] = "Paid" Then
Me![PaymentDueDate] = Me![StartDate]
End If
End Sub
My problem is after the field is populated in the form, the data does not store in the table. Have I written something wrong? Any help would be appreciated. Thanks
View 6 Replies
View Related
Aug 15, 2005
Help!!
I have a form created on a query. I created a field to add up the cost of all components on the form and now I need to save it to a field in a table so that I can run a report (my invoice)! Any suggestions??
View 3 Replies
View Related
Mar 18, 2008
Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.
http://i30.photobucket.com/albums/c329/oohmygod831/screengrab-1.jpg
and this is the sql for the query
SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4
FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift
WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year]))
GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);
View 2 Replies
View Related
Sep 14, 2006
I have a small Database that produces invoices, Each invoice could have 5 items on it which are compeleted at different Dates. (cutting hair in an OAP Home) Invoice to be produced at the end of each month which shows what happened each week. This will be generated for each person in the home. I can display all the information in a form when inputing the invoice information. I want a button to copy the invoice number the client name and the total of the invoice to a new table. The total of the invoice is calculated control on the form.
How do I do this??? can anyone help
I need a total in a table to substract payments from.
All Help will be much appreciated.
View 1 Replies
View Related
Nov 19, 2004
Hello,
I'm creating a database to enter Tests that have been done. I am recording the date that the test was performed and now I need Access to tell me when the next test is due (i.e. 5 years from the latest test date). I used the expression dateadd("yyyy",5,[NextTestDue]). But when I put this under Control Source, it only displays it on the form but does not save it in the table. How do I get it to save the info to the table?
I need the NextTestDue field to automatically populate when I enter a test date (The date the test was performed). Please help.
View 3 Replies
View Related
Jan 21, 2015
I am trying to find out what VBA Code I should use for a way to save a main table name: Master_Template on a certain day of the week (weekly) to happen automatically on close of the database - of course on that certain day of the week.
View 5 Replies
View Related
Oct 20, 2013
I am writing a program in Access 2010. My check boxes are not saving to the table. I have stepped through the code with no errors and it only saves the last check box to the table. Here is my code:
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error
Dim rs As Object
Dim db As Object
Set db = CurrentDb
Set rs = db.OpenRecordset("tblUserPermissions")
[Code] .....
I attached the full code.
View 14 Replies
View Related
Jun 19, 2014
I have a field on a form [vin] that is based on a table, except i changed control source to a Dlookup to pull the vin number in from another table so i wont have to type it when i enter the record. However, this field is not saving when i save the record. I cannot just make a relationship because we reuse the same vehicle numbers, when we sell van 1 with vin 123, and replace it with a new van with Vin 456, we still call it van 1, so the unique vin is the only way to distinguish the two.
View 3 Replies
View Related
Oct 4, 2011
I have made a form to enter daily timesheet info for personnel. I have on this form a combo box with different job descriptions, with this info located in a "job description" table. Once all info is entered on this form, I want all info to be entered into my "main time table".
It seemed to be working ok, until I printed out my report based on my main time table. In the area where my job description should be, I don't get the actual wording I had selected from the job description table (using the combo box), only the ID number.steps to have the selected text (from a combo box) saved in the recordset of another table?
View 1 Replies
View Related
Nov 26, 2012
I have created a form and a subform within it, I have managed to populate all the dropdown boxes from the tables I need. but I need to save all the information in putted to a table called bookingstbl and I do not know where to begin?
View 6 Replies
View Related
Jan 27, 2015
I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve.
View 14 Replies
View Related
Jul 1, 2015
I created a table name HEAD with column names (ID, Begin, End, Month, Year and Quarter)
Begin and End are dates. I haven't entered any data in Month, Year and Quarter fields. In the table properties I kept a validation rule of ([Begin]<=[End]). It worked.
I Created a form named FORM. I named the control source for Begin and End to Begin and End from HEAD table. For the Month, Year and Quarter control sources I wrote
=MonthName(Month([End]))
=Year([End])
=Format([End];"q"". Quartal")
The form consists of all the data ID, Begin, End, Month, Year and Quarter values too..
My problem is the data of Month, Year and Quarters are not saving in the HEAD table from the FORM.
The FORM data of Begin and end dates are saving into the HEAD table but not the month, year and quarters.
View 5 Replies
View Related
Nov 12, 2012
Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table
I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula
="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source
What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.
I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.
I have tried the beforeUpdate method within the ItemNo textbox
Private Sub ItemNo_BeforeUpdate(Cancel As Integer)
Me!ItemNo = Me!ItemCalc
End Sub
but nothing happens (in that the table is not updated)
Any way of putting together an incremental item number based on the selected category with a "TV" prefix....
View 6 Replies
View Related
Nov 5, 2014
I have a subform which is for payment, it has a calculated field which is called TotalPayment, with expression:
=Nz([Forms]![Reservations2]![Days]*[Forms]![Reservations2]![Rate],0);
in the control source. When a value is displayed in this field an ID number should be assigned to the PaymentID field, but this does not happen, the field remains null.
View 1 Replies
View Related
Jul 17, 2013
find the attached MS access DB for INVOICE printing . I am facing two issues there .
1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .
2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .
3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .
View 2 Replies
View Related
Oct 16, 2013
I created a Microsoft Access database and access to the system must be controlled by User Access Level Control. The level of the user determines whether the user can add, edit, delete or view a certain form.
I created three tables which are linked via foreign keys: tblUsers, tblUserRoles and tblPermissions. (See the Tables attachment)
I designed the Permissions form to be user friendly by adding checkboxes on the form so that the Administrator can select whether a new user has Add, Edit, Delete or View rights. (See the User Level attachment)
To test my code I added a user as an Administrator. The problem is that when I select the Add, Edit, Delete and View checkboxes, it only saves the last checkbox to the Permissions table. The Administrator must have Add, Edit, Delete or View privileges on the Employees form, but now he only has View privileges. My code does not generate an error. (See the Incorrect attachment)
The Permissions table is suppose to save four entries(See the Correct attachment)
Here is my code.
Private Sub cmdSave_Click()
Dim rstPermissions As Object
Dim dbFSManagement As Object
Set dbFSManagement = CurrentDb
Set rstPermissions = New ADODB.recordSet
[Code] .....
View 14 Replies
View Related
Dec 17, 2011
I Have a Contacts table, when I use the filter on the field that I want filtered I get what I want, no problem, however when I save that filter and close the table and then reopen it it the filter is gone, how can I save the filter that I set up?
View 2 Replies
View Related
Aug 28, 2007
I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J
I can get the first step to completeing the new table of parent/child relationship like this:
PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J
Where "" are null values
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example:
*"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Example of final table:
PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J
Any help is greatly appreciated.
BsK
View 1 Replies
View Related