Tag Info

Hot answers tagged

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

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 ...


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

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: ...


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 ...


5

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery. If I edit the linked Access table will it be reflected in the SQL server table? Yes; as before, these updates will be visible on the other end on next refresh or requery. Trusted Connection only controls how the connection ...


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

I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.


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

First you will have to use query language. All the flavors of SQL use the almost same SQL query syntax. So you will first have to learn SQL query language. For eg. you can start with tutorial at www.w3schools.com/sql/default.asp Stanford University had offered public course on databases last Fall. They had nice tutorial on how to set up SQLite. Once you ...


4

To address your concerns about BIT: You can set your BIT column to NOT NULL. You can use -1 when setting a BIT column to "true". You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language). CREATE TABLE dbo.foo(bar BIT ...


4

All you have to do is the following: Across the Top of the Access Window, Click the File Tab Click the Compact and Repair Button I would not worry about growth because data insertion into MDB usually appends changes to back of the file. Garbage collection and space reusage is not a strong suit for MSAccess. To make matters worse, there is filesize limit ...


4

MS Access grows just from using it - running queries, etc. That is just the nature of it. I would recommend including a compact and repair process in your daily processing. Access removes the existing file and replaces it with the compacted version, thus shrinking the database. If you are worried about the size, the consider creating a daily process ...


4

MS-Access is rather picky at how it wants the joins to be formed. Add parenthesis: SELECT A.Name, B.Quantity, C.Quantity FROM ( A LEFT OUTER JOIN B ON A.ID = B.ID ) LEFT OUTER JOIN C ON B.No = C.No ;


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 ...


4

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are: Data Integrity Query Flexibility The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each ...



Only top voted, non community-wiki answers of a minimum length are eligible