Hi,
Relevant to this query are two tables, tblNames and tblLinks.
I've been trying, with a very limited understanding, to generate a list of every tblName.Name where tblName.NameID IS NOT in tblLinks.NameID.
Is this something I can do in the query design view or do I need to work with raw SQL?
Any tips appreciated:)
Hi,
I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?
Thanks,
Richard
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
Please help, thanks.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
I am having difficulty deleting records in a linked DBF (standalone) table. The table links fine. I can run a delete query and the records appear to be deleted when I view the table from within Access. However, when I view the table outside of Access, the records that I thought were deleted are still there. The only way I can actually delete the records, is to import the table, delete the records and then export the table as a new DBF.
Can someone tell me why deleting from the linked table isn't working?
Thanks,
Sup
I have done everything I can think of to remedy this, but I can't figure out why this is happening. I have a linked table from excel that contains 5 fields for each record. I have a table in access with matching records and 20-30 fields. The linked spreadsheet is used when adding records. I have a query that queries both tables to get all data from both and a form based on that query where others can pertinent data for the records resulting from the query. My problem is that when I open the form the new records that were added in the linked file are there but all the fields from the access table cannot be updated. I have looked every place I know to look for record locks, read only options, everything I can think of why i cannot update these records and I am coming up empty. I checked my join properties and selected the only one that actually displays the linked records when the query is run (not sure the name of the join but it's #2 of 3 join properties options (in Access 2002). maybe I am just overlooking something simple? Do you have any ideas what I can do here?
Thanks!
Greetings all.
I'm posting this question here as it pertains to my form but it could go in tables section as well.
I developed a prototype app in Access and have just finished upscaling it
to Sybase 12.5. When I open up the associated form to add records, I am unable to do so. The built in button that allows you to do so is greyed out.
When I go into the table directly, again, no ability to add recs.
I've never come across this so if anyone has any experience with this, any help would be most welcome.
TIA,
Tim
My aim is to chart the career history of many people in my DB and my intention is to achieve this with two "Main" tables with many smaller tables linked to it.
Table 1 will contain the generic data about the individual, employee number, name, DOB, etc etc (there are many more fields) and Table 2 would hold their career history. Where they have worked, when, etc.
When viewing Table 1, I see all data, including a sub table showing career history, which is great. What I would like to do however, is have a query that will return all data from table 1 and Table 2 that refer to the employee number.
Attempts that I have made thus far, mean I have to enter the employee number twice (I would prefer to enter this only once) and then it gives me many pages (in report view) each page has all data from table 1, and one entry from table 2.
I would like to see all data from table 1 and then all data from table two, listed.
I have an Access database with linked tables from Sybase SQL Anywhere, via an ODBC data source. I have just linked the tables recently, all the data used to be in native Access tables. It is fairly common for members of our team to open these tables in datasheet view, and copy/paste several records to create new records, then edit a few fields on the new records. This used to work fine with native Access tables, but when we try it with the linked tables we either get an ODBC error - "primary key value already exists" or the new records show up with #Deleted.
The root of the problem is this: In the old Access tables, the primary key was an autonumber field, and Access was smart enough to assign new ID's when you copy/pasted records. In the linked table, the primary key is type "Number" in Access, and Access is not smart enough to let Sybase assign new ID's when you copy/paste records in datasheet view. Access is trying to force the existing ID's into the primary key field, and Sybase says "too bad so sad".
Of course, I could just write some quick append queries to copy/paste the data. And in the short term, that's exactly what I will have to do. But is there any way, long term, to allow members of my team to do it the "quick & dirty" way by copy/pasting in datasheet view? I have tried to change the primary key to an autonumber field in design view, but Access doesn't allow that. Is there a way to do it in code, or a way to force Access to allow Sybase to always handle the primary key field?
Is it possible to add new records to a linked table ? i tried it out but new records is not possible, is there a workaround for this.
View 8 Replies View RelatedI have a Membership List Table with a calculated field entitled Member Name.
The second table entitled Groups contains only 46 records each of which has up to 30 Fields also containing member names.
I Have written a query to establish which of the members from the single field of the first table do NOT featured in any of the fields and records of the second table. In the Query Design View I have listed 'Member name' from Table 1 followed by all the relevant member fields from Table 2, carrying the criteria 'Is Null'.
When I run the query, it merely lists all the members from Table 1.
Is there a way to gain access to the Field Properties using VBA?
I'm trying to loop thru the Tables in a database and find out the Fields that are indexed in each table.
I can loop through the table Defs to get each table name and a list of Fields but I can't seems to get to the Field Properties, namely "INDEXED".
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
I have an access db with a linked table (to Sharepoint) which contains about 15000 records. I have to copy those records to the local access db. To do this I have a bit of VBAcode. It works, but for each next record that is copied it takes a bit longer. When starting the program the records are copied quite fast, but soon you see it slowing down until after a couple of thousand records it just goes too slow to be usefull, meaning it would take a lot of days to copy all records.
What I now do is let it copy 500 records and exit ms-access completely and then start it again and let it copy the next 500 records. This works but is tedious as I have to do it about 35 times. Why this gradual slowdown occurs?
The VBA code is pretty straightforward.(I use VBA because I do a bit of data wrangling while reading in the values of the records)
Code:
Dim importdb1 As DAO.Database
Dim rsimportdb1 As DAO.Recordset
Dim i As Double
Set importdb1 = CurrentDb
Set rsimportdb1 = importdb1.OpenRecordset("2015")
Set rsMain = CurrentDb.OpenRecordset("tbl_import")
[code]....
Hi,
I was wondering if anyone would be kind enough to help me out on this problem I have.
I have a table:
---------------------------------
Source | LinkedTo
---------------------------------
A1 B2
B2 C1
B2 C2
C1 D1
C2 D2
C2 D3
B2 D4
A1 D5
Another table:
-------------------------------
Item | Load
---------------------------------
D1 2
D2 4
D3 6
D4 8
D5 10
So looking at the tables,
C1 = 2
C2 = 4+6 = 10
B2 = C1 + C2 + D4 = 20
A1 = 20 + 10 = 30
Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??
If anyone could help, that would be great! Thank you in advance.
Bear
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
Hello All,
I am have some difficulies in the following query:
The result i need is Summary5 ResultG/001, G/0015 (Room) and soo on
Field Names Are:
Room & Summary5
I realy am not sure as how to do this, if it help the reason why i need to do this is for a report that will show "Asbestos Found In Rooms ...."(Summary5:Asbestos Found In Rooms" "&[Room]) is what i tried but this does not show it right, any ideas.
Alastair
**************************** Resolved With Thanks to raskew (Bob) & Matt Greatorex*****************
Hello
I have a table with a list of lectures and the dates and times they are running. I then have a related table which lists the feedback marks and comments for each lecture. I want to enter them via a form and have set up a form with the the lectures and then a subform for entering the feedback. This all works fine. However I would like to be able to have the lectures listed by date order and wondered what the best way is to make this work so that the user can simply scroll through each lecture on the form and it automatically goes through the list by date.
I hope this is clear and thanks for any help.
Kay
Hi,
Having recently correlated the impossible pile of photos that I have on CD, including a lot of re-naming etc... I have now managed to get them down onto DVD.
I there an easy way to import a directory listing into an access database other than inputing the lot by hand? I don't wan't to add thumbnails just the file name and the directory it's in. I can embelish other details later via ASP, just need the bulk of the work done easily.
I have some experience of database design but not huge amounts of programming, other than the ASP side of things so if anyone has a complex answer please go gently with me!!!
Al
Hello,
I currently have a DB that has a field in a table that records which shift (A, B, or C) that some data was collected on. I am trying to write a Query in order to filter the results so that either the data from shifts A,B, or C show up, or if "All Shifts" is selected, then all the data shows up. I have another table set up that records the shift selection from a form. I was trying to use an Iif statement but it does not seem to be working for the "All Shifts" option. Selecting "Shift A" or "Shift B" etc. works fine.
IIf([ShiftSelect]="All Shifts","Shift [ABC]",[ShiftSelect])
**[ShiftSelect] containts the users selection for which shift they want to view.
I have also tried this with a wildcard statement (Like "*") instead of "Shift [ABC]", to see if I could get any data to show but I still get nothing when "All Shifts" is selected!
Let me know if you can see the problem, or know another way to go about this query!
I have a Form named frmClearances that lists the name, age, Club Clearing From, Club Clearing To and the Date of Clearance... this is used for my local sporting Club.
I have created reports that separate names into "Clubs Clearing From" so that each Club is aware of who has gone. The Reports are named...
clrSportingClubName
I have searched this Forum and all over as well, including Tutorials and I cannot understand how I can make a list box that contains the names of the reports so that they can be selected individually when required for each Club.
I copied the SQL for each query and placed it in the RecordSource of each Club Report so that I did not have numerous queries.
I have even copied code from other list boxes to then changed a couple of things but that has been to no avail.
Could somebody please explain to me how I do this? Thank-you very much in advance for any assistance.
I have a report that i am trying to list in the header section, a listing of all the names for a particular field, if it is in my report. I will use these names to send the report to.
View 1 Replies View RelatedSo I am using 2003. On the report I have created I have this one entry that exists for all my records, it is a Name, and at the bottom of the report I am trying to create a summary that will list each of those names. I already have a count going, so I thought maybe I could tap that, but not every record has a name listed. I am farely inept at coding.
What I want is this:
(Institution 1) Accrediting Institution: ACICS
(Institution 2) Accrediting Institution: ACCST
(The part above is Done, what I need in Below)
(SUMMARY) Accrediting Institutions: ACICS, ACCST
Thanks
So I have a textbox on a form that contain values. I want to convert it to a ComboBox. Since a lot of these values are the same in a lot of records (for example, "Inventor"), I want the entered values to appear in the ComboBox dropdown so they can just be selected instead of typed in. I converted the textbox to a Combobox by right-clicking on the textbox => Change To => ComboBox. It changed successfully, but I want it to list the values in the dropdown, but it doesn't.
View 1 Replies View RelatedI've posted in general because I really don't know what approach is best for this requirement. I can't even come up with a meaningful, yet succinct title.
Here's the problem. I have tables:
tblApplication - defines an application (name)
tblServer - defines a server
tblInstance - defines a partition on a server (defined in above table).
tblApplicationInstance - defines a specific instance of an application on an instance on a server (i.e. ties the above three tables together)
The tblApplicationInstance table has an autogenerated ID field as primary key, and foreign keys to tblApplication and tblInstance (and thus through this to tblServer).
All well and good. Now the next table
tblApplicationUse - defines that a specific project (a foreign key to another table but I don't think it is an important factor here) is using a specific application instance over a date range.
The question. How to allow entry of new tblApplicationUse records without having to find and enter the ID from tblApplicationInstance. Rather, I want the user to specify the Application, Server and Instance, but be limited to only those that are defined.
I tried a simple query, thinking it may allow me entry, but not so. I've been building some simple forms for query parameter prompting lately but am fairly inexperienced with these. The crudest form of prompting I can think of is to simply apply a drop-down to the ID field (in tblApplicationUse) and use a multi-column format here. But it ends up very wide and is less than ideal.
Can anybody give me ideas on the 'proper' way to do this?
Hi all,
I've got one question again.
Let's say. I've created several tables to store data for several categories.Note: there is no relationship between each table.In each table, there is the date field in which the record is created.
Then I want to make another daily record form that is to find records from all categories which is created at the current date.
Is this possible to search records from various tables and combine them to show in one form or report?
Can anyone help me in this case?
thanks in advance.