Microsoft Access is a relational database management system that is part of the Microsoft Office suite.
6
votes
4answers
3k 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 ...
6
votes
7answers
184 views
Main Considerations When Moving From MS Access Programming to SQL Server
First post, be gentle...
I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store ...
6
votes
3answers
853 views
How to log data changes in Access at the table level?
I support an Access database for a non-profit organization. We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are ...
6
votes
3answers
175 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.
5
votes
3answers
391 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 ...
5
votes
3answers
332 views
Online resource for writing Access SQL statements
I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, ...
5
votes
1answer
1k views
Is it possible to synchronize two ms access db?
We have a flower store where we use MS Access and a client program to work with the database. But this program can work only with one database.
We are going to open a new store, so now we have to ...
4
votes
5answers
1k 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 ...
4
votes
2answers
170 views
Concurrency question?
I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
4
votes
2answers
689 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 ...
4
votes
3answers
582 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 ...
4
votes
2answers
1k views
MS Access / MySQL: unlock locked Records
I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.
Over time many records are locked and editing is not possible anymore.
Is there a ...
4
votes
1answer
393 views
How to perform the dreaded double hop with linked servers?
The situation:
I have SERVER1, SERVER2, ACCESSDB on SERVER3.
I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB.
I want to be able to access the linked server ...
4
votes
1answer
1k views
Using a Parameter Query to generate Charts in MS Access Reports
I am using Microsoft Access 2007 and I have a query which looks something like this;
PARAMETERS Identifier TEXT(255);
SELECT
DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE ...
3
votes
2answers
146 views
COUNT (*) = 0 does not work?
Alright, I'm trying to make a query for a library that will show which students have never borrowed a book. For this, I have done the following:
SELECT
LEERLINGEN.LLNR,
LEERLINGEN.VOORNAAM,
...
3
votes
3answers
670 views
Database Performance Tuning
I have a MySQL backend, MS Aaccess front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. ...
3
votes
3answers
95 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, ...
3
votes
1answer
168 views
MS Access database in a network
I would like to ask if MS Access database supports working in networks & multi-user access?
3
votes
1answer
770 views
MS Access displays alle columns in SQL Server tables as “deleted”
I have created an ODBC connection to a SQL Server 2005.
When I link to the tables from MS Access, some tables have only "deleted" data.
This means the rows and columns are displayed, but all data ...
3
votes
1answer
314 views
Removing duplicates from a query with conditions in Access 2007
I have a table of transactions with 880,095 records that I am querying.
There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.
Eg 1.
...
3
votes
1answer
482 views
Ms-access ODBC calls for linked table behaviour
I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables.
The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise ...
3
votes
1answer
128 views
Normalizing a database without access to source data?
I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling ...
3
votes
1answer
101 views
I need help to migrate denormalized table content to normalized form- MS Access
I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships
However, for ease of collecting ...
3
votes
1answer
563 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 ...
3
votes
1answer
208 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 = ...
2
votes
5answers
324 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 ...
2
votes
2answers
1k 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 ...
2
votes
2answers
135 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 ...
2
votes
1answer
69 views
I get the error “You are attempting to execute a query in which the selected expression is no part of a statistical function.” in MS Access 2010?
Please note: my copy of Access is in Dutch, so any English parts are translated from Dutch.
I'm currently working on a database in Access using SQL, with the exercise being:
Create a query that will ...
2
votes
4answers
3k 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
74 views
Would using timestamps solve consistency issues with snapshot isolation?
I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm ...
2
votes
2answers
2k views
Database to track employee performance
This is going to be very basic as I'm a complete newbie when it comes to database design.
I've been tasked with setting up a way to track employee data over a number of months and potentially years. ...
2
votes
1answer
1k views
Joining 3 tables using SQL Query?
I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three ...
2
votes
1answer
1k 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
230 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 ...
2
votes
1answer
310 views
Is it possible to change the Access data source to SQL Server?
Sadly I found myself in an akward position.
I'm currently managing a "db" in Access (meh). A complex structure of mask and vba scripts was built over this "db".
What I'd like to do is change the ...
2
votes
1answer
233 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 ...
2
votes
2answers
661 views
Ms-Access 2007-2010 SQL - from single row record how to create multiple rows
I need help in below table updation using SQL in Ms-Access 2007-2010 database.
I've a table called table1 which has below entries:
table1:
--------------------------------------------------
| col1 ...
2
votes
2answers
224 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 ...
2
votes
1answer
217 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 ...
2
votes
3answers
173 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 ...
2
votes
2answers
233 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.
...
2
votes
0answers
52 views
How can we achive the replica of databases for SQL Server?
I have been working in VB6 to VB.NET migration project.In VB6 project they have individual databases (access db .mdb) for every user now we made that to centralized database using SQL Server.
In the ...
2
votes
0answers
435 views
Access 2010 linked table - solve the “decimal field's precision” error
I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables ...
2
votes
0answers
273 views
Parameters in queries using vba
I am trying to write a process to run several prewritten Queries in a row without any human interaction. I tried to write a macro, but the after saving it it still prompted for the date range in the ...
2
votes
0answers
174 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 ...
1
vote
2answers
83 views
Pull Access schema into a SQL Server table
We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no ...
1
vote
1answer
300 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 ...
1
vote
2answers
164 views
Good tool for making MySQL queries graphically?
After working a while with Access 2010 I have fell in love with the way you make queries. You just pick the tables you want in your SELECT query, then you get all the JOINs laid out automatically, and ...
1
vote
3answers
371 views
EXCEL sheets batch input to Access
I have a lot of Excel datasheet (in one file)
I want to batch input to Access
but I can't find a sample code to do it
the name of datasheet in access is same as in excel
is there any code of ...
