I have a db, and in developing it, I've created hundreds and hundreds of queries.
Now as I am learning, I see that I can create queries "on the fly", with code like:
Dim cnnl As ADODB.Connection
Set cnnl = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnl
Dim mySQL As String
mySQL = "SELECT *"
mySQL = mySQL + " FROM tblCustomers"
mySQL = mySQL + " WHERE ((CustomerID = 1 ))"
myRecordSet.Open mySQL
Set myRecordSet = Nothing
Set cnnl = Nothing
My question is, do you think it's better to code the query on the fly like this, or to create them as permanent ACCESS objects, as I have done?
My feeling is, that the ADODB method is probably more efficient, and if I can delete those many existing queries, and create recordsets on the fly, only when needed, the db would be a LOT smaller, in terms of MB, becuse of all the objects I'll be able to delete from the Queries window. Or, are they relatively insignificant in terms of MB?
Or, should I adopt the "IF IT AIN'T BROKE DONT FIX IT" approach, because all those queries do work, and there are huge numbers of forms dependant on them.
The size IS a factor, the db is over 50MB now, and I'd like to optimize it.
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions. The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
Hello everyone. I have a few issues with access. I hope someone can help me. Here is my dilemma:
I currently use a database in ACCESS. In this database is a table of stored values. I currently run a single query called “Basic Search” to find certain values of the fields. I click the “!” button to execute and my values are presented to me.
Each time I want to find the value of certain fields, I have to open access, find the query, delete the last value that was in the “Basic Search” under the “criteria” field and search again.
Is it possible to create a single search web page on my client pc (not a server) that will access the database and display the results I requested? Also, is there a way to design a “Form” type page in access that I can use to run the query? All help is appreciated.
How can I get around not being able to create multiple statements in a single query? For example, I tried to do this:
DELETE * FROM UniqueDates; INSERT INTO uniqueDates ( Entry_Date ) SELECT entry_date FROM [SELECT Entry_Date FROM BarcelonaInternalActions UNION SELECT Entry_Date FROM QPTActions UNION SELECT Entry_Date FROM TokyoInternalActions UNION SELECT FORMAT(Entry_Date , "Short Date") FROM RDActions ]. AS U;
but I received an error. I am trying to clear out a database and insert new records every time I run this query. I think maybe a better option would be to append any new records into the database rather than deleted and inserting a fresh batch of records. What do you think?
Does anyone know of a way that I could search all of my queries and VBA Code for specific columns? We add and remove columns all the time and our reports and forms crash when we miss taking them all out. We have so many that it is very hard to do manually.
I have a real issue with my database and a large number (not all) of the queries that make up the database system. The information is presented as reports but it is a problem with the query not the reports.
It seems that I have a simple select query that does a few calculations and other stuff, but yet it only selects data that has been on the system for a while. E.g. if I were to enter a new record now that would be selected by the query (and checked it had been stored in the correct table) it would not appear in the results of the query.
It seems that the queries won't find data that has just recently been entered, only results that were entered before a specific date in the past.
I'm confused why it does this? Can anyone explain?
I'm trying to modify a query in project but I get this Error and not sure where or how to fix this problem. I've been searching Microsoft Web Site.
You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems.
I've been wracking my brain and scouring the net for information on how to do SELECT queries in Access and I can't figure it out or find correct information. Some idiot had a website that said to use DoCmd.RunSQL to do SELECT queries, but failed to mention that only ACTION queries can be used in that method, so I'm back at ground zero. What I want to do is this:
I want to be able to run a select query from a table, and store all the information in some sort of object so that I can access all the individual fields returned in the query and do things with them. Is there anyway to do what I'm asking for?
I am creating a program that will allow the user to select a sale and pull up only those product items associated to that sale in the details. Any help would be appreciated. Then with in the details section the user will/should be able to enter in the Quantity needed to order. Any suggestions on how to do this. My tblSale does provide the data needed so how do I get the data to go to the form?
I am new to this forum so if I have not asked properly I apologise now.
The Database is attached. Form Orders is the form that I am using to accomplish my task. The getproducts4Sale Query is the information needed.
Sr.NO Name Amt.Tot Amt.Needed 1 Mark 100 24 2 Mark 100 80 3 Tom 150 12 4 Tom 150 45 5 Joe 50 23
Need to add a field which is Amount.LEft so that the table looks like this
Sr.NO Name Amt.Tot Amt.Needed Amt.Left 1 Mark 100 24 76 2 Mark 100 80 -4 3 Tom 150 12 138 4 Tom 150 45 93 5 Joe 50 23 27
The last field is Amt.Tot - Amt.Needed and this should roll over and subtract for all transactions of Mark, then restart again for Tom and then again for Joe. Let me know if there is any confusion on that.
I'm a bit puzzled with this so hopefully someone can help me out. I have a database that has around 20 users who key on their vehicle orders. The main orders table has a number of fields. I have started to notice that the 'Order Date' field that gets populated on completion of the record is now blank on almost half the records. (I have retrieved an older copy of the DB and the dates are there, so they did get input at one point). Further more, I have another field called Business Group which is a look up to a linked table. The Orders table is showing the records in Business Group Ascending order for some reason rather than by the primary key. And finally, when I open the table a progress bar appears at the bottom of the screen stating 'running query'. It seems my main table is now behaving like a query and is losing data.
Anybody got an idea what's going on?
And yes I have definately checked it is a table. Im using 97
I'd like to transfer access-queries to Oracle. If I export access-queries, Oracle recognize these as tables and not as dynamic queries? Any idea how to solve this or does anybody know a tool to create queris in Oracle?
I updated the tables but I am still getting the old tables and their fields in the query builder. Please help me as soon as possible.
Secondly if you can tell me a cool link that can help me in creating different form fields like buttons, combo boxes, radio buttons along with database connectivity.
I will really appreciate your help in this regard,
I updated the tables but I am still getting the old tables and their fields in the query builder.
Secondly if you can tell me a cool link that can help me in creating different form fields like buttons, combo boxes, radio buttons along with database connectivity.
I will really appreciate your help in this regard,
occassionally i will run queries that have run before, the hourglass will turn on, then turn off, and access does nothing visible, but tack manager says its running 90% cpu. . .
any ideas on what is happening or i am doing wrong?
Hi I wonder if anyone could re-orientate me and put me in the correct direction regarding the above subject. Without wittling on too much what I,ve been basically trying to do is to get a query that I've made, derive its criteria from a form that has 3 combo boxes on it allowing a user to select varying options. I have been using a forum thread which described the process in more detail and whilst I,ve followed it to the letter the end result that I get an annoying message "The expression On Click event that you entered as the event property setting produced the following error: A problem occured while MS Access was communicating with the OLE server or Active X control. It goes onto say that the expression may not result in the name of a macro, the name of a user defined function, or [Event Procedure].There may have been an error evaluating the function, event or macro". The thread took me to www.fontstuff.com/access/acctut08.htm and it is on this site that I have been trying in vain to come up with a matching result. Am I going mad or is there someting on their tutorial that is mis-leading me! I am desperately trying to adminster some 'self help' and to use the forum threads but I am still at a loss as to what to do next. Could some kind person please help...
I have a query which shows data according to a field on a form. I am trying to get this across the department on the intranet, so I replicated the form as a data access page but i can't see how to link the Criteria row on the paricular column in my query with the field on my data Access form.
Am I missing something, like data access forms are only for displaying or something ? :o
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty FROM [tbl_Audited Classroom for Week 02] HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
I have created a table with multiple Yes/No fields. I wish to create a query where the user can select from a form that displays all the Yes/No fields Is it possible for this to be done
Thanks for your help
REFINEMENT OF PROBLEM I have created a table for a small library at work. The items in the table are item_number, author, title etc. Also I wanted to classify each item into various categories. In the Table I have set the categories as Yes/No fields. An item can belong to a number of categories eg admin, finance,resources etc. What I would like to do is for a user to be able to interegrate the table via a query to find the articles that meet the criteria they want to search on. Ideally this would be done via a form that the user can tick the applicable categories (Yes/No fields). I tried setting up as parameter queries however this is very messy. Is there a way it can be done. Sorry for confusion in first posting.
