Selecting 2 tables from 2 different databases (ACCESS)

You can use IN: SELECT t1.*, t2.* FROM T1 INNER JOIN (SELECT * FROM atable IN ‘C:\Docs\DB2.mdb’) t2 ON t1.ID=t2.ID EDIT: sc = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\docs\other.mdb” cn.open sc s=”SELECT * FROM t1 INNER JOIN ” _ & “[MS Access;PWD=databasePWD;DATABASE=C:\docs\db.mdb].t2 ON t1.ID=t2.ID” rs.Open s, cn EDIT 2: You can use the aliases to identify which database a … Read more

How to write Case Sensitive Query for MS Access?

You can use the StrComp() function with vbBinaryCompare for a case-sensitive comparison. Here is an example from the Immediate window to show how StrComp() works. See the Access help topic for more details. ? StrComp(“a”, “A”, vbBinaryCompare) 1 ? StrComp(“a”, “A”,vbTextCompare) 0 StrComp() returns 0 if the first two arguments evaluate as equal, 1 or … Read more

Save password for ODBC connection to MS SQL server from MS Access 2007

The best solution is obviously to use Windows security. If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until the program is closed: copy the connect string of one of your tables create a passthru queries “ptqConnect” and enter any fast SQL statement in … Read more

How to select top 10 in Access query?

select top 10 Name, Price from MyTable order by Price desc Updated: @Fionnuala pointed out that: “Access SQL selects matches, so it will select all items with the same highest prices, even if this includes more than 10 records. The work-around is to order by price and a unique field (column).” So, if you have … Read more