ms-access

Access 2007 upgrade DAO references conflict

I am updating an Access 97 DB w/ VBA code to Access 2007. The VBA code uses DAO objects that Access 2007 references when I converted the db files (first from 97 to 2002, then to 2007). My problem specifically is with the two references: "Microsoft DAO 3.6 Object Library" and "Microsoft Office 12.0 Access database engine Object Library...

Select top 1 for each group

I have an Access database that contains a table with information about parts we sort. This table has an autonumber ID field and a 110ID that links to another table with the part information. It also contains a sortDate, sortShift, sorted, scrapped, and repaired. I need to find how many parts have been sorted since the last defect (non...

Ms Access gives rubbish when updating a join column

Updating a column that is part of your selection criteria should be no problem I think/thought. I still have a query that gives odd results: update table1 as t1 inner join table2 as t2 on t1.id = t2.old_id set t1.id = t2.id I use table2 to map id to old_id. table1.id and table2.id are both primary keys. table2.old_id is also unique. ...

open file from path stored in access db when user clicks button on form

I'm new to Access VBA development and being asked to debug and add features to an Access 2007 application that two previous developers worked on. A form displays records from a database and shows a button for each record. The button is supposed to open a file using the appropriate path. But when the user clicks the button, it always use...

How to save a ado recordset into a new local table in access 2003?

I'm trying to import tables from a FoxPro 9.0 database into access 2003. So far, from google searches and many trials, the only way for me to connect to the tables is through an OLE DB connection programatically. I have set up 3 ODBC connections with different configurations but none of them work. I get unspecified errors, that I can't f...

microsoft access table designing

How do I format a field to be 10 characters or numbers i.e. 112255353v or 5555551155 (for both these conditions) The field should contain only 10 characters and it can be numbers or letters or both. ...

SQL 2000, Access Query

Hi, I have a SQL database where one of my fields 'Status' has one of 10 entries in it (not from a dropdown list). I let some people have access to the data through an Access database, they can add and modify entries. My question is, in Access, how can I make it so that the 'Status' field is a drop-down list from which the users can ch...

Transferring data from one field in a table in a database to another field in a different table but same database in VB.NET

I am creating a database in VB.NET for a movie rental place. I currently have three forms; Member Information DVD Information Borrow DVDs What I would like to do is when I am viewing a member's details, if I click a button 'Borrow DVD for Member', the member's ID number transfers over to the Borrow DVDs table in the Member ID which a...

Running an external script from Access

Hi all, I want my Access application to run an external program (in this case a R script) after the user clicks a button. I use this code: Dim RetVal RetVal = Shell("""C:\Program Files\R\R-2.10.1\bin\R.exe"" CMD BATCH --no-environ --silent --no-restore --no-save ""c:\test.R"" ""c:\test-result.txt""", vbHide) MsgBox RetVal This works ...

Trying to improve performance by removing DLookUp - Access 2007

Hi there, could anybody possibly give me some pointers as to the appropriate method to use here. -Edit I have fields in a form that relate to a users 'risk address', and I have a checkbox that updates the values in these fields that if selected loads the 'contact' details (from the table tblInsPersDet) into these fields. If the checkbo...

create a customised openFileDialog

i have an access program which i have been working on and i want to create a openFileDialog which will show specific folders and files. For e.g. when you click a button, instead of having the dialog showing all the folders and files, it will be given a parameter (folder name) and it will show only that folder. i am trying to achieve some...

proper way of renaming / backing up files

i am constantly making changes to access and excel file macros and databases. what i usually do before i modify the file is i make a copy of the file and rename it to the filename_todaysdate.ext. is this the proper/safest way of doing things? ...

Access 2003 combo box acts like it is locked, but it is not

I am designing an Access 2003 form (continuous) with a combo box in the form header. This combo box has as a row source another table that is unrelated to the form's record source. I have created the combo box and set its row source to the table i want. When I click on the combo box arrow, I see the values I want in the drop-down. I c...

MS Access 2007 - Cycling through values in a list box to grab id's for a SQL statement

Lets say I have two tables, one for transactions, and another table who's primary key is the foreign key in the first table, and this relationship simply associates locations to transactions. I have a form with a list box that shows all the potential locations, so that the user can open some dashboard forms that only pertain to a given ...

MS Access and SOAP, XMLRPC etc

I have been searching high and low on Google and finding very little information on using Web services with MS Access 2007. I'm sure this is possible somehow but I can't find any relevant information. Any ideas? ...

Query to eliminate multiple rows based on oldest date

Working in MS Access 2003 SP3: I have a query that I am running to find what 'cars' were sold with a date after the delivery date. I have thousands of rows. When it is all said and done, I want to just have a handful of rows for each 'car' and then the oldest date. Any suggestions? CAR DATE ORDERED DATE DELIVERED CUSTOMER NU...

What is the current best practice for distributing a database with an app?

Platform: Windows / Database: Access / Language: C++ Several years ago I had a Windows app that uses a database and I just installed an empty database as part of the install process. The database had all of the queries and macros I needed. I am wondering what the current best practice for this sort of thing is: Do you build the databas...

specifying relative links for linked table manager in access 2007

i have a front end and back end of an access database. the front end has linked tables and i need to do a relative link instead of an explicit one. i need "../database" instead of "address/database" is it possible to do this? or must i specify the exact URL? ...

ms-access: update record where some field = textbox value

can someone please help me with the following query i need to update a datasheet (table) in access through a form: i will have something like this SQLtext = "update table1 set column1="sometext" where column2=textbox1.value" DoCmd.RunSQL SQLtext is this possible to do? i have a textbox on a form and when i click a button on that fo...

SQL/access question: Do not append that student-term record if the student AND the term already exist!

I have another SQL/access 2007 question that seems really basic but I'm not sure how to Google for it. I have this table STUDENT-TERMS with the following fields: StudentTermID StudentID (links to STUDENT table) TermID (links to TERMS table) TermGPA (this is the nugget of info that needs to be recorded) STUDENT table looks like this...