If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
I have a fairly simple append query that appends two columns of data to another table - all good. Except, the destination table has a field 'ServiceDate' that I would also like to be completed at the same time with today's date. I presume that this is =Date(), but where do I put it to make this happen?
View 2 Replies View Relatedi have a database in access 2003 when i open it with access 2003 it shows data in table but when i open same table in access 2007 it shows only header rows , no data
how can i see this data into access 2007 or excel 2007.i want to link these table data with excel 2007 or access 2007 but with above problem i can't do it
I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.
Code:
Option Compare Database
Sub LocalServerConn_Test()
Set conn = New adodb.Connection
Set rst = New adodb.Recordset
[code]....
how to create a form in access to insert/update/delete data from a table in sql server?
View 4 Replies View RelatedI have a database I took over, that generally uses linked tables to a MYSQL servers on another machine. The Access database uses a machine DSN to connect.
There are many queries that contain references to linked tables using another name. For example, the table is "service unit" but the queries are using "SU" as a reference. I cannot find any database object with that name SU. There are many such tables referenced like this - all uppercase letters, which is a common thing they all share.
Now, the server had a file integrity issue, and now the queries that contained the SU references will not work anymore. I created a linked table and called it SU and but the queries still won't work until I edit the queries, remove the old SU table, and add the new one in. So,
1. Where those table references could have come from, if it's not a linked table? Is there a hidden location on the server, or in the access database that stores another connection method?
2. Why won't simply adding a similarly named database object work the same way? Why must I re-edit all the queries?
Is it possible to select some data in queries in the same db to a table?
Many thanks!
I am trying to consolidate a few tables I made to make them less numerous and hopefully easier to handle. At one point I received an error message that said I had exceeded the maximum number of indexes in a table (32) and thus it could not be saved. When I scroll through my table in design mode every box that has "Indexed" as an option is marked at "no". Many boxes do not have that option and thus I am not sure if those are automatically indexed(?), not indexed(?) or that indexing is not relevant for those values(?). I definitely do not have any, let alone 32+ boxes, that have "indexed" checked to "yes" though. They are all "no".
How do I figure out which columns in my table are indexed otherwise so that I can reduce those numbers and get more columns on my tables (by the way, I am no where near the limit of 255 columns on any of my tables)
I have a command button on a form which loops through pages on a tab control and constructs an SQL string to insert a new record into a corresponding table (each tab has it's own separate table)
The tables are linked via a common account ID (an autonumber on a separate 'Core' table, the latest record for which is generated earlier in the code; this section creates a linked record in each of the other tables)
I've used a naming convention in each page such that each control name and it's corresponding field name in the destination table are the same (apart from the first 3 characters which I use to identify the control type, i.e. "chk", "txt" etc.)
For one of my pages, the insertion of the new record keeps failing. I've gotten it to work once or twice but only by randomly changing some of the values on the form (checking / unchecking boxes or keying data into random textboxes etc.) But I can't identify why it works sometimes and not others?
The other 3 tabs/pages insert records with no issue, every time.
I've tried debugging but I don't get any error message when I execute the constructed SQL; there are no apparent control violations and all of the relevant fields are correctly named & referenced. The only 'required' field in each table is the common Account ID, which is present, correct & not duplicated, so omitting null / empty fields or passing empty strings / values for the remaining fields shouldn't be an issue (?)
I can only assume it's a problem with a value being passed somewhere but what's stumping me is that on the other pages, it inserts the records exactly as expected (whether data has been provided or not)
Stepping through the VBA, it looks like a record should be inserted - but when I check the table, there's nothing there?
Code:
Dim pge As Page
Dim ctl As Control
Dim strSQL As String
Dim strSQLFields As String
Dim strSQLValues As String
[Code] .....
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
there is a query which i made which counts data from two tables RAUTH AND PERS
SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held
FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy
GROUP BY RAUTH.coy, RAUTH.Auth;
THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table
RAUTH table structure
Coy Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
pers table
Persno Name Coy Qual
124578 Gita Tata Bsc
235689 Sonu Dell BA
748596 Kiran HP BCA
459687 Sony Tata MCA
965214 Baiju Bangour CCNA
963656 Ashok Opera MCA
Has anyone else encountered this?
I wrote a Access 2000 database which has a linked table to a HTML file which worked perfectly. However, I Have recently installed Access 2007 and now when I use my database to try to open the HTML linked table it says it is either empty or not in the right format!
Any ideas anyone?
Regards,
Dalien51
Hi folks
Is there a linked table manager in access 2007?
Cheers,
B
Hi all,
I am using Microsoft Access 2002. I would like to know how to insert images in Access table and retreive those data & images using Report.
I did change filed as OLE object. And try to insert object.
But I doesn't show at report.
Is there any other way to insert images in the table?
Please kindly help me.
How to take the results of query and copy them to a column in a table?
I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....
Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info
queryreservations!AllINfo.value=tablereservations! Allinfo.value
and how to loop through records that are not filled out already?
seems like it should be a simple Do.command but I am not sure how to code it..
I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.
The sub table looks like this:
tblRevisions
- RevID (auto Number / primary Key)
- RevDate (date of revision)
- RevName (Who made the revision)
- RevDesc (What revision was made)
- RevAuthorized (Who authorized the revision)
- RevLabel (Foreign key to the primary table)
The info I want to insert is:
RevID - Auto Number
RevDate - 11/27/2013
RevName - 3
RevDesc - "Added Label to database"
RevAuthorized - 1
RevLabel - (One for each record ID in tblLabels)
I suspect that I want to start with an insert into statement, something along the lines of:
Code:
Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel)
Values ("11/27/2013",3,"Added label to database", 1, ?)
I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though.
I would like to populate fields in a Access 2007 database form.
When the "Symbol_Stock" field is chosen, how do we programmatically populate the "Symbol_Stock_Y" field and the "Stock_Name" fields
Form - record source - select a table that when the user enter a value in the field of the form it gets place into the designated table (select a table). my problem is that I have two fields in the form, one field from the data entry is suppose to go to the train table,and another field the data entry is suppose to go to the station table. if I have the form's record source have a designated table for the data entry.It doesn't have two tables for the data entry.
View 13 Replies View RelatedSQL stored procedure which is simple INSERT statement on a single table 'tblSOF'
Code:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter values below.
[Code] ....
I am stumped with the following error.
Error: Msg 102, Level 15, State 1, Procedure InsertINTO_tblSOF_sp, Line 80 Incorrect syntax near ')'.
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
[Code] ....
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
I have an Access 2007 application where don't work the link table manager.
I also try to complete reinstall the Office 2007 and also I register the Accwiz.dll in the office folder (regsvr32.exe Accwiz.dll) but the module still does not work.