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
I decided to add a condition to the Where Clause that always evaluates true but allows the coder to find your comment. Select … From … Where …. And “Comment: FYI, Access doesn’t support normal comments!”<>”” The last line always evaluates to true so it doesn’t affect the data returned but allows you to leave … Read more
It is possible to do a little with VBA. For example, here is a start on creating script for a database with local tables. Dim db As Database Dim tdf As TableDef Dim fld As DAO.Field Dim ndx As DAO.Index Dim strSQL As String Dim strFlds As String Dim strCn As String Dim fs, f … Read more
I would check the “End of File” flag: If temp_rst1.EOF Or temp_rst2.EOF Then MsgBox “null”
Access is not a DBMS. Or at least it’s not just a simple DBMS. It’s a very good RAD environment, a simple way to create SQL code graphically, and a regular front-end to fully fledged DBMs. Neither SQL Server (Express or MSDE) nor Oracle, MySQL, etc. will ever replace it, until they come integrated with … Read more
Use Not IsNull(Fields!W_O_Count.Value) So: =IIF(Fields!approved.Value = “N” & Not IsNull(Fields!W_O_Count.Value)), “Red”, “Transparent”)
There is no error in the code, but the error is thrown due to the following: – Please check whether you have given Read-write permission to MS-Access database file. – The Database file where it is stored (say in Folder1) is read-only..? suppose you are stored the database (MS-Access file) in read only folder, while … Read more
You can easily write a bit code that will read in a file. You can either assume one sql statement per line, or assume the ; So, assuming you have a text file such as: insert into tblTest (t1) values (‘2000’); update tbltest set t1 = ‘2222’ where id = 5; insert into tblTest (t1,t2,t3) … Read more
To build on Ilya’s answer try the following query: SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>”~”) AND ((Left([Name],4))<>”MSys”) AND ((MSysObjects.Type) In (1,4,6))) order by MSysObjects.Name (this one works without modification with an MDB) ACCDB users may need to do something like this SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>”~”) AND ((Left([Name],4))<>”MSys”) AND ((MSysObjects.Type) … Read more
There’s nothing to get the current function name, but you can build a fairly lightweight tracing system using the fact that VBA object lifetimes are deterministic. For example, you can have a class called ‘Tracer’ with this code: Private proc_ As String Public Sub init(proc As String) proc_ = proc End Sub Private Sub Class_Terminate() … Read more