Hot answers tagged ms-access
7
Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:
alter database [...] set read_committed_snapshot on;
No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of ...
6
Charles,
You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system.
If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational ...
6
You need to use an outer join, otherwise you won't get those students back that did not borrow a book.
Then you need to count() on the students table, not "the group" (which is done when you use (*))
SELECT
LEERLINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM,
LEERLINGEN.KLAS,
COUNT(LEERLINGEN.LLNR) ...
5
A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you.
Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.
5
The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.
SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ...
5
I'll just summarize all my comments here as an answer.
You should read this:
http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts
The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since ...
5
You have a working query, but you are selecting:
FROM CF30, EC01, OC02, OM01U1, RS2101F
With no explicit joins and only one implicit join:
WHERE OM01U1.OM01015 = RS2101F.OUTNUM
This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:
FROM OM01U1
INNER JOIN RS2101F
ON OM01U1.OM01015 = ...
5
When you have an aggregate function, you need a GROUP BY statement. In your case, it would be
SELECT
LEERLINGEN.LLNR,
UITLENINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM,
SUM(UITLENINGEN.BOETE)
FROM
LEERLINGEN
INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE
...
5
After translation of table names i think that this will work:
SELECT
LEERLINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM,
LEERLINGEN.KLAS
FROM
LEERLINGEN
LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
WHERE
UITLENINGEN.LLNR IS NULL;
EDIT:
You are looking for LEERLINGEN ...
4
Here is Microsoft's page about Access SQL syntax:
http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx
and here is a tutorial about Access SQL syntax for Access 2000. A bit dated, but the concept is still there:
...
4
Note that e.g. SQL Server Express is free and could potentially be a drop-in replacement for Access. This would give you the auditing functionality that you need. I don't think the "audit table" approach is viable; it assumes that anyone who connects to the database does so via the "official" client that has that logic in. With triggers there's no way to get ...
4
In addition to LittleBobbyTables method, there's a third option, which involves making an _audit table for each regular table and writing a whole lotta logic to support them, so that when things get changed, you insert a record into those tables to do so. (you already know when you're saving/updating/deleting the records, yes?)
Granted, this is how "bigger ...
4
Unfortunately, Microsoft Access doesn't have triggers, or anything even resembling triggers. Off the top of my head, you have a couple of unappealing choices:
Create DateModified and UserModified columns, and update your code to include these values. The downside is that you'll only know the last person who updated a record, and won't have a history to ...
4
A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the
You'll want to log queries that aren't using indexes
Update
In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify ...
4
It looks like the linked SQL Server table does not have a unique index on in. MS Access uses the MS Jet DB engine that is designed around a keyset model. Actions like inserts, updates, etc, uses this keyset. This may be missing from your linked SQL Server table. Click here for more detailed info.
4
From a quick glance it looks like you need to:
Replace your IIF statements with CASE expressions
Change the colums referenced by ! i.e. mstPatient!Add1 becomes mstPatient.Add1
Change CHR(13) to CHAR(13)
How to convert IIF to CASE
From Access:
IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ')
+ 'My postfix string' AS MyIIFStatement
...
4
SELECT col1,
col2,
col3,
col4,
col5,
col6
FROM TableX
WHERE col1 = 1
OR col2 = 2
OR col3 = 3
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
(CASE WHEN col2 = 2 THEN 1 ELSE 0 END) +
(CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
col4, col5, col6
or, for MS-Access:
ORDER BY ...
4
You can either build your own monitoring tool or look to a 3rd party solution that can provide one for you. If you're interested in building your own, it depends on what version of SQL Server you're working with. If it's 2005, you can use the Blocked Process Report trace event. If you're running 2008 or above, I'd suggest using the equivalent extended event, ...
4
There is a lot to learn... either online or through books. Here are a few first links to get you started (very incomplete list, but should keep you busy for a while) :
Forums
Stack Exchange
Stack Overflow : you can navigate with the sql server related tags
Sql Server Central
Blogs
SimpleTalk / SQL
Sql Blog
Books
SQL Server Maintenance Plans ...
4
As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:
Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the ...
4
I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end.
Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits. Those waits are showing up from the various ...
3
If the collation of your database is set to accent insensitive, then this should work (for most accented characters).
For example in the collation Latin1_General_CI_AS, the CS means Case Insensitive, Accent Sensitive. You would want the collation to be Latin1_General_CI_AI which means Case Insensitive, Accent Insensitive
Be warned though, that not all ...
3
Yes.
When you "upsize", Access creates "linked tables".
The queries and forms on top of this don't know any difference
Some links:
http://stackoverflow.com/q/184406/27535 (SO)
http://support.microsoft.com/kb/2279654/en-us (MS KB)
Edit:
You'll need to deploy a new mdb/accdb to each client, along with a DSN (Control Panel, Admin tools, "Data Sources ...
3
Security Account Delegation
And for SQL Server 2000 but still valid too
Basically
Configure the server in AD
Set up the SPN
Use sp_addlinkedsrvlogin so useself = true
3
The sp_who2 stored procedure lists all active connections and includes the ProgramName.
You could also select from sysprocesses (joining in whatever other information you require such as sysusers) but it's just easier to use sp_who2.
(caveat, this will only work for applications which set the name, some applications may not)
3
You might use the system view sys.sysprocesses:
select sp.hostname, sp.program_name, sp.*
from sys.sysprocesses sp
where spid > 50 -- user processes
Or you can use the Activity Monitor (from Management Studio) -> Processes -> column Application Name.
3
I suggest you read up about database normalization. Here is a good starting point: Third normal form
Definitely do not create one table per employee as management of that database structure will soon become too cumbersome and your application will need to be modified every time a new employee is hired.
I do not know what your business rules are, but ...
3
The only way to get complete control over the query for ODBC is to create them as pass-through queries.
In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).
In ODBC mode, Access will decompose queries that are bound ...
3
This process will be a bit cumbersome for the DB. Not only do you want to know if the current record is being viewed, but you also want to notify the users of each other. When a user reads a record, the DB system quickly fetches the data and returns it to the user. As far as the DB is concerned, once the data is sent to the client, nobody is 'viewing' said ...
Only top voted, non community-wiki answers of a minimum length are eligible
