ms-access

free sql code editor?

i am breaking my eyes trying to write sql code in ms access. is there a free editor out there? ...

ms-access: missing operator in query expression

i have this sql statement in access: SELECT * FROM (SELECT [Occurrence Number], [1 0 Preanalytical (Before Testing)], NULL, NULL,NULL FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalytical (Before Testing)] IS NULL) UNION SELECT [Occurrence Number], NULL, [2 0 Analytical (Testing Phase)], NULL,NULL FROM [Lab Occurrence Form] W...

How to get multi row data of one column to one row of one Column

Hi friends, I need to get data in multiple row of one column. For example data from that format ID Interest Sports Cooking Movie Reading to that format ID Interest Sports,Cooking Movie,Reading I wonder that we can do that in MS Access sql. If anybody knows that, please help me on that. ...

How to link ms excel 2003 to ms access 2003

I have office 2003, and i have develop a database from ms access 2003. What i want is to link ms excel and ms access so as when am updating either of them both will be updated ...

Query with UDF works in Access but gives Undefined function in expression (Err 3085) in Excel

I have an Access table with a date/time field. I wanted to make a composite Key field out of the date/time field and 3 other text fields in the same format as the matching Key field in another database. So I concatenated the 3 text fields and wrote a User-Defined-Function in a Module to output the date field as a string in the format "Y...

transactions in MS Access

Let's say I have the following code in a form that is triggered on some click event. DoCmd.SetWarnings False DoCmd.OpenQuery "AddSomeStuff" DoCmd.OpenQuery "UpdateSomeOtherStuff" DoCmd.OpenQuery "DeleteABunchOfCrap" DoCmd.SetWarnings True Can I assume that the three update queries I executed (in SQL Server) are not transactional in th...

Do sfSubForm.fForm.RecordSource and Forms(fForm).RecordSource refer to the same object and property?

Hi, this has me pretty confused and I can't find the answer anywhere else so thought I'd post here to see if anyone can help! I have a form in an Access 2007 database with a subform (sfSubform) embedded in it. The subform control's SourceObject is set to be another form (fForm). fForm's RecordSource starts out as a table. At one poin...

Does MS Access 2010 have Tif image control display issue?

We run MS Access as the front end for most of our business. When we upgraded from Access 2000 to Access 2003 the image control we used to print out Tif images failed to load Tifs anymore. A bit of Googling showed that MS did this intentionally and I'm wondering if the problem still happens in Access 2010. Can someone with a downloaded ...

How to compact an MS Access database, while the database is open, using vba

I am running a few modules of VBA code. In the middle of running the code crashes as Access reaches its max size of 2GB; but, if I compress the database at that point it is only 200MB. Is it possible to compress the database at regular intervals while the code is running? ...

access 2007 locked

I'm connecting the access 2007 database from 5 different machines. (C#.Net) I'm having this error message: The database has been placed in a state by user 'Admin' on machine XXXXX that prevents it from being opened or locked ...

UPDATE query that fixes orphaned records

I have an Access database that has two tables that are related by PK/FK. Unfortunately, the database tables have allowed for duplicate/redundant records and has made the database a bit screwy. I am trying to figure out a SQL statement that will fix the problem. To better explain the problem and goal, I have created example tables to use...

ms-access: displaying column as "something else"

i have the following access sql statement: SELECT * FROM (SELECT [Occurrence Number], [Occurrence Date], [1 0 Preanalytical (Before Testing)], [Cup Type], NULL as '2 0 Analytical (Testing Phase)', [2 0 Area], NULL,NULL FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalytical (Before Testing)] IS NULL) in this: NULL as '2 0 Analytical...

ms-access: DB engine cannot find input table or query

here's the query: SELECT * FROM (SELECT [Occurrence Number], [Occurrence Date], [1 0 Preanalytical (Before Testing)], [Cup Type], NULL as [2 0 Analytical (Testing Phase)], [2 0 Area], NULL as [3 0 Postanalytical ( After Testing)],NULL as [4 0 Other], [Practice Code], [Specimen ID #] FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalyt...

ms-access the ability to enter multiple values into a textbox on a form

i have a textbox on a form and i would like the user to be able to choose from different values but also be able to enter their own values, how do i do this? i would like to clarify that the user should be able to enter multiple values into the same textbox ...

ms-access: clear text in all controls with one click

i have some comboboxes and some textboxes on a form i would like to clear all of them with one line of code. is that possible? something like all_controls.text="" ...

How to run a query from the VBA Editor window in Access?

I would like to do something like this: DoCmd.OpenQuery "select * from some_table;" How would I do this? Please keep in mind I don't want to create a new query, save it, and run it. I just want to type the select statement in the VBA code and run it. ...

How to run a query and put the result into a variable

I am running a query from the VBA editor of Access: select max(somerow) from sometable I want to put the result of this query into a VBA variable. How do i do it? ...

Passing a parameter in a Report's Open Event to a parameter query (Access 2007)

Hi there, I would like to know if there is a way to set the parameters in an Access 2007 query using VBA. I am new to using VBA in Access, and I have been tasked with adding a little piece of functionality to an existing app. The issue I am having is that the same report can be called in two different places in the application. The fir...

help with sql query - in access 2007

hi i have 2 tables table items has 144602 records table A has 27721 records code in items = BAR8 in A i want to show all records that equal i try this: SELECT Items.Code, A.BAR8 FROM Items INNER JOIN A ON Items.Code = A.BAR8; and i get 28048 records !!! i need to get 27721 , how to do it ? thank's in advance ...

Comparing date in access database

How can i compare the day in the access database to a given day in c#? The date column in the database is an general date(day/month/year) try { database = new OleDbConnection(connectionString); database.Open(); date = DateTime.Now.ToShortDateString(); string queryString = "...