ms-access

Quickest Way to Copy SQL Data

I could use some suggestions / ideas. I wrote a console application that queries all data from a table in MS Access (I know, but I inherited it) to a SQL table. It runs every morning as a scheduled task. The fields between the two tables are not identical. Currently I select all data from the MS Access table, loop through the dataset and...

ms-access - grouping select statement

here's the query: SELECT [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)],Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [CountOf1 0 Preanalytical (Before Testing)] FROM [Lab Occurrence Form] WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]!...

ms-access: query (concat multiple records into one)

here's a glimpse of the original table: Occurrence Number Occurrence Date 1 0 Preanalytical (Before Testing) Cup Type 2 0 Analytical (Testing Phase) 2 0 Area 3 0 Postanalytical ( After Testing) 4 0 Other Practice Code Comments 1477 2/5/2010 1.1 Specimen Mislabeled PURSLEY 1476 2/5/2010 1.1 ...

MS Access 2007/2003 - Referencing a control on a sub form, from the parent form

So if I want to reference a control (like a text box) on a sub form, from the CBF of the parent form, how should I do this. me.SubForm.SubControl gives me an error. thanks justin ...

Access data conversion issue

I'm using Access 2003. Have a table with some date values in a text data column like this; May-97 Jun-99 Jun-00 Sep-02 Jan-04 I need to convert them to proper date format and into another Date/time column, So create a new Date/Time columns and just updated the values from the Text column into this new column. At first it looked fine,...

Export data from Access to Excel without losing leading zeroes

I have a table in Access I am exporting to Excel, and I am using VBA code for the export (because I actually create a separate Excel file every time the client_id changes which creates 150 files). Unfortunately I lose the leading zeroes when I do this using DoCmd.TransferSpreadsheet. I was able to resolve this by looping through the re...

Selecting from a table and inserting into another table's column of a different type using query in ms access.

I have some txt files that contain tables with a mix of different records on them which have diferent types of values and definitons for columns. I was thinking of importing it into a table and running a query to separate the different record types since a identifier to this is listed in the first column. Is there a way to change the val...

How can I migrate schema and data from Access 2000 to Sql Server 2008?

Title says it all. I want to copy the data from an existing Access 2000 database to an existing Sql Server 2008 database. I would prefer to have the ability to only copy some data, so that I may add constraints (like ON CASCADE DELETE) as I go. Any ideas? ...

sql access how to return between dates

How do I specify a date range in MS Access? Is the below query correct? Do I have to put "2/1/2010" in quotes? Or do I have to do something like date(2/1/2010)? SELECT [Occurrence Number] as Fld FROM [Lab Occurrence Form] WHERE [Practice Code]="ACCIM" AND [1 0 Preanalytical (Before Testing)]="1.1 Specimen Mislabeled" AND ([Occu...

update insert msaccess

i wish to update or insert if missing into an msaccess database using asp. i was trying something like: IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) and UPDATE Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 I...

display image in crystal report using physical path.

Hello, i am using vb.net and access. in which i have one field in which i have saved only the path of that image. Now i want to display that image in crystal report dynamic.so what should i do? i want to fetch the image path in crystal report and display in it. please try to show me a code so i can identify reply me soon thanks ...

Randomly Assigning Positions

Hey, Here's my basic problem. Let's say I have 50 employees working on a certain day, and I want my program to randomly distribute them to a "position" (I.e.: front desk, phones, etc) based on what they have been trained on. The program already knows what each employee has been trained on. What is the best method pragmatically to go thro...

how to update a recordset in a subform using vb (I am getting error #3426) in Access 2007

hello, I am trying to update a control in a subform. Actually I am calculating the number of months. here is the code: (which doesn't work!) While (Not Me.Form.Recordset.EOF) months = Round((Me.End - Me.Start) / 30, 0) Form_FinanceSubform.[number of months] = 0 Me.[number of months] = months Me.Form.Recordset.MoveNext Wend the error ...

sorting a listbox in VBA

I have a listbox with values. Now I need to sort that listbox, BUT NOT BY ALPHABETICALLY. You see the values in a listbox are from a table. ------------------------ | name | order | size | ======================== value1 4 value2 3 value3 1 value4 2 I hope I made myself clear. So the list box has the items "value1, va...

Acos & Asin substitute in Access

How can I calculate track & distance & wind vectors in Access. Many thanks ...

MS Access 2003/2007 - Passing data through a variable on unbound forms vs. a hidden text box

Ok so I hope the title of the question matches what I about to ask, but here is what I am trying to get at: So I have an access database that uses a number of unbound forms, and the purpose of the forms are to collect data and save to various tables with VBA click events using SQL statements (INSERT or UPDATE based on whether the ID of ...

Join expression not supported for inner join with subquery

In Access 2003, I'm getting a "Join expression not supported" exception for this: SELECT ID FROM Recipes INNER JOIN (SELECT RecID, COUNT(RecID) AS NumIngredients FROM Ingredients GROUP BY RecID) ON RecID = ID I have two tables, Recipes and Ingredients. Recipes.ID corresponds to foreign key Ingredients.RecID. I want to get t...

Avoid requerying mysql db from Access when sorting

Hi there I have an MS Access frontend and MySQL backend. Everthing runs well however I am trying to avoid the following. On an Access form, I use it to do read queries. Once the results are shown on the Access form (excel like format), I can sort A-Z, Z-A etc The thing is that everytime I do any sorting, I can see on MySQL Administrator...

A Registration system using HTML and Microsoft Excel/Access

My friend asked me to make a registration software for his blood donation camp. He told me that he can't host an ASP.NET or PHP page. Also that since they'll be running the software in many systems, which may not be interconnected, they may not be able to use a Database server. Is there a way to store/read data from an MS-Excel or MS-...

Using lookup value to filter a split form

Hi there, thanks in advance for any help I'm using a macro to perform a filter on a split form by taking a value from a text box. Everything works fine, except for fields that use a lookup to get there value. Unfortunately I cant figure out how to search the shown value in the field as opposed to the bound column. Should I use dLookUp...