I have Access 2010 tables linked to Sharepoint 2010 lists and my table becomes disconnected when I run a delete query on the table in Access.
I can append the table and Sharepoint stays connected.
Can I do an append that deletes the previous contents of the table?
I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.
View 1 Replies View RelatedI have a form with a Count textbox. It's control source is a field named "Audit Count" in the table "DB Audits". The idea of the textbox is to display a running count of how many audits a specific auditor has completed that day. To do this I have the textbox set up with a default value of:
Code:
=DCount("[Loan Number]","DB Audits","[Auditor] = fOSUserName() And [Audit Date] = Date()")+1
Assuming I'm using the DCount function correctly, this is supposed to count the number of [Loan Number] records entered in the "DB Audits" table by the auditor (whose name is found using fOSUserName()) on today's date. So, for example, when opening up the form at the beginning of the day the Count textbox would read 1 and when the auditor clicked Save and New it would increase to 2.
The problem I am having is something I encountered while doing some random tests of the form. If an auditor submitted an audit (let's say the 1st of the day), the form correctly displays the next count as 2. However, if the auditor were to delete the record from the underlying "DB Audits" table while the form was still open, if they were to enter a new record, the Audit Count field would display 2 even though it should be 1 (since the 1st record had been deleted).
How can I have the Count textbox refresh whenever someone deletes a record from the table while the form is still open? I tried a Requery command using the AfterDelConfirm event but I couldn't get it to work.
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
View 3 Replies View RelatedHi everyone,
I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g
cboContinent cboCountry
Africa Zambia
Africa Congo
Africa South africa
Europe England
Europe Holland
If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland
Thanks
Humphrey
I have two tables [A and B]. I combine these tables with a query. I create a new table [C] with data from the query with append...So far this works..
My problem: I change data in the first two tables [A and B].. How can I auto update the data in the new table [C]
Please a solution!
I have the following code, the purpose of the code is that to take all rows from each table to append them into one table. However, I am testing this code with 2 tables (Table2 and Table3) each table has 2 records, when I run the code, it keeps adding records to table 1 that exceeds one million. what is wrong with my code?
Dim tblString, I As Integer
Dim rstFrom As Recordset, rst2 As Recordset
Dim db As Database
Set db = CurrentDb
Set rst2 = db.OpenRecordset("Table1", dbOpenDynaset)
[Code] .....
I'd like to copy data from an excel spreadsheet and paste it into a temp table in Access and then hit a button which will run an append query and append all the data in the temp table to a permenant table.ow to create a temp table?
View 3 Replies View RelatedHi,
I have built an Access DB containing 3 tables: dimensions, time, companies. The tables are not linked and are to be used to look up values for the new form. The goal is to create an Access form that would allow the user to select distinct values from all 3 tables, enter some own data and then execute an append query to add the record to the main table.
Something like this:
Initially I have 3 tables:
Prepopulated Dimensions table with fields:
dimension
....
Prepopulated Time table with fields:
Date
Day
Month
Year
Prepopulated Companies table with fields:
Company
....
My form is to be able to select distinct values (combobox) from all three fields:
Company
Dimension
Day
Month
Year
Value (data entered by user)
The record then is appended to the Main table containing:
Company
Dimension
Day
Month
Year
Value (data entered by user)
Thanks a lot for your time and help!
Polar
I have a table in MS Access 2010 with a Date field and 3 Memo fields. I wish to append data to the Access table from a MS Word doc that has a date field and 3 memo fields. My wish is to have a command button on the MS Word doc which appends the data to my Access Table.I am aware that a web page can append data to an Access table.
View 2 Replies View RelatedI am trying to import from Excel and append to an existing table. My excel sheet is named tblStatus and I am trying to append it to my Access table "tblStatus".
I get an error message that says "The first row contains some data that can't be used for valid Access Field Names. In these cases, the wizard will automatically assign valid field names." (I used the excel sheet to set up my table.) After I click OK, I get to the point where I can click finish, and I get a "Subscript out of range" error.
MY row headers are:
strOrderOps
strOrderNo
strOperation
strOrderType
dtmBasicStartDate
dtmActualFinishDate
dtmCalFinishDate
strStatus
I am not sure what is going on.
I have two tables that have the exact same structure. They both started with the same data, but each were changed separately. (Two copies of the same DB, editted separately). I need to review the data in each record and determine what has changed or if records have been added. Is there an easy way for me to do this? (Note: Each record does have a primary key)
View 1 Replies View RelatedGreetings to All,
I have one table (tblLink) that resides on an SQL Server quite a distance from me. I have another table (tblLink_r) that resides in a local .mdb file. The tables are identical except for the data they contain and where they are located. Only one index (LinkIdx=autonumber) exists. I am making sure there are no duplicate indicies in the LinkIdx fields because I started the LinkIdx field in tblLink_r at 2100000000.
When I open tblLink (remote), I can scroll to the end of the 21,000 records relatively quickly - read: little latency. Queries performed on this table similarly show little latency.
When I open tblLink_r, I also have little latency. This table has 100 records.
I need a single object which contains the contents of tblLink and tblLink_r. I have tried a UNION ALL, but this query takes upwards of 4 minutes to complete which I can't understand since either of the constituent tables only take ~.5 seconds to call up.
I am hoping someone can provide a way to speed up the UNION ALL query or provide an alternate means to combine the contents of these tables.
I am in the process of revamping an old database. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. E.g. If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field.
View 4 Replies View Relatedsorry for the title. i didnt know what to write.
this is the situation:
i have a form where it populates a table in access (ssmdata) from an oracle table w_ssm_data). this works perfectly.
Then the form read the hole new table in access (ssmdata) to update a specific field.
Problem:
If i open the access table (ssmdata) it is sorted by the flight fields. However, when the form reads the table in order to update the specific field the table seems to not to be sorted by the flight field.
More info:
The ssmdata table in access has many primary keys (flight, departure,arrival,caterer,frequency and aircraft).
If i take a look to the W_ssm_data table in oracle it is not sorted by flight and it is sorted in the same way the form is reading the ssmdata table in access.
example:
table in access:
flight dep arr caterer aircraft frequency
1 a b a 111 67
1 a b a 222 134
2 c s c 111 5
table in oracle:
flight dep arr caterer aircraft frequency
2 c s c 111 5
1 a b a 111 67
65 t h t 252 1
when the update step starts it reads the access table as it looks in oracle. but if i open the table in access it looks sorted by flight.
thx 4 your hlp again, max
Hi,
I am trying to copy the contents of a table with 11 fields into a new table which is the same except for an additional two fields at the end.
Any advice would be appreciated..
Thanks..
Any way in MS Access to read the TOC data from an Audio CD? It is I believe in the lead-in part. I have a music database, and would like to be able to read the track data directly from the CD (Title, artist, timing), without having to type it all in again.
I know this can be done, because my CD labelling software extract the data automatically - but can Access do it?
I have some code to create a table of contents/index for a report which has "chapters" which can be added or dropped depending on whether we choose to stock something or not. And I'm getting the strangest issue. The very first "chapter" starts fine on Page 1. But the next item we sell (Chap 2), then starts on Page 3, instead of Page 2. Here's what's strange. Every other item is then one page off. Below is my code.
Code:
Option Compare Database
Dim db As Database
Dim TocTable As Recordset
Dim intPageCounter As Integer
Function InitToc()
'Called from the OnOpen property of the report.
[Code] ....
The InitToc fx is called upon the opening of the pricebook, the UpdateToc fx is called upon printing. There's something in here where I think I'm calling the functions incorrectly, but I'm not sure quite what the error is.
I want to use Cascade Delete on a main table that has relationships with a few tables. The type of these relationships are weak and the delete will work perfectly I think. But, I am concerned about how I can only delete the FK from one of the tables because it contains a non-prime FK.
For example: Customer buys House. So Customer PK is in House as FK.
If I delete customer, I don't want house deleted. Is there anyway that is native to access in how I can only delete the FK from tblHouse without deleting the house record.
Hello,!
I have a stand-alone form with one table as its source with no referential integrity issues. I am using Access 2000 version.
I used the commands
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
to delete a record, and it does nothing, no error message, but does not delete either.
I found the help site
http://support.microsoft.com/default.aspx?scid=kb;en-us;182435
They mentioned it sa for Access 97, but I thought it relates to what I was facing. I took off the Modal and Pop-up properties back to 'No'. Still the same problem. Then I substituted the above 2 commands with
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
But now I get the error message
The command or action 'DeleteRecord' isn't available now..
Have you seen this before? Any help is greatly appreciated.
Vchell.
This may seem a very simple question, but I really don't know how to do it. Basically, I want to MessageBox contents of a table with a button. I have a table called Map, and has two fiels: Column Number and Description. When a button is pressed, then I want a messagebox to come up and show the column number and the description of a single field. Although I know it is very wrong, here is the code I used for this (r![column Number] = 4 is meant to be the forth column number in the table):
Dim d As Database
Set d = CurrentDb
Dim r As Recordset
Set r = d.OpenRecordset("Map")
r![column Number] = 4
MsgBox "Column No: " & r![column Number] & ". Description: " & (r![Description])
By the way, there are seperate buttons for seperate fields. It is DAO as well
Thanx in advance
Hope this works, The table is as follows, ish
Name1 y/n1 y/n2 y/n3 y/n4 y/n5
a...........y......y....n.......n.....n
b...........n......n....y.......n.....y
c...........y......n....n.......n.....n
d...........n......n....n.......n.....y
e...........y......y....y.......n.....y
f............n......y....n.......y.....y
g...........y......y.....y......y.....y
what i need to be able to do is take a form/query answer some questions, y/n1= y/n2= y/n3= ......
Then off the back of this be able to pull out the correct item from column name1.
But i don't need an exact match on all columns. If i am only interested in y/n2=y i need to display all matches
My head says the following If y/n2=y and y/n3=y Then Name1 = e and g
or another example if y/n2=y and y/n4=y and yn5=y Then name1 = f and g
if a y appears in more columns it doesn't matter but it has to be in all those columns i am looking at
can anyone suggest the simplest way forward on this.
I am creating a report which is about 200 pages and would like to add a table of contents for each category i have with page numbers, however i have googled and tried many different things but to no avail and what it the correct way or even showing me on a sample database.
View 2 Replies View RelatedI would like to ask if it is possible to use the contents of a table as a comparator? For example, I have this column in my table that has the schedules of the professor, if the professor for example failed to login within fifteen minutes after his/her supposed start of class a message will be displayed on the screen notifying that the professor has not yet logged in.
View 1 Replies View RelatedI 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.
I have a field called "Bad Pc Part" which is a listbox. THe problem is I want it to display the contents of 3 other fields from another table.
I created a query for the Bad Pc Part field but the problem is it only displays the information in the first field of the table. It doesnt show the contents of the other columns. I need it to do this.
Please Advise3.