Returning A Row From SQL And Storing In Local Table
Oct 16, 2007
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
View Replies
ADVERTISEMENT
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
Nov 21, 2013
I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.
Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".
View 2 Replies
View Related
Apr 1, 2015
I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.
View 2 Replies
View Related
Dec 31, 2014
In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.
My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.
View 3 Replies
View Related
Mar 12, 2008
I have a form based on a query where I want to document the status of certain records. The original data is on a SQL server so I am linking to it in my query. I have a local table with the added fields to document my review and status. My query has both tables in it and are linked by the report number. So I have join properties to show all records from the SQL database and only the records in the local table that match. So initially the data in my local table will be blank. I want to use the form to add comments on the status. The problem is, when I type in the form fields nothing happens. It's like the query is confused and won't let the form write back to the local table.
So my question is, do I have my query set up properly so that I can read the fields from the SQL database and yet write in my comments to the local table. I am using a select query.
Thanks for the help.
Jim
View 1 Replies
View Related
Jan 25, 2005
I have a SQL table that holds financial data. There are 12 columns labled Acct_Per01 through Acct_Per12. I need to select specific GL codes and sum the values in these columns as various groupings, Private, Commercial, Ancillary etc. for each of 14 hospitals.
How can I best create a working table where I have one record for each hospital for each fiscal period.
I want the final table structure be be like the sample below.
Any thoughts on the best way to do this?
Fiscal_Period 200506
Hospital ALX
Private 79,500
Commercial 0
Ancillary 650,457
View 4 Replies
View Related
Jul 30, 2006
Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.
The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).
View 2 Replies
View Related
Jun 27, 2005
Hi there,
I am about to start working on a new project where I have a front-end in Access2000 that is linked to a ODBC Data Source (ORACLE).
There could be some performance issues in the future, when the users have to retrieve (query) the data from the ODBC over the network and it slows down. Nothing is sure yet, but when this is the problem, I will need to look at an alternative plan. I had the following in mind:
I would like to create a scheduler program, that will retrieve all the relevant data from the ODBC and update the tables that are local to each user’s Access .mdb file. Can I do something like this in VBA, or do I need to do something with the Windows Scheduler?
Any tips are welcome, thanks in advance
J
View 2 Replies
View Related
Oct 31, 2013
Adding a field in Access 2007.
I am adding a exisiting field which is already available on a Global Table and would like to add it on a local table within the same database. Also bearing in mind the db contains main objects - Tables/Queries/Sharepoint lists/forms/reports
Whats the process in doing this? Once added how does the data get populated?
View 1 Replies
View Related
Nov 21, 2004
How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?
View 2 Replies
View Related
Feb 11, 2008
Hi,
Firstly this is my first post so please forgive me if i do not conform to the usual way of asking a question!
I have an access database set up and it is working well, so far!
What I wish to do is the following, I am creating this for a cycling club to store race results...
I wish to store the time that they completed the stage in, how do i do this using the date/time field option when all it wants to do is store a complete time.
for example i wish to store 1 hour 12 minutes 15 seconds in the table as 1:12:15 for stage 1.
Plus they may have received a penalty, so in another field I would like to say store a 10 second penalty...
anyway you get the idea.... please could somebody inform me on the best way to do this, it has to be stored as a time so I can add all the stages up later and create a report!
Thanks in advance...
View 3 Replies
View Related
Mar 13, 2006
Hi
I have a database which picks up information from my accounts package. It has the following fields
ID,Name,Date,Ref1&Ref2
I need to extract the last ID number and store it in a table or somewhere and then extract the above information after that number into a csv file in the order above. It is used for my invoices and therefore when I do an import from my accounts package it updates all the transactions.
An example would be I say put 5 invoices on with a ID nr's of 1, 2, 3, 4 & 5 and output to a csv file. When I run the query/macro? it looks at the table with the last ID nr and starts from 6 and so on
I just need the ones I have done today. It has to be the ID number as each invoice is put on it gives it a sequential number, the dates could be different for each invoice.
I hope I've explained myself:)
Thanks in advance
Cheesey
View 1 Replies
View Related
Apr 20, 2007
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
Thanx for any help!
View 2 Replies
View Related
Jan 9, 2007
i have a form with two combo boxes cboMth and cboYr
i also have three text boxes in the same form with computed results: txtA,txtB,txtC.
now how can i store txtA,txtB,txtC with reference to the (Mth and Yr) into a table?
e.g: the table will looks this way (below)
===== ==== ==== ====
Mth/Yr txtA txtB txtC
===== ==== ==== ====
01/2007 580 683 93
:
:
:
06/2012 312 510 80
:
:
how shall i write the codes?
View 1 Replies
View Related
Aug 10, 2007
Hi,
I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.
Gary
View 13 Replies
View Related
Dec 4, 2013
I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table.
Table examples:
County
Code
Funds Spent
Accounting Code
Date Entered
Aitkin
123
$20.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
Aitkin
112
$23.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
Becker
145
$12.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
County
Accounting Code
Aitkin
4872727001000000
Aitkin
4842727001000000
Becker
4872727005000000
View 14 Replies
View Related
Nov 17, 2006
Hi,
I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.
View 6 Replies
View Related
Jun 27, 2006
Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.
I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.
Thank you very much if anyone could point me in the right direction.
View 3 Replies
View Related
Jan 8, 2014
I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?
View 1 Replies
View Related
Jul 26, 2012
I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:
=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.
View 2 Replies
View Related
Jul 4, 2015
I have a set of maybe 5 template word docs which I populate with info form our database using bookmarks , the word docs are currently sat in a shared network drive.
I have started wonder if it would be better to store them in a table and call them from there to keep things together.
View 1 Replies
View Related
Jun 11, 2007
N.B. This is not your usual 'Bad practice to Store calculated values in a table' post
I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.
e.g
Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]
So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.
How do I go about this?
TIA
View 6 Replies
View Related
Mar 19, 2005
I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!
View 2 Replies
View Related
Jun 13, 2015
Currently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1
PHP Code:
Function ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim Atmt As Outlook.Attachment
Dim InboxItems As Outlook.Items
[Code] ....
View 1 Replies
View Related
Nov 5, 2013
I want to be able to store 2 identical fields on the same table in the database. The copy will start off being the same as that input in the original field. 90% of the time the 2 fields will always be identical but the copy may be changed at a later stage. I looked into using the copy as a calculated field but found out these can not be updated. Is there another control I can use or do I have to code the population of the 2nd field specifically (in AFTER UPDATE EVENT?) myself. The added complication is that the original and copy must be selected from a combo box as all values are held in a separate table.
Also trying to make the second field = to the first field in the design stage of the table using default value
SO if I put default value = [table].[fieldname] I get an error stating could not find field 'table].[fieldname', and yes the opening and closing square brackets are missing from the error.
View 5 Replies
View Related
