There is perfect logic to the Parentheses Rule in VB(A), and it goes like this. If a procedure (function or sub) is called with arguments, and the call is on a line with other statements or keywords, the arguments must be enclosed in parentheses. This to distinguish the arguments belonging to the procedure call from … Read more
In your example, because you use CurrentDB to execute your INSERT you’ve made it harder for yourself. Instead, this will work: Dim query As String Dim newRow As Long ‘ note change of data type Dim db As DAO.Database query = “INSERT INTO InvoiceNumbers (date) VALUES (” & NOW() & “);” Set db = CurrentDB … Read more
Use Access’s VBA function Replace(text, find, replacement): Dim result As String result = Replace(“Some sentence containing Avenue in it.”, “Avenue”, “Ave”)
I am able to use this feature on my machine using MS Access 2007. On the Ribbon, select External Data Select the “Text File” option This displays the Get External Data Wizard Specify the location of the file you wish to import Click OK. This displays the “Import Text Wizard” On the bottom of this … Read more
You should be able to do this with a pretty standard DAO recordset loop. You can see some examples at the following links: http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx http://www.granite.ab.ca/access/email/recordsetloop.htm My own standard loop looks something like this: Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(“SELECT * FROM Contacts”) ‘Check to see if the recordset actually contains rows If Not … Read more
Consider the Switch Function as an alternative to multiple IIf() expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT … CASE approach you referenced but which is not available in Access SQL. If you want … Read more
It’s mainly a matter of history, effectively ACE has superceded JET: Wikipedia answers your question in great detail. The most relevant sections are: With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine is fully … Read more
You can use the following code to open a file location from vba. Dim Foldername As String Foldername = “\\server\Instructions\” Shell “C:\WINDOWS\explorer.exe “”” & Foldername & “”, vbNormalFocus You can use this code for both windows shares and local drives. VbNormalFocus can be swapper for VbMaximizedFocus if you want a maximized view.
If obj Is Nothing Then ‘ need to initialize obj: ‘ Set obj = … Else ‘ obj already set / initialized. ‘ End If Or, if you prefer it the other way around: If Not obj Is Nothing Then ‘ obj already set / initialized. ‘ Else ‘ need to initialize obj: ‘ Set … Read more
We wrote our own script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules. Here it is, it should give you some pointers. (Beware: some of the messages are in german, but you can easily change that.) EDIT: To summarize various comments below: Our Project assumes … Read more