Microsoft Access is a relational database management system that is part of the Microsoft Office suite.

learn more… | top users | synonyms

4
votes
1answer
1k views

SQL Server ODBC Mirror/Failover Partner

Background/Context We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover. We have both .Net and Access ...
0
votes
0answers
28 views

Duplicate Customer Records for SQL Query from ODBC with Joins and Group Bys [duplicate]

Possible Duplicate: SQL Query with ODBC, Joins and Group Bys I am getting duplicate customer records with the query below. The main purpose of the query is to pull a list of customers into ...
0
votes
0answers
96 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...
3
votes
5answers
462 views

SQL Query with ODBC, Joins and Group Bys

I am having issues with the statement below. The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i). The query is ...
3
votes
1answer
256 views

How can I conditionally summarize multiple rows in Microsoft Access?

I have a table of data, in Microsoft Access 2003, listing multiple rows for each person. I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = ...
3
votes
2answers
226 views

Access 2000 database rapid growth

We have an Access 2000 format database running in Office Access 2003. We have a problem with the size when I merge the database; the size is about 250 MB. Two days later, the size has ballooned to ...
5
votes
2answers
2k views

Column definition for a SQL Server equivalent to Access BOOLEAN type

The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for ...
2
votes
2answers
304 views

Help with Vehicle Service database

I am using the Desktop Vehicle Maintenance template in access, but i am trying to cater it towards our farms needs. We will be keeping track of the service's done on Trucks, Tractors, Equipment, etc. ...
0
votes
1answer
172 views

Select products for an order via check boxes in Access 2010 [closed]

I have a lookup table where I have defined some products. I have another table that defines orders. This table defines which products is in the order by having a ID that is linked to another table ...
1
vote
1answer
560 views

Unable to add records to query in Access 2007

I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it. There are three tables. Let's call them ...
0
votes
1answer
204 views

Sorting Report entries on alphanumeric combination

I am trying to move over a database that was previously stored in an excel workbook. The previous setup had a different worksheet for each bookshelf and the individual shelves were listed at ...
0
votes
1answer
839 views

Access 2007 sql editor

Is there an ACCESS 2007 JET SQL editor or client (not sure the right term is) that can be used to code Access Jet SQL queries? It is very difficult to write SQL queries in ACCESS 2007 since it's like ...
0
votes
1answer
495 views

Select DISTINCT records with extra information

I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one ...
0
votes
2answers
825 views

Is it possible to join derived tables to actual in Access 2007

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below: SELECT * FROM --MyOtherTable is a "real" table MyOtherTable ...
2
votes
2answers
2k views

SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database ...
1
vote
1answer
620 views

Implementing a family database using Access 2010

A while ago I asked this question about using Access 2007 to implement a family tree model. I had all the tables and relationships mapped out in a UML diagram but realised it was getting out of hand ...
1
vote
1answer
1k views

Why do I only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008

I was finally able to create my ODBC connection but now when I go to External Data > ODBC > LINK TABLES using my ODBC connection in Access 2007 I get a huge list of tables such as ...
2
votes
2answers
301 views

SQL Server 2008 ODBC

My SQL Server 2008 is located on a remote computer so I am trying to create an ODBC connection using SQL Server Authentication. My problem is that the way i normally connect to my SQL Server is by ...
1
vote
2answers
939 views

is it possible to create a form in the intranet that can be stored in a database?

I am trying to create a form for my companies intraNet site. The idea is as follows: An employee wants to make a purchase, so they will go to the appropriate page in the intraNet They will fill out ...
2
votes
2answers
834 views

Merging two Access DB's

Our workflow requires two users to work on the same Access database. The problem is that users are geographically separated and can't work on same .accdb file simultaneously. So, the only way i see ...
0
votes
0answers
956 views

How to bulk import multiple CSV files into master tables with variable field names?

As a learning exercise, I am trying to map several data files into a few master tables. Right now, I have a file hierarchy as follows: <Root> - Client 1 - CustomerInfo.csv - ...
6
votes
5answers
3k views

What is the easiest way to get started using databases with real data?

I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living ...
0
votes
1answer
218 views

Write Access Client App for SQL Server 2005 Backend [closed]

I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very ...
1
vote
1answer
66 views

Rename cell based on duplicate compound key in MS Access

I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns. Here's an example: Col_1 Col_2 Col_3 Note A1 B1 C1 A1 ...
2
votes
0answers
237 views

MYSQL & Access ODBC

I am trying to connect mysql with access, i am settings up the ODBC correctly from the data sources, however i get a driver error. I am using access 2010 and the driver version of the ODBC conector ...
2
votes
2answers
665 views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
1
vote
1answer
81 views

Microsoft Access constrain view by date in outer query

I'm working on a query in Microsoft access and it does not appear to allow me to filter the inline view by a date that exists in the table outside of the inline view. A simplified version that ...
1
vote
0answers
262 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
2
votes
2answers
1k views

Acceptable size for MS Access Database?

I have developed an Access 2010 database. It has three main tables: HOUSES id, address, number_stories, garden, parking, imageAttachments (~1-4 per record), etc..., about 60 fields total ...
6
votes
4answers
4k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
1
vote
1answer
3k views

Records in table not showing on form Access 2007

Excuse my ignorance in asking this question - I am a database novice and need help! I will explain as best I can. In the table on my DB I have 235 records. I have created a form which references ...
3
votes
3answers
114 views

~15 data sets w/ 25k rows & ~20 columns + quick one time reports = Access VS SQL?

I am working with around 10-15 datasets each containing around 25,000 rows and around 20 columns. Data is text in name/adress/desc and the rest numbers. I need to be able to calculate certain fields, ...
2
votes
5answers
9k views

Full Outer Join in MS Access

I have two staff lists: List A: StaffID Supervisor ==================== 0001234 NULL 0001235 0001234 0001237 0001234 0001239 0001237 and List B: StaffID Supervisor ...
2
votes
1answer
289 views

Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this?

I have a requirement to read reference data from several access databases for an ETL job. The data owners have produced a file with a series of linked tables that consolidates them all into one ...
0
votes
1answer
361 views

Print filter parameters on a report

I'm using MS Access 2010. I have a form with a number of fields of various types (number, text, lookup, checkbox). Users can right click on any field to filter the form. Then I have a button that ...
1
vote
3answers
121 views

If A is next to B, how to automatically assert that B is next to A

I am working in Access 2010. Let's say I have a database that tracks where packages sit in a warehouse. Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc) ...
2
votes
1answer
2k views

Does MS Access have anything equivalent to triggers?

I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient ...
2
votes
3answers
218 views

Does this database table violate normalization rules?

I’m working with Access 2010. I’m developing a database for a public art program – we paint large scale murals on walls. The database tracks blank walls in the city as potential sites for murals. It ...
1
vote
1answer
273 views

Microsoft Access 2003 vs SQL Server 2005 Express

There is an Access 2003 database that each "corps" has to manage themselves containing personal, training and material data. It's written very poorly. Besides that, the server only runs Windows 2000, ...
0
votes
1answer
269 views

access 2003 to sql server 2008

I search a way to export an access 2003 database to sql server express 2008. the export is on la (other computer than the server). the computer have office 2003. the computer doesn't have admin ...
0
votes
1answer
498 views

check if linked server table column exists

I have several access databases that I'm copying over to the sql server, connecting to as a linked server, and then pulling data from each night. I need to be able to identify if the current access ...
5
votes
3answers
521 views

Querying special characters

I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the ...
2
votes
3answers
303 views

Can I limit access to postgresql 8.4 databases via ODBC on the server side?

I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-) We have a postgresql 8.4 database ...
6
votes
3answers
206 views

What would I look for in SQL Server to determine if a user connected with Microsoft Access?

Is there a way I can check SQL Server to see who is accessing it using Microsoft Access (as opposed to a website)? I'm using SQL Server 2008R2 with the default logging settings.
6
votes
3answers
928 views

Query best possible matches and order them

I'm trying to write a query along these lines: select * from tbl where col1 = 1 and col2 = 2 and col3 = 3 order by ... ; I want first all the results where all 3 WHERE ...
1
vote
2answers
674 views

Access exporting to CSV is weirdly slow

I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is ...
3
votes
1answer
447 views

Is it possible to change the Access data source to SQL Server?

I'm currently managing a "db" in Access (meh). A complex structure of masks and VBA scripts was built over this "db". What I'd like to do is change the Access datasource from Access itself to SQL ...
2
votes
1answer
278 views

Weird error on simple query in MsAccess

Any idea what's wrong with the query SELECT DISTINCT Table1.Description FROM Table1 ORDER BY Table1.Description DESC; I'm running this on MsAccess on a table where all fields are Memo. There are ...
-6
votes
1answer
850 views

How do I convert this Access query for SQL Server? [closed]

I have a Query used in Access which does not work in SQL Server as it stands: SELECT mstPatient.PatientID, mstPatient.PatientID AS PKNO, ...
1
vote
1answer
352 views

Ms Access queries equivalent in Sql Server

Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do SELECT * from allProducts; My ...