I need to run an UPDATE query on a large MDB file (~30mb), is it possible? how?
+1
A:
As Remou said, use the Query Designer.
Once you are in there you can drag and drop as you like to.
If you really want to type in the SQL directly, you can. You can switch from Design view to SQL view and manually type it in.
The SQL is pretty standard on MS Access. There are some things on other DBs that you would think are part of the standard, but aren't and having been implemented on MS Access' side. On MS Access the wild card character might be different than you expect. The default is "*" (ANSI-89) for .mdb's and .accdb's, but that could also be "%" is your are working with ANSI-92 SQL (used by .adp's - Access Data Projects). As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases — .mdb and .accdb files. You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases.
You might also be used to nesting queries in MySQL. Some of them you can't do the same way in MS Access - you have do some funny workarounds to get the same effect. Other wise it's pretty close. This may also have something to do with the differences between ANSI-89 SQL and ANSI-92 SQL (and beyond).
CodeSlave
2010-08-16 14:10:01
It is not always asterisk, it can be percent, depending on what version of SQL you have set the database to use. There is no problem in creating nested queries, you can often view them in design view, once you have set them up in SQL view.
Remou
2010-08-16 14:17:53
You're right... MS seems to have changed the wildcard for .adp's to % (vs. .mdb's - thanks a #$%@#$%#$ lot, one more reason not to upgrade anything to the new version). Upon further testing I can do `select * from (select * from bar);` which didn't used to work (hooray), but I still can't do `SELECT bar.* FROM bar where f1 in (select b1 from foo);` which works on other DBs.
CodeSlave
2010-08-16 15:09:28
Answer adjusted based on feedback.
CodeSlave
2010-08-16 15:11:42
You've confused the wildcard issue. It's not "* for mdb". Any operation using ANSI-92 mode with an MDB requires % and _ instead of * and ?. See http://office.microsoft.com/en-us/access-help/access-wildcard-character-reference-HA010076601.aspx
HansUp
2010-08-16 16:04:21
Son of a B****. Seems they fixed that in 2002. Well, the default still seems to be ANSI-89 when working in an .mdb's though - at least 2003, can't test 2007+ here. I'll adjust my answer.
CodeSlave
2010-08-16 16:47:15
Better. Still ADO operations with any Access db format require ANSI-92 wildcards ... doesn't matter if it's an MDB and whether or not the database properties have been set to ANSI 89 or 92 ("SQL Server Compatible Syntax"). That holds true for A2007 as well. Confusion about ADO and wildcards seems to bite a lot of folks. And please be careful what you imply about my mother! :-)
HansUp
2010-08-16 18:30:18
The reason ADPs use % and _ is because they are using ADO to interact with SQL Server, and through ADO you can use only "SQL 92" mode no matter what the target database engine is. That is, using ADO, even MDB/ACCDB will use % and _. Likewise, DAO can use only "SQL 89" mode, so no matter what the back end, if you're using DAO, you use * and ? for wildcards. There is also an effect on derived tables, with 92 using the standard (SELECT...) As DerivedTable and 9 using the idiosyncratic [SELECT ...]. As DerivedTable.
David-W-Fenton
2010-08-16 20:12:20
@HandsUp Just a figure of speech, I'm not impugning the reputation of your mother in any way :-) ____ I don't doubt either of you on ADO/DAO. I will take the position; however, that the OP is just going to pop open his existing MDB and dealing with it in the query builder. In that case (assuming the defaults are still set on the DB) then the ANSI-89 wildcards will apply. If they're approaching this update in code and using ADO then it will work more like the OP is used to. I'm also assuming that if this is the OPs first exposure to Access (I think it is) then they will not be using ADO.
CodeSlave
2010-08-16 20:54:27
I dunno,CodeSlave. Seems like too many assumptions considering the OP never said he intends to update the database from *within* Access. But you may well be right. Meanwhile, I clarified the situation for Mom so she can stop cursing you! :-)
HansUp
2010-08-17 16:35:35
Seems to me that if somebody asks as SQL question about Access, assuming native Access wildcards (i.e., SQL 89) makes perfect sense. It's only if the user's options have been changed to use SQL 92 that this wouldn't work. If they were using ADO, that should be clear from the question. Given that nothing is specified here, I'd go with the default Access assumption and provide a SQL 89 answer.
David-W-Fenton
2010-08-17 22:35:24