For the Street Number
For the Street
For the Street Number
For the Street
Useful function if you are taking two tables with overlapping identity key to be placed in a table that will have a further child record and you wish to separate the new keys.
Public Function WriteUID(LCounter As Long) As Long Dim rstC As DAO.Recordset Dim LCountStart As Double LCountStart = LCounter Set rstC = CurrentDb.OpenRecordset("TABLEREQUIRINGUNIQUEID") Do Until rstC.EOF = True rstC.Edit rstC!UID = LCounter rstC.Update LCounter = LCounter + 1 rstC.MoveNext Loop MsgBox "Finished UNIQUEID write" End Function
After writing Update queries with multiple joins on linked Oracle Databases I was receiving an error on running particular update queries on linked tables that had no Primary Key identified.
On linking the tables I had been asked to identify a field with a unique value. Not thinking I had passed on this and initially this didn’t seem to be an issue. Subsequently I identified that the error associated with the update queries originated from this lack of key identification. Re-linking required tables and ensuring that I identified a unique key allowed for these queries to be processed. This of course is because Access is wanting to use the unique key to identify the field for update.
Operation must use an updateable query.
Just one to be mindful of if you are linking to enterprise grade backend databases.
Continuing the theme of tools that assist the use of MS Access as a platform for transferring data between systems. Here is a small function that will allow you to write the pure SQL syntax of all queries in a database to a table. I personally used this in a system transfer project. The business had given us something called a field mapping plan that identified the table and fields in one system and where they were to be migrated in the other system. Having written the queries I then wanted to go back through and reconcile the original mapping to the SQL to ensure that absolutely every field had been taken across. Writing the SQL into a table allows for table and field combinations to be methodically searched. Quite useful.
Create a table called T001SQLCollection with at least 2 fields – QueryName and SQL. This is where the recordset writes the SQL to.
This is very much a reverse of the previous post function.
Public Function ListQueries() Dim rstList As DAO.Recordset Dim i As Integer For i = 0 To CurrentDb.QueryDefs.Count - 1 Set rstList = CurrentDb.OpenRecordset("T001SQLCollection") With rstList .AddNew rstList!QueryName = CurrentDb.QueryDefs(i).Name rstList!SQL = CurrentDb.QueryDefs(i).SQL rstList.Update End With Next i rstList.Close MsgBox "Finished" End Function
This completes the task of taking automatically generated SQL previously placed in a table and writes the SQL therein to Query Objects naming them automatically. This has several advantages to cut and paste –
1) Its Lightning Quick
2) Completely consistent naming
3) Cut and Paste can be awkward with the windows
4) Its just fun
To created the NestedIIfs table see this post
This is the post on writing Query Objects directly
It requires that you have a table called T005NestedIIFs
with the populated fields
Public Function WriteNIFQueryObjects(LCounter As Long) As String Dim rstX As DAO.Recordset Dim QName As String Dim qdf As Variant Dim strSQL As String Dim LCountStart As Long LCountStart = LCounter Set rstX = CurrentDb.OpenRecordset("T005NestedIIFs") Do Until rstX.EOF = True qdf = rstX!SQLField QName = "Q" & LCounter & rstX!TargTable & "-" & rstX!Targfield & "-Update" LCounter = LCounter + 1 Set qdf = CurrentDb.CreateQueryDef(QName, rstX!SQLField) rstX.MoveNext Loop MsgBox "Query objects written to Database numbers starting" & LCountStart End Function
In line with my general theme of automation here’s the framework of a tiny but potentially very useful function to create queries in the current database, linking this in with the loop queries and say something like the nested IF generation function could allow you to take your table of nested queries and write them to the database.
Public Function CreateQuery() Dim strSQL As String Dim qdf As Variant strSQL = "SELECT * FROM T01Contacts" Set qdf = CurrentDb.CreateQueryDef("GeneratedQuery", strSQL) MsgBox "GeneratedQuery Created!" End Function
So in my previous post I had looked at generating single SQL Nested IF statements using MS Access but had highlighted that Access will error out indicating the SQL is too complex should there be more than 13 nested IIFs in a single SQL.
What happens if you have 20 values that you require to be changed.
Answer = you pick 13 and produce one sql statement with 13 nested if statements and another with 7 nested IIF statements.
But damn it,doesn’t that introduces yet another manual step to what was supposed to be automation. Yes it does this is why I wrote this function which allows you to define the number of nestings and will go off and write the appropriate number. And yes I hear you DBAs saying a better way would be to link in the conversion table at the beginning – absolutely but there may be occasions where you can’t link to the required database.
The following iterates through a conversion table and writes the resulting SQL into a table named T005NestedIIFs – it should be noted that the Wend statement is artificially optomised here and if you have an extremely large code conversion table you may need to work on this code to make it dynamically alter the number of WEND statements depending on the BatchFileNo you wish to create and its relation to the number of records in the conversion table. The below code goes to the last record as part of the recordset count and when at 0 will jump to exit so for smaller code conversions its semi-dynamic but for larger files the limit of values converted with be the BatchFileSize * 200 which may or may not be big enough for purpose.
To have this working you will need two tables and knowledge of a third.
This should have 5 fields
The flag fields only require a single integer value. They are used to record how far through the conversion table you have reached so that when you break from your SQL query you resume at the finish of your former position.
The queries are placed in table
In which I have created 3 fields
One marked SQLfield
Public Function CreateTableofSQL(TargetTable As Variant, TargetFieldforUpdate As Variant, BatchSizeNo As Long) On Error GoTo Err_Writetofile Dim rst As DAO.Recordset Dim rst2 As DAO.Recordset Dim rst3 As DAO.Recordset Dim rst4 As DAO.Recordset Dim RecordCount1 As Long Dim RecordCount2 As Long Dim LCounter As Integer Dim SQLString1 As String LCounter = 1 While LCounter < 200 LCounter = LCounter + 1 SQLString1 = "" 'Three recordsets were testing differing flag fields because order of the integrity of the recordsets were being affected by each other Set rst2 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag2 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag2)<>1));") Set rst3 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag3 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag3)<>1));") Set rst = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag1 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag1)<>1));") 'MoveLast required to ensure correct record count at first pass reset to first after this rst.MoveLast RecordCount1 = rst.RecordCount rst.MoveFirst rst3.MoveLast RecordCount2 = rst3.RecordCount rst3.MoveFirst SQLString1 = SQLString1 & "UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=" Do Until rst.EOF = True rst.Edit rst!xFlag1 = 1 rst.Update RecordCount1 = RecordCount1 - 1 SQLString1 = SQLString1 & "IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'" If RecordCount1 = 0 Then SQLString1 = SQLString1 & " " Else SQLString1 = SQLString1 & "," End If rst.MoveNext Loop rst.Close Do Until rst2.EOF = True SQLString1 = SQLString1 & ")" rst2.Edit rst2!xFlag2 = 1 rst2.Update rst2.MoveNext Loop SQLString1 = SQLString1 & " WHERE ((" rst2.Close Do Until rst3.EOF = True RecordCount2 = RecordCount2 - 1 SQLString1 = SQLString1 & "(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'" rst3.Edit rst3!xFlag3 = 1 rst3.Update If RecordCount2 = 0 Then SQLString1 = SQLString1 & " " Else SQLString1 = SQLString1 & " OR " End If rst3.MoveNext Loop rst3.Close SQLString1 = SQLString1 & "));" Set rst4 = CurrentDb.OpenRecordset("T005NestedIIFs") With rst4 .AddNew rst4!SQLfield = SQLString1 rst4!Targfield = TargetFieldforUpdate rst4!Targtable = TargetTable rst4.Update rst4.Close End With Wend Exit_WritetoFileError: Exit Function Err_Writetofile: If Err.Number = 3021 Then MsgBox "All records in Translation table written out no more to translate" Else MsgBox Err.Description End If Resume Exit_WritetoFileError End Function
I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought it was obvious that this would be better accomplished joining the conversion table in the query editor and moving on from there, particularly because MS Access has a limit on the number nested IIFs allowed in a single statement. Nonetheless I publish it here as it may prove useful.
Here I create a table T001CodeConversionTable that holds the translation from one set of codes to another. This field also has to have several fields in it named
The variables TargetTable and TargetFieldforUpdate exist in the table that will have the resultant SQL performed on it.
Public Function CreateNestedIF(TargetTable As Variant, TargetFieldforUpdate As Variant) Dim rst As DAO.Recordset Dim rst2 As DAO.Recordset Dim rst3 As DAO.Recordset Dim RecordCount1 As Long Dim RecordCount2 As Long Set rst = CurrentDb.OpenRecordset("T001CodeConversionTable") Set rst2 = CurrentDb.OpenRecordset("T001CodeConversionTable") Set rst3 = CurrentDb.OpenRecordset("T001CodeConversionTable") RecordCount1 = rst.RecordCount RecordCount2 = rst3.RecordCount Dim fs, TextFile Set fs = CreateObject("Scripting.FileSystemObject") Set TextFile = fs.CreateTextFile("C:\Users\Mark\Documents\NestedIFs.txt", True) TextFile.WriteLine ("UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=") Do Until rst.EOF = True RecordCount1 = RecordCount1 - 1 TextFile.WriteLine ("IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'") If RecordCount1 = 0 Then TextFile.WriteLine ("") Else TextFile.WriteLine (",") End If rst.MoveNext Loop rst.Close Do Until rst2.EOF = True TextFile.WriteLine (")") rst2.MoveNext Loop TextFile.WriteLine ("WHERE ((") rst2.Close Do Until rst3.EOF = True RecordCount2 = RecordCount2 - 1 TextFile.WriteLine ("(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'") If RecordCount2 = 0 Then TextFile.WriteLine ("") Else TextFile.WriteLine ("OR") End If rst3.MoveNext Loop rst3.Close TextFile.WriteLine ("));") TextFile.Close MsgBox "Created NestedIFs File in C drive" End Function
So you would like to construct simple applications that you can at the moment create in MS Access but you want to do it on the web. By that I mean you would like to create a data driven application with somewhat complicated forms that can be accessed by anyone through either IE or Chrome anywhere in the world with a simple login screen at the front to prevent simply anyone accessing the applications collecting the information into a database. What are your options for programs that will assist you in a MS Access like environment rather than going the full IDE deep dive – Visual Studio route – for what I consider to be a reasonable fee?
From my experience the unicorn of access on the web is slowly coming to fruition BUT for the vast majority of people with a budget similar to that for MS Access – lets say £200 ($250) a year for unlimited applications there is simply nothing which is quite as easy and powerful as MS Access. Some are pretty close but simply not as stable and require typically several magnitudes greater amount of configuration. WYSIWYG design isn’t quite as WYSIWYG and stability is a few orders lower than the desktop.
What you are probably looking at can typically be described as either RAD tools for the Web, a Low Coding Platform or something called a Code Generator any of those phrases can be useful for Google.
Assuming you don’t have your own servers whatever you do you will need to spend money on a web host.
The minimum this is likely to cost you is in the region of $15 a month. If you don’t want to spend the next 6 months learning about the insides and outsides of frameworks then I would suggest you go to one of the below three providers who all provide complete environments set up and ready to go with their particular generators pre-installed. This is good value it is extremely difficult to beat these guys on cloud hosting costs and unless you are very advanced and have very particular requirements its a waste of time to try. All three of the below providers will allow you to create limitless number of applications albeit you are limited by the space you hire on your web server. Similarly distribution will be limited by the quality of web server you sign up for. In all likelihood if you have few users it is unlikely that the coding front ends of your applications will be a limit to the number you create more likely the size of databases you are attaching them to and the shear time you have available to create applications.
For a period I was paying a monthly amount for a Nubuilder Pro hosted platform. This performed well and I could create an unlimited number of applications. As it was so hosted I skipped the step of learning some of the deeper parts of the initial configuration. I hope at some point to go back to this. It is open source and seems well maintained with a very dedicated developer. The developer re-wrote much of it and at March 2019 it latest re-incarnation is Nubuilder Forte.
Be warned n-tier web applications do not play as friendly as the desktop you WILL be slower to construct applications than you are on the desktop, getting into it WILL take time and a bit of commitment, you WILL have far less flexibility regards coding, there WILL be less people about to ask questions and there is far far less WYSIWYG design capabilities, error trapping is poor and errors are far more likely to be fatal and the really big warning is that on release of new web frameworks you may not necessarily be able to update without a full site re-design (A fact that comes as a nasty surprise to many CIOs and Project Managers when they realise that they are locked into front end system replacements every 4 or 5 years ) Know how to get data to your local environment out of the back end and accept that the front end is ephemeral and not likely to last in the same way as your desktop applications. (Your database will last but don’t expect to be running it through the same front end ten years from now). Accept that you will now have monthly or annual rental fees for cloud provision.
That said the design of these items is significantly faster than its ever been.
Scriptcase and ASP Runner dot net (Xlinesoft also produces a PHP equivalent generator) have free downloads that are good for getting started.
Commit to one and go for it. – I’ve got both PHP and ASP.NET solutions.. Nubuilder only connects to MySQL whereas Scriptcase and ASPRunner.NET connect to pretty much any database. I started with Nubuilder and am using ASPRunner.net as well because importantly it connects to SQL Server and it was easy to get up and running in MS Azure. Scriptcase is php based and I believe the applications it build require some kind of security program to sit on the web server this put me off – they do however have hosting that you can sign up for which is pre-configured. Their IDE is web based which could be a winning advantage. One of the great advantages of ASP runner dot net is that the program produces an open web application that should run on all stock servers. I found Nubuilder Pro (now Nubuilder Forte) really conceptually elegant which despite its rather drab looks is incredibly flexible the applications it produces are however limited to MySQL and non responsive (But being non responsive you get get more detailed forms!). I would probably be able to change it’s look if I was prepared to get my own server and install everything on it myself. That is not something I have time to do at present.
Nubuilder hosts its IDE in the browser which again is an advantage. ASPRunner.net is more traditional in that you have a program running on a desktop that creates the plumbing of your application which you then need to push to a server for publication this has the advantage that you get to see the plumbing in the background which makes backup of the site easier but publishing slightly harder.
You may have heard of other generators / design applications out there for example – Zoho Creator / Alpha 5 / Outsystems these hold your hand even more but as a result are even more proprietary and won’t fit in that budget of £200 per year ( by quite a long way!)
Some further information on costs – nubuilder being open source in theory could scale for very little money espectially if you have your own servers already. Scriptcase and Xlinesoft ASP Runner product have an initial fee followed by annual subscription – you may be able to configure it so that you can create unlimited applications for that one fee (if you have good access to web servers ) but it is likely that initially there will be some kind of variable cost per additional application you wish to build. I am using MS Azure with ASP Runner dot net and a developer database costs me about £5 a month with each application being hosted in a web application service which again costs £5. With both Scriptcase and ASP Runner if you stop paying the annual fees your applications will continue to work you will just not get version upgrades. You will be able to step back into version upgrades but you may need to restart your subscription with an additional fee.
Although MS Access had been upgraded with each new version of Office. The desktop side of the product had appeared to have lacked development and sometimes suffered from some strange marketing decisions. In particular its omission in 2014 from some of the Office 365 subscriptions.
On Friday the 4th of November Microsoft announced that it would be included in the Office 365 Business and Business Premium subscriptions.
Not only that but it would appear that Microsoft seem to be actively embracing a more visible development strategy for the product. To be fair for many years they have had regular summits at headquarters with super users to allow them to give feedback on product.
They now have a suggestion box that gives some indication of how they are collecting information on what developments they should be working on and are working on in the future
MS Access Suggestion Box
I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages?
Functions return a value that is stored whereas subs don’t. The main difference is not only the return value, it seems that subs are faster than functions (at least in .net) because the MSIL code of subs is much shorter when no value is returned. so overall subs are faster when no value is returned.
MSIL stands for Microsoft Intermediate Language – which is the a programming language that has been standardized later as the Common Intermediate Language
Function TypicalWhileLoop() 'This performs the same as next loop but uses the while loop Dim LCounter As Integer LCounter = 1 While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function
Function TypicalForNextExample() 'This performs the same as the while loop but uses for next Dim i As Integer For i = 1 To 9 MsgBox (i) Next i End Function
Function TypicalDAOrecordset() 'Make sure the name of the recordset is unambiguous 'Good practice to reference the actual library Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM T001Main where T001Main.ValueNumber = 0") 'the data source can be a Table Name a query name or an sql string 'it would be possible to change the SQL to set to another set of records 'Check to see if there are any records in the set If Not (rs.EOF And rs.BOF) Then 'there are no records if End of File and beginning of file are both true rs.MoveFirst Do Until rs.EOF = True rs.Edit rs!ValueNumber = 300 rs.Update rs.MoveNext Loop Else MsgBox "No Records available for updating exit sub" Exit Function End If MsgBox "Looped through the records and updated ValueNumber field" rs.Close Set rs = Nothing Set db = Nothing 'libraries for DAO can be found on AllenBrowne site 'remember to break an infinite loop press ctrl + break End Function
Not quite finished yet but place here for later correction.
Public Function CountAllTablesRows() Dim rs As New ADODB.Recordset Dim rsRC As New ADODB.Recordset Dim strTbName As String Dim lngRowCount As Long Dim tbl As TableDef CurrentProject.Connection.Execute "Delete from TABLE_INFO" rs.Open "TABLE_INFO", CurrentProject.Connection, adOpenDynamic, adLockOptimistic For Each tbl In CurrentDb.TableDefs Select Case Left(tbl.Name, 4) Case "mSys" Case Else rs.AddNew rsRC.Open "Select count(*) as The_Count from [" & tbl.Name & "]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly rs.Fields("TBL_NAME") = tbl.Name rs.Fields("TBL_ROWCOUNT") = rsRC.Fields("The_Count") rs.Update rsRC.Close Set rsRC = Nothing 'Debug.Print tbl.Name End Select Next rs.Close Set rs = Nothing MsgBox "Counted Numbers in Table" End Function
Writing to Microsoft Word
Firstly a warning – this creates doc documents that can be opened in Word 2010 but are strictly speaking 03 iterations hence the doc suffix
First need to load in the library for Microsoft Word (this is 2003 version)
Then you are free to open and manipulate the items in Microsoft word..
Private Sub Command_Click() On Error GoTo Err_Command_Click Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Set wrdApp = CreateObject("Word.Application") Set wrdDoc = wrdApp.Documents.Add ' create a new document wrdApp.Visible = True ‘this line can be altered to not open the document on the screen With wrdDoc With .Styles(wdStyleHeading1).Font .Name = "Arial" .Size = 16 .Bold = True .Color = wdColorBlack End With With .Styles(wdStyleHeading2).Font .Name = "Arial" .Size = 12 .Bold = True .Color = wdColorBlack End With With .Styles(wdStyleNormal).Font .Name = "Arial" .Size = 10 .Color = wdColorBlack End With .Content.ParagraphFormat.LineSpacingRule = wdLineSpaceExactly .Content.ParagraphFormat.LineSpacing = 10 .Range(0).Style = .Styles(wdStyleHeading1) .Content.InsertAfter "ThIS SHOULD BE HEADING1" .Content.InsertParagraphAfter .Range(.Characters.Count - 1).Style = .Styles(wdStyleHeading2) .Content.InsertAfter "THIS SHOULD BE HEADING2" .Content.InsertParagraphAfter .Range(.Characters.Count - 1).Style = .Styles(wdStyleNormal) .Content.InsertAfter "THIS SHOULD BE NORMAL" .Content.InsertParagraphAfter .SaveAs ("C:\CreatedWordDoc.doc") .Close ' close the document End With ' With wrdDoc wrdApp.Quit ' close the Word application Set wrdDoc = Nothing Set wrdApp = Nothing Exit_Command_Click: Exit Sub Err_Command_Click: MsgBox Err.Description Resume Exit_Command5_Click
An article on libraries specifically related to MS Access is available here
Here’s some code I used to generate HTML for a web configuration file. It takes a database (the current open one) then looks to a query called QueryTargetInformation and places the fields – PlaceName / EastingMn / NorthingMn / EastingMx / NorthingMx in a HTML Structure and creates a file called CodeGeneratedHTML.txt place it on the C drive.
I put around 1,000 repeated links in HTML configuration file using this.
This was for a web mapping application – the eastings and northings were obtained from Ordnance Survey Open Source shape files from Ordnance Survey and then QGIS to get the eastings and northings of a variety of locations. These were transferred into the relevant columns of a database and this code triggered from the onclick event of a form command.
Private Sub Command_Click() On Error GoTo Err_Command_Click Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("QueryTargetInformation") Dim fs, TextFile Set fs = CreateObject("Scripting.FileSystemObject") Set TextFile = fs.CreateTextFile("c:\CodeGeneratedHTML.txt", True) Do Until rst.EOF = True TextFile.WriteLine ("<bookmark name=" & Chr$(34) & rst!PlaceName & Chr$(34) & ">") TextFile.WriteLine (" <min>") TextFile.WriteLine (" <x>" & rst!EastingMn & "</x>") TextFile.WriteLine (" <y>" & rst!NorthingMn & "</y>") TextFile.WriteLine (" </min>") TextFile.WriteLine (" <max>") TextFile.WriteLine (" <x>" & rst!EastingMx & "</x>") TextFile.WriteLine (" <y>" & rst!NorthingMx & "</y>") TextFile.WriteLine (" </max>") TextFile.WriteLine ("</bookmark>") rst.MoveNext Loop TextFile.Close MsgBox "Created CodeGeneratedHTML File in C drive" Exit_Command_Click: Exit Sub Err_Command_Click: MsgBox Err.Description Resume Exit_Command_Click End Sub
Good article on Early and Late Binding
Coding can appear very complicated sometimes but to simplify the difference in terms of implementation this line is very clear;
The only difference between early binding and late binding (in terms of the code you write) is in the variable declaration.
Or it could be an excuse to post a really nice picture of some bindings.
The following uses a function and the before update event of a form.
Sometimes it can be useful to have some kind of order field in the child records to indicate the order or version numbers of items. Although an incrementing Primary Key can be used child records may be in the thousands and if related to the parent you may want a simple almost ranking within the group. Which may be more meaningful when viewed filtered according to the parent.
A particular case may be where you are storing documents which have some kind of version.
Public Function GetChildCount(OrderNo as Integer) As Integer Dim intCount as Integer intCount = DCount("FKID","[ParentTable]","[FKID]=" & OrderNo) GetChildCount = IntCount + 1 End Function
This counts the number of records with the same FKID in the table called ParentTable with a FKID equal to OrderNo
Then within the before update event of the Sub_Form
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.Order = GetChildCount([SiteID]) End If End Sub
The If statement just ensures that when you edit a record the order is not updated to the count of the child records if a count already exists in the field Order.