ms-access

Microsoft Access – Case Query

There is no CASE … WHEN in Access SQL. You can use the Switch Function instead. UPDATE HAI SET REGION = Switch( NUMREG Like ‘*1’, ‘BDG’, NUMREG Like ‘*2’, ‘JKT’, NUMREG Like ‘*3’, ‘KNG’ ); That query uses Access’ default (ANSI 89 mode) * instead of % wildcard character. If you want to use the … Read more

Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

You can concatenate the records with a User Defined Function (UDF). The code below can be pasted ‘as is’ into a standard module. The SQL for you example would be: SELECT tbl.A, Concatenate(“SELECT B FROM tbl WHERE A = ” & [A]) AS ConcA FROM tbl GROUP BY tbl.A This code is by DHookom, Access … Read more

How to use cross join in access?

I’m not sure about what do want to accomplish, but the syntax for a full cartesian product(cross join) is select * from table1, table2 If you don’t want to cross everything but only some columns, something like SELECT * FROM (select id from details) b, (select detail from details) c ; should work: id detail … Read more

Read and write to an access database using Javascript

First, make sure that ‘/\’ and ‘\’ (in connection string) is just a typo in SO. Second, here is a version of Delete command: function DeleteRecord() { var adoConn = new ActiveXObject(“ADODB.Connection”); var adoCmd = new ActiveXObject(“ADODB.Command”); adoConn.Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”\\dbName.mdb””); adoCmd.ActiveConnection = adoConn; adoCmd.CommandText = “Delete * From tblName Where FieldName=”Quentin””; adoCmd.Execute(); adoConn.Close(); } And, Edit … Read more

Is it possible to convert VBA to C#?

Automatic conversion isn’t possible at the moment, but doing it manually will also help improve your C# skills. There’s a Top 10 article here that takes you through the common differences: http://msdn.microsoft.com/en-us/library/aa164018%28office.10%29.aspx You may also find the following links useful: The MSDN page for developing Office solutions with C#: http://msdn.microsoft.com/en-us/library/ms228286.aspx The MSDN Visual C# application … Read more

What is causing my OLEDbException, IErrorInfo.GetDescription failed with E_FAIL(0x80004005)

I apparently was mistaken when I said the query did not contain any reserved words. The query I was using was selecting from another query in the Access Database. That other query had a reserved keyword that was causing the problem. BTW: The Access database engine runs in different modes, depending on whether it is … Read more