Hi i hope someome can help. I am importing a data into a table, records are being rejected as they should due to validation rules. However access is not creating a table of rejected records. What am i doing wrong.
Thanks for any help
I think this is a pretty simple thing, but for some reason I'm lost.
I need to add a number of records to a table.
The user would input a starting record number and ending record number (ie 60000, 60003) and access would create those records with the record number field populated with all the values, in other words, it would create 4 records 60000,60001,60002, and 60003
any ideas how this can be done? I'm thinking a query, but it might require coding of a for...next loop???
Thanks in advance for the help
Hi,
Firstly, I apologise if this has been posted before. I have done a couple of searches of the forums, but can't find what I'm after... and being new to access, I'm finding it hard to know what exactly to look for. How to even explain what I want is difficult!
I am designing a database for the clients we deal with a work. It started out as being a database of 'post' and correspondence, so the Idea was, we'd have a databse of all the clients... and by selecting their names, it would bring up a list of all the letters that have come in for them. I have managed this part fine - probably basic, but fine.
What I would like to do, is expand it to more than just 'letters'. Each client has done a particular 'work' with us, and each client has particular details that are unique. For example, a client will have made certain transactions through a bank account.
I hope to be able to click on a client, and have a screen or page pop up with details and comments about that particular client. I have tried hyperlinking but that doesn't really produce what I'm after. For example, I tried creating a "report" with a page for each client, so that I could have comments for each client - hyperlinked to the table. But all it does is link to the "whole report" and not a particular page for a particular client.
I'm sure this is sounding really confusing:
I have a client named "Peter Litman". His name and details are within a "client" table (listing all clients). I hope to be able to click on his name (or row) and bring information (comments, details etc.) about him. I thought this could be some sort of "pop up", or link to a special page. ??
The same would then apply with all other clients.
Any ideas? questions?
thanx.
Table name: Items
Field: ItemName
Form name: Frmpayments
when I open the above form, it should create texts boxes on "frmpayments" based on how many data available in "Items" table under "Itemname" field. It'll be even better, if it can put those item's name in each text box it creates.
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
Hey guys,
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
Any help?
Thanks a lot in advance!
Daniel
Hi all,
I write a program which is querying some data from a MS-Access database.
The problem here is, that the tablenames contain characters like "/" or "-" and I
do not want to change these names, because I think that the database
structure will be damaged.
To solve this problem I thought of creating a view to the table. I tried creating
this with a SQL statement, but that did not work.
Is there any other possibilty to create a view on a MS-Access table?
Regards,
Stefan
Hi,
I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.
Before anyone suggests it, yes I have posted this on SQL server forums.
declare @TheName char (8)
,@Cmd char (500)
select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)
select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd
I'm experimenting in MS Access VBA reading from one MS Access application to another as visible in the code below. My problem is that I'm reading a recordset from an Access file and want to take that recordset (rec) to create a table in the current databank. If I run an cmd.commandtext "make table query" it runs the query across the connection and places the table in the remote database (as expected). Can I take the recordset and create a local table using ADODB command or is there a better approach. (I should note that the remote MS Access file does not have an .mdb suffix) Thanks in advance.
Sub get_indbyind()
Dim strInputfile As String
Dim dlgOpen As FileDialog
Dim bob As String
bob = Application.CurrentDb.Name
' select connection
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = False
.Show
End With
strInputfile = dlgOpen.SelectedItems.Item(1)
' make connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim strcnn As String
strcnn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & strInputfile
cnn.Open strcnn
' MsgBox "connection made"
' create recordset
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
rec.Open "SELECT * FROM SATransfers;", cnn
Dim n As Long
Dim i As Long
Do While Not rec.EOF
Debug.Print rec.Fields(0).Value; rec.Fields(1).Value; rec.Fields(2).Value; rec.Fields(3).Value
rec.MoveNext
Loop
' create table from connection cnn
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT SATransfers * INTO tmp1 FROM SATransfers;"
Set rs = cmd.Execute
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
End Sub
I have currently been given a task to re-work some current coding within Access. This is well above my understanding of Access and was hoping to get a little assistance with some simple coding language.
The database I am manipulating has data collected from tourists about their destinations and origins.
I wish to create 2 tables based on a current table. One table will be short trips (trip length <= 150km) and the other long trips (trip length < 150kms)
I wish for these tables to be created from a table of raw data upon the click of a button (GUI interface).
If you are puzzled by my question. I'll try to make it simplier by asking:
1. what line of code will create a new table overriding old
2. what type of code is required to create this table with specific column headings?
3. How do I import information from current available tables with the condition: 'Row Y' is > X
I am trying to create an access table linked to an excel file.
The excel file has 256,000 kb, 15 columns, 6,400 rows.
When I go to CREATE, DESIGN VIEW in access and try to create the new linked access table i get this message -
"MICROSOFT ACCESS HAS ENCOUNTERED A PROBLEM AND NEEDS TO CLOSE". I click on Ok and then it says "REPAIR MY OPEN DATABASE AND RESTART MS ACCESS".
I click on OK and then save the back up file. The file disappears. I start over again and the same process starts again and again...
I have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
View 2 Replies View RelatedI 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! :)
Hi all,
I have a text source file and inpul layout i.e. field names start and end positions in excel file. I want create a table in access from the text data using excel file layout.
Can you please help me out in this. I am a mainframe programer and recieved an request to work in access.
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
View 1 Replies View RelatedIs it possible to access specific records from a Microsoft Access table without locking the whole table?. I have different processess accessing at the same time different records from the same table and I getting an execption, indicating the table is locked. Can any one provide me some help regarding how to access specifi records without locking the whole table in MS Access?
View 1 Replies View RelatedWe have a database which has a table with 2 fields - here is an example of the data:
Order File Path
240971 cclapps1scannerWorks Orders 2008Works Orders 2008ITL1ITL1TMSPC11.GFD
We basically need to change the file path to read cclapps3 instead of cclapps1 in every record.
access 2013. I am working on a project which I should design a form as program.
in that form I want to make a field with expression builder to retrieve some data which satisfy a condition (that condition is based on some input form who work with the program) I want to access records one by one and check if they satisfy the condition but i do not known how can I do this?
Hi
I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.
For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?
I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks
I have a database in which i keep track of my books collection. Amongst my tables i three of them are:
tblBooks - main books table
tblKeywords - where i store keywords (such as "database", "programming", "web development" etc)
tblBooksKeywords - to create a many-to-many relationship between the above two tables.
Now in tblbooks i have about 270 records (1 per book). Now for each book in this table i would like to create a record in the tblBooksKeywords table to add a the keyword "Computers" to each book.
i.e.
Book1 - Computers
Book2 - Computers
and so on till Book 270
Now rather than i do this manually for 270 records is there a way in which i can create these 270 records automatically ?
Thanks
i want to count records in progressive access table .in simutanasly column like excel "countif" function if Attached File
Column A Column B
A Count (A) =1
A Count(A)= 2
A Count (A)=3
B Count (B) =1
B Count (B) =2
A Count (A)= 4
How I can automatically remove blank records from access table??
View 3 Replies View RelatedHi, im having a problem with shring my db over a networkt,
as far as i understand access 97 locks a page (2k) of records when a record is being edited,
does anyone know a way in 97 how i can get access to lock ONLY the EDITED record and not the page?
in Tools/Options/Advanced i have "edited record" locking clicked and each form locks only the record edited but its still locking the page, i know there is "record level locking" feature in Access 2002 but not in 97, is this possible?
thank you
Scott
Hey all,
I have two records for a list of employees: start date and end date. I want to create a query for the top 5 employees who have been employed the longest (and are still currently employed, so end date should not exist for the top 5). How should I go about this using a top values property? thanks.
I have a simple database with 2 tables, students and progress. I need to set up my database so that when I create a new record for a student (using a form I've created) it automatically creates 4 new entries in the progress table using the ID I have generated in the form and a task number (1-4) for each of these entries.
Additionally, once all tasks are set to complete = true, I need to set the field "all tasks complete" to true. I'd like to do this all without vba if possible.
I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.
The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.
The portion of code for the loop & email:
Do
'Build outputbodytext
DoCmd.OpenQuery ("EmailQ2")
DoCmd.OpenQuery ("EmailQ1")
'set up email
Set rec2 = CurrentDb.OpenRecordset("outputbodytext")
strSendTo = rec2("Email")
[Code] .....