Query Execution Time
Jan 3, 2006
I have a query, it joins a local table to a remote table (Oracle Database Table) which makes it slow(ish).
When I am in design mode and i click the "View Datalist" button, which visually shows me the results, the query executes in about 12 seconds.
When I use the "!" Button to physically Execute the Make-Table Query, it takes hours.
What things should i look for that might cause this problem? Where do I need to optimize.
To me it seems rather odd that the query would execute so fast when i view the data than when it physically executes the data.
Same speed problems when i just run the query as a Selection Query as opposed to the Make-Table Query, so it's not an issue of writing to disk/etc.
View Replies
ADVERTISEMENT
Mar 14, 2005
Hi,
how can i create a comand button that open a form at predifined date?
example: if current date=2005/14/12(yyyy-dd-mm) then open form "example"
thnks,
Kopas
View 4 Replies
View Related
Dec 8, 2014
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
View 1 Replies
View Related
Sep 24, 2007
I am trying to take input for my reports based on a query result dynamically for which i dont want to display the Datasheet after the execution of Query ,inturn i am displaying results in Report. Any help on this would be appreciated.
View 14 Replies
View Related
Dec 19, 2005
Hi
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
RMA
View 5 Replies
View Related
Jul 13, 2014
I have the following tables:
Supplier
Supplier# [Other columns]
Material
Supplier# Material# [Other columns]
DeliveryHeader
Delivery# Supplier# [Other columns]
DeliveryDetail
Delivery# Supplier# Material# [Other columns]
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier.
On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
View 4 Replies
View Related
Feb 12, 2006
i have created a new utility DB that has only one form..when it opens I set the startup form "Main" and on the OnOpen event of this form I have some code to open another database and close this one...
this means that whenever i open this database, it opens, opens up the other one and this utility DB closes...and i cannot access it coz it always opens and closes automatically...how can I stop it from executing to be able to edit its VB code?!
Thanks, this is driving me mad !
View 2 Replies
View Related
May 23, 2007
Hi Folks,
I am wondering if it is possible to execute a DTS package via Microsoft Access. I have done a bit of research and cannot seem to really find anything that claims this is possible or how to go about to get it to work. I am running SQL Server 2000 which has quite a few DTS packages ... it would be really neat if I could just create a small interface in Access whereby a form has a few buttons on it that can be clicked and the DTS packages can be executed on the server, this way folks won't need to log on to the server and run the DTS packages via SQL Server Enterprise Manager. What would REALLY be awesome would be if values can be passed between SQL and Access but I think if that is possible it would be quite an animal to achieve (I know it is possible via Visual Basic programming but not really worth all the work to create an independent program just to run a DTS package or pass a value). If anyone can provide me with any information or where to look I would appreciate it.
Thanks,
Joe
View 3 Replies
View Related
Jul 3, 2005
Hi everyone,
This is my problem. Almost all the time(90%), I open the main form of my application and a large part of the form is blank(white). If I open it from the Database window it often work well and all the form is visible.
When the bug occur, If I just switch from Access to another application like Internet Explorer and I come agian to Access then all the form is visible.
Is someone ever seen that kind of bug. What that can cause that problem. How can I solve it?
N.B. There is a lot of controls on my form and I guess that bug can be linked with it. Seems like Access is trying to open my form too rapidely.
View 1 Replies
View Related
Jan 1, 2006
I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.
I tried to run the following statement in my VBA code to update a table in my database:
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)
Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?
The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.
Dim rstDataLoad As New ADODB.Recordset
With rstDataLoad
Set .ActiveConnection = cnnSHELL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;"
.Open
End With
'Calculate Retire_Date
rstDataLoad.MoveFirst
Do While Not rstDataLoad.EOF
Calculation omitted.
rstDataLoad.MoveNext
Loop
rstDataLoad.UpdateBatch
rstDataLoad.Close
Set rstDataLoad = Nothing
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
View 4 Replies
View Related
Jun 19, 2007
I'm using hte Shell() function but this doesn't wait for the executable file to finish so I'm looking for a command line function that would wait until the executable file is finished executing...
I saw this function WaitFor but it is not supported in MS-Access? So, what do I do?
Thanks...
View 7 Replies
View Related
Dec 20, 2014
I have this segment of code that adds data to fields FName and FPath in the table Files. It works fine but the syntax is a nightmare with all the double quotes and symbols.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
I have another field in the table Files called CompletedBy that shows who updated the table (they type their name into a textbox Text1 before clicking a command button to run the code). I would like to add this data to the table during the execution of strSQL. All the records added to the table Files will have that value in the CompletedBy field (say, Bob Jones).I tried the below changes but the code doesnt work.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath, CompletedBy) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """ & Me.Text1 & """;"
CurrentDb.Execute strSQL
How would I add a value in field CompletedBy, from the textbox Text1, to all records added to table Files when the code is run?
View 2 Replies
View Related
Aug 23, 2013
I have a form that loads when the database is open, and the form has buttons etc. that leads to queries and reports in the database. (Queries open in a form). Since most queries take a while to load, I was wondering if there is a way to Pop up a message box stating "Please Wait, Query is running..." (or something like that), and once the form opens the MsgBox should close automatically. (The user should not have to hit Ok button.)
Most users of this database would have restricted access such that they would not have navigation pane and toolbar visible - hence I want to make it a touch more interactive when a process is running...also if someone is new to access they might not notice that taskbar status and I don't want them thinking the program is stuck etc..
View 4 Replies
View Related
Nov 12, 2014
I'me running on Win7, Office 2010, Acc2003 format (.mdb), writing data to Excel 2010 (.xlsx)I have code to create an Excel file, send record set data to it, and then add some formulas and formatting. I was trying to tune up the formulas, when I got a pull back:Access VBA code suddenly stops running, and the file gets displayed in Excel
Code:
'ApXl is Application.Excel
'xlWSh is the Excel WorkSheet
'rst is my DAO.Recorset
[code]...
The same happens when I have ApXl.Visible = True
View 10 Replies
View Related
Nov 3, 2014
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE
FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID
WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
how i get end time due to CHECKTYPE]=O
View 1 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Jun 7, 2006
I have a link table with a field in GMT time. I want to convert the times to local time in a query. I know that I should use DateAdd, but I am just wondering how to know summer and winter time. On the summer our local time is +2 GMT and on winter +1.
View 14 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Sep 29, 2005
Ok, this might need to be in the macro section or something, but just incase, I have a query that I want to run at about 10pm every night (when everyone has gone home) , to make it even more complicated it queries from sage and creates a table each night (that i want to overwrite the old table) and it will need to enter in the sage login information :(
Does anyone know how to do this as to be honest, it has me at a bit of a loose end, I guess it would be a macro, but I have no idea of the commands used
View 1 Replies
View Related
Jan 25, 2006
I have a table with StartTime and EndTime, can i set up a query to show records between those times?
View 2 Replies
View Related
Mar 13, 2006
Hi
I have a DB where I take orders throughout the day and the time is stored in hh:mm:ss format.
Anyone know of a query that I can run that will show me how many oders between certain time? I.e. between 18:00:00 and 18:59:59
Thank you if you can help me!!
Ben
View 1 Replies
View Related
Mar 30, 2007
I have a table that consists of [employee], [scheduledate], [starttime] and [endtime]. I need to query this table to get the total of all employees working during a certain period of time (ex. 1400 - 2200). I have been pondering this for quite a while and have yet to come up with something.
Any help you can provide would be greatly appreciated.
View 8 Replies
View Related
Oct 12, 2005
Hi,
I have a question.
When I run the query, I want to know how long it takes to run.
How can I writes and add the function in the query for the timer.
Thanks.
View 4 Replies
View Related
Dec 6, 2005
I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.
SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC
FROM tblChat
GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date')
HAVING (((tblChat.dtDate)>=Date()-7))
ORDER BY tblChat.dtDate;
The query works fine. Now my question.
I would like the query to figure the average time per chat for each day.
Could this be done without VBA? Any ideas??
Thanks.
View 3 Replies
View Related
Aug 2, 2006
as the topic says, i'm a beginner playing around w/ access and i tried to make a query that used 2 tables, but the problem is that when i try to view the query, (since i'm using fields from 2 diff tables may be why) none of the data shows up and all of the fields are blank.
i'm trying to create a query that runs off 2 tables in order to better organize my data. so what i'm trying to do is (with the invoice database i'm trying to create) get some fields from table1 to automaticallly roll over to table2 on the query because table1 and 2 use a different invoice numbering system but some of the values are identical for the y/n fields.
does anyone know how i can fix my query so that i can make it that it merges 2 tables together and still shows the data?
View 12 Replies
View Related
