ms-access-2007

Calling a MS SQL Server scalar UDF from vba

I am programming an Access 2007 (accdb, not adp) frontend at connect to a SQL Server 2005 backend. How can I call a scalar user defined function from my vba code? ...

Physical storage of data in Access 2007

I've been trying to estimate the size of an Access table with a certain number of records. It has 4 Longs (4 bytes each), and a Currency (8 bytes). In theory: 1 Record = 24 bytes, 500,000 = ~11.5MB However, the accdb file (even after compacting) increases by almost 30MB (~61 bytes per record). A few extra bytes for padding wouldn't be...

Import Paradox table in Access database: Incorrect collating sequence

Hello, I have a Paradox 5.0 database and want to migrate it to Access 2007. But if I try to import a Paradox table, Access gives an error message: "Incorrect collating sequence". The Help says: "You tried to link a Paradox table that was created with an international sort order that is not the same as the one you are using". What am I...

Access / Excel crossover: Should i attach spreadsheets to records

I currently have an archaic system of client records that I am trying to improve. For each client i have a directory, in that directory i include a directory for each job. Each job has a spreadsheet that i use to store their personal details, and run calculations and costings specific to their needs. In turn I also have word documents t...

Exporting Access Query to Excel

I've got an Access 2007 database on which I have created around 15 SQL queries to process specific data, I have created a main frame navigation menu using menus in Access, I now need to extract all th queries to Excel using VBA code, I have managed to do this with the code below by creating a button and specifying this code to it. Priva...

Complex SQL query with group by and two rows in one

Okay, I need help. I'm usually pretty good at SQL queries but this one baffles me. By the way, this is not a homework assignment, it's a real situation in an Access database and I've written the requirements below myself. Here is my table layout. It's in Access 2007 if that matters; I'm writing the query using SQL. Id (primary key) P...

remove autoexec macro from MS Access 2007

I have created an autoexec macro in MS Access 2007 and cannot find it! I know its there because it runs, but I cannot find it to delete or modify. I can see other macros that I have created just not the autoexec one. Can someone please point me in the right direction to find where it is located .... cheers :) ...

SQl queries searching by date range

Hi, I have a table in an Access 2007 database. All fields are of type text. Can the following be done using the where clause? If so how? SELECT * from Table1 WHERE (ColumnDate is between 26th and 19th of march 2010) SELECT * from Table1 WHERE (ColumnAge is between 25 and 40) The usual < <= operators don't seem to work. Thanks, ...

Dynamic List Box In A Form

This question involves 3 tables and 1 form in my Access database. The tables are: - Contacts - Customers - Contacts to Customers Relationship The form is: - Manage Contact What I want. A list box that displays the output of this SQL query: SELECT Customers.AccountName FROM Customers INNER JOIN [Contacts to Customers Relationship] ON ...

Access 2007 - Forms created on the fly

Hi There, Hopefully I can find some words of advice from somebody. Is it possible to create forms 'on-the fly' so to speak in MS-Access 2007 I have a structure of tables: tblCustomer tblQuestAns A customer may answer the questionnaire answers many times as they relate to all products. I propose to hopefully have an initial input fo...

Retrieve list of indexes in an Access database

I know there's a way to get a list of all tables in an Access database by using the quering the MsysObjects: "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4)<>'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name" Does anybody know a similar (or other) way to retrieve a list of all indexes...

Change an access Primary key with relationships

I have a database in access 2007 accdb extension , there are more or less 30-40 tables with related primary key "local_number". it is a text primary key with 10 lenght. How can I change the length of this primary key to 30 WITHOUT delete previosly all the 30 relationship. A similar question. I need add a compose primary key to my PK ...

Combo-box values automatically update

Hi all, hopefully somebody can help The table structure is as follows: tblCompany: compID compName tblOffice: offID, compID, add1, add2, add3 etc... tblEmployee: empID Name, telNo, etc... offID I have a form that contains contact details for employees, all works ok using after update. A cascading combo box, cmbComp, a...

How to connect to MS Access 2007, from Java on a mac

I'm looking for a way to connect to a MS Access 2007 database from Java. I don't believe the ODBC way is available to me as I'm writing on a mac and will be pushing this product to Linux. The HXTT drivers also do not work with 2007. Is there a way, a tutorial, an example, etc that accomplishes this? Setting the connection as a JNDI Data...

cascading combo box causing empty fields in next record

Hi there, I'm having problems with a cascading combo box. Everything works fine with the combo boxes and the values get populated correctly. Private Sub cmbAdjComp_AfterUpdate() Me.cboAdjOff.RowSource = "SELECT AdjusterCompanyOffice.ID, AdjusterCompanyOffice.Address1, AdjusterCompanyOffice.Address2, AdjusterCompanyOffice.Address3, ...

Connecting to PGP Desktop encrypted MS Access database

I'm using a computer that has an encryption program installed called "PGP Desktop". This app encrypts individual files in a folder so that you can only open them if you are running PGP Desktop and provide the unlock key. I need to import data into SQL Server from the access database which is PGP Desktop encrypted. There is no password ...

Access 2007: Unrecognized database format

My client has an Access 2007 (accdb) file. It's stored on SharePoint, and there are three users that check it out, make updates, and check it back in. One user is on Windows 7, another on Vista, the third on either Vista or XP (my client wasn't sure). There are no linked tables, and its only ever used by one user at a time. I'm told th...

How do I correctly use two Not Exists statements in a where clause using Access SQL VBA?

I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address. What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows: SELECT UnitID,...

How to set the itemdata in a combox from key value

Hi, How would i set the itemdata from getting just the ID/key of a row, if i didnt know what order it is in on the list For example I have a combo box showing the following: cmbPeople: ID Name 2 Rod 4 Jane 6 Freddy eg. I know I want Jane to be the focus of the combo box, and i have the ID but i dont know that its the seco...

Use subform record set as domain argument in DAvg()

Is it possible to use a subform's 'current' record set as the domain argument to DAvg() (etc.)? Basically, I have a subform that displays a subset of records from a query. I would like to run DAvg() over this subset. This is how I've gotten around it: =DAvg([FieldToAvg], [SubformQuery], "ChildField=Forms.MasterForm.MasterField And Fiel...