ms-access

Get Name of Current VBA Function

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

ODBC Call Failed with stored procedure – Pass through query

To get more information about the cause of an “ODBC–call failed.” error we can loop through the DBEngine.Errors collection and see if there are other messages that might be a bit more descriptive. For example, with the code qdf.Connect = strConnectionString qdf.SQL = ” EXEC [dbo].[SAMPLE_TEST]” qdf.ReturnsRecords = True On Error GoTo oops Set rst … Read more

Trouble with InputBoxes

Here is a way to catch most outcomes of interacting with the dialog; Dim value As String value = InputBox(“Please enter a #”, “Determine Limit”, 10000) If (StrPtr(value) = 0) Then MsgBox “You pressed cancel or [X]” ElseIf (value = “”) Then MsgBox “You did not enter anything” ElseIf (Val(value) = 0 And value <> … Read more

Access substitute for EXCEPT clause

In order to get rid of the EXCEPT you could combine the conditions and negate the second one: SELECT DISTINCT P.Name, T.Training FROM Prof AS P, Training_done AS TC, Trainings AS T WHERE ((P.Name Like ‘*’ & NameProf & ‘*’) AND (P.Primary_Area = T.Cod_Area)) AND NOT ((P.Name Like ‘*’ & NameProf & ‘*’) AND (P.Cod_Prof … Read more

Check if access table exists

You can use the hidden system table MSysObjects to check if a table exists: If Not IsNull(DlookUp(“Name”,”MSysObjects”,”Name=”TableName””)) Then ‘Table Exists However, I agree that it is a very bad idea to create a new table every day. EDIT: I should add that tables have a type 1, 4 or 6 and it is possible for … Read more

Do we have transactions in MS-Access?

Nobody has actually given you any code examples here in the answer or even cited an example (the Access help files do include examples, though). The key issue to keep in mind is that in Jet/ACE (Access does not support transactions itself — it depends on whatever database engine you’re using for that) that the … Read more