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
Category: All
VBS – Pieces of code
Shutdown computer
Option Explicit Dim oShell Set oShell = Wscript.CreateObject("Wscript.Shell") oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down
Trigger speech
Option Explicit Dim speechobject set speechobject=createobject("sapi.spvoice") speechobject.speak "Your system is setup and ready for your day"
Delay for Seconds (10)
Option Explicit Dim dteWait dteWait = DateAdd("s", 10, Now()) Do Until (Now() > dteWait) Loop
Sleep Function for Delay – Count in Milliseconds (5 mins below)
WScript.Sleep 5*60*1000
MS Access VBA Function – Count Numbers of Records in Tables and list.
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
Using VBA to write Word Document
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
allenbrowne.com
Using VBA and Databases to create HTML
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
The Economics of Immortality (part 2) and a real reason for moving platform.
The announcement in December 2014 that Microsoft was releasing a version of Visual Studio Community to virtually everyone for free and then again a further announcement in February 2016 of Microsoft’s purchase of Xamarin and subsequent release with VS community edition suggests to me that my theory that cost of development tools should steadily reduce to next to nothing is on the button. Still it comes as a surprise that it seems to be happening so quickly. This can only be a good thing especially when considering that increasingly, any application can be developed locally and sold globally or at least published internationally.
In my opinion it is looking more and more likely that the best reason for moving legacy systems to new platforms will in the future not be cost savings but rather revenue possibilities of resulting systems. In a word Software as a Service. But why will anyone one want to create their own Services rather than paying to be a customer of others? Two reasons – number one – at present the field is wide open and in many fields there is a complete lack of rich software out there applicable to individuals problem, secondly the caution with which some individuals will relinquish their information and knowledge of their tools – in that respect revenue generation might just be a nice optional bonus. I do think that different categories of software will progress at different rates and maybe in that respect we can look to Accounting software to see the future for other categories. Everyone is interested in money and the shear ubiquity of the requirement for accounting support has focused resources heavily on tools resulting in a healthy market of options for customers for online software. There are still sticking points in the form of cost especially when being charged on a per seat basis but it is no longer really practical for any individual organisation to develop their own accounting software especially when you can choose both on quality and price in the wider national markets. But outside of accounting the choice of products seem to be somewhat scarce.
So it seems its early days for Software as a Service but certainly maybe the question should be not what legacy systems need replacing but…
What tool do you have at your work which you think with a redesign could be used by others by publishing to the web?
Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7
This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.
Hit Window button
Type “Task Scheduler”
Press return you should get the following screen.
Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create
Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.
Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab
The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.
If we continue with the theme and run a vbs script to do a backup at 11:48 everyday
Early and Late Binding
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.
nuBuilderPro – Import csv into a table of your application MySQL database (Its very easy)
I don’t know about you but for me its pretty rare to start an application without any information. At the very least there may be lookup tables or you have information collected in a spreadsheet. Thus when I came to nuBuilderPro one of the first things I researched was how to get information into a table. nuBuilderPro uses a vanilla version of mySQL in the background so this is what we will be working with. We will be attempting to import a csv file. You will need a clean organised csv file.
First create the tables that you require information to go into. Ensure that you have exactly the same table structure as the csv file that you wish to import. Therefore either adjust the table or the csv appropriately. Failure to have the same structure will halt the import.
Next navigate to the administration panel using your particular variation of the below url. Note that it is important to have the / at the end of the url otherwise you will be taken to the more specific database administration page where you design forms. Don’t worry if this happens you can still get to the php administration page by hitting the databases button. In fact this is an alternative way of getting to the screens that I show here.
https://youracount.nubuilder.net/nuadmin/
Use your username and password to get into the nuadmin index panel
Once you have entered your username and password appropriately you should be at the following address
https://youraccount.nubuilder.net/nuadmin/index.php
Now select the small spanner sign in the top right – this takes you to the php admin section for your whole VPS there are other ways of going into this web page but we will go this way for now.
You should be taken to a section which looks as follows
All databases within your VPS should be listed on the left. Each new application will have a database created for it. Each database holds all the required tables that hold your database and are listed on the left hand side. Click on the database in question and then hit structure. You are interested not just in the database but also the particular table. There is a notification grey line at the top of the page which shows you what database and what table you are in.
Importing a csv is a straightforward process of hitting the import button at the top selecting the csv file and hitting the go button. If the csv file contains column names you may wish to alter the row at which import starts.
Once import has been completed it will indicate how many lines were imported and how long it took. If there are problems you will obtain a message indicating so. I tried to create a simple Russian / English dictionary and it was really very straightforward. It is important that the csv has the right number of columns as per your designed table.
nuBuilderPro – a database driven Web Framework for simple Form Design
I have been seeking a way of designing simple web forms for a reasonable cost for some time. I was continually coming across either very professional products with really quite large annual charges (alpha anywhere) or alternatively products that charge a per seat cost for users (zoho creator). Fine I don’t mind paying for software I really don’t but what about a project that only brings in $2000 dollars a year how can I justify Alpha Anywhere’s costs for that or how can I give access to individuals who will need an application very rarely but for whom there is real value in knowing this information. I have long believed that software costs should be coming down in cost rather than ever increasing and getting users interested in your applications is often impossibly hard at the best of times when there is no cost let alone when you tell them that they need to shell out for a subscription. What happens to the user who only needs something once in a blue moon or a user not from within your department within a section where you have no control of the budget? I’ve recently had a lot of success with open source software and as a result I have been scouring the internet for options for some years. Recently I found a project called nuBuilderPro from an Australian based development house called nuSoftware. nuBuilderPro is version 3 of what I think is their own framework. It looked good and what’s more although based on the LAMP stack which is something I’ve had very little dealings with they offered a fully hosted VPS service where I could experiment and get my bearings. They completely host the development environment for a very very reasonable price. Given that I didn’t know whether it was going to be useful to me yet this seemed an ideal opportunity to experiment. It has only been a short time since sign up but I can already see that it will prove useful. I have signed up for the most basic of server accounts and at 17th of January created 3 very basic applications. All in about 3 hours of work. Thats what I like to see proper RAD development.
The website is here.
And the options for hosting are here
www.nubuilder.net/hosting.php
Importantly you can start small get to know the product and work up – additional users do NOT cost you extra money, login security comes built in and you are free to design as many applications as you can fit within your VPS. Given that when I first signed up I didn’t have any users or applications and didn’t know how to design applications, this seemed completely ideal.
So far it looks like I’m going to use it for small but important applications that need very quick development that are very distributed. That’s not to say it couldn’t be used for much larger applications but I need to run before I can walk. Given the underlying database engine and stack it is likely that it will be able to handle much bigger projects than I am likely to throw at it. Although Open Source all but the top of the stack has been in solid development for many years I guess the question mark is over the control management framework for the database integration and form building UIs which is somewhat new and untested. I would very likely have gone with something like Lightswitch but Microsoft have been giving a distinct lack of commitment to any one RAD web tool recently. Certainly it is true that very few individuals seem to know about it and the forum is somewhat quiet compared with some open source projects. Importantly though I haven’t come across any other open source projects with the price model that they have and importantly I can easily get any information in and out so I don’t believe I am taking any risk. I hope to be investigating it further over the coming months. From what I have seen so far I am very impressed and did I say it had a unique price model anyway watch this space!
MS Access and Forms – Create a Filtered Autonumber for Child Records
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.
VBA Function Boolean Switch to test for specific character sets within a field
Boolean Switch to test for specific character sets within a field. This codes tests whether a field contains blanks or the specified characters only and will return -1 if true and 0 if false. If a character occurs that is not within the LValid_Values it will return 0 as false. This is different from identifying whether a field contains the listed characters. This can be useful for identifying characters in a field that you are wanting to alter the variable type. MS Access (and other databases) will delete field values that cannot be converted so if possible you want to identify values with illegal characters. This code can be used to identify this. Change the value of LValid_Values to represent the allowable characters and then you can reference the function in a query to identify illegal records and values. My primary use case is testing for numerical values in a string field which I am looking to alter so that I can change it into a long integer variable type. This is particularly useful for hunting down things like letters in house numbers or slashes in flat identities.
Function CharCheck(targetField) As Boolean Dim LPos As Integer Dim LChar As String Dim LValid_Values As String 'Start at first character in strField LPos = 1 LValid_Values = ".0123456789" 'Test each character in strField While LPos <= Len(targetField) 'Single character in strField LChar = Mid(targetField, LPos, 1) 'If character is not LValid Value, return FALSE If InStr(LValid_Values, LChar) = 0 Then CharCheck = False Exit Function End If 'Increment counter LPos = LPos + 1 Wend 'Value is LValid Value, return TRUE CharCheck = True End Function
Ranking of Child Records according to Groups
Imagine you have a school full of Students and they have done a variety of exams. All results are collected in a table and you would like to obtain rankings by subject. How can you automatically rank all the students for whom you have results.
The table T01Student
PKID Students Marks Subject
1 Tony 34 Maths
2 Bob 32 Maths
3 Thor 48 Maths
4 Jack 42 Geography
5 Tom 41 Geography
6 Kate 45 Geography
7 Sid 26 Geography
8 Michael 40 Chemistry
9 Colin 50 Chemistry
10 Hannah 60 Chemistry
11 Geoff 5 Chemistry
12 Jim 2 Chemistry
It is then possible to use the following query to get a ranking
SELECT (select count(*) from T01Student as tbl2 where T01Student.marks < tbl2.marks and T01Student.subject = tbl2.subject)+1 AS rank, * INTO TempRank FROM T01Student;
rank PKID Students Marks Subject
2 1 Tony 34 Maths
3 2 Bob 32 Maths
1 3 Thor 48 Maths
2 4 Jack 42 Geography
3 5 Tom 41 Geography
1 6 Kate 45 Geography
4 7 Sid 26 Geography
3 8 Michael 40 Chemistry
2 9 Colin 50 Chemistry
1 10 Hannah 60 Chemistry
4 11 Geoff 5 Chemistry
5 12 Jim 2 Chemistry
Then use a simple select query to order by subject then rank – Note Depending if you want to count down from the top so the lowest “Marks” gets the highest rank reverse the < symbol or reverse the order of rank - here I have highest mark is no 1. Subject rank Students Marks Chemistry 1 Hannah 60 Chemistry 2 Colin 50 Chemistry 3 Michael 40 Chemistry 4 Geoff 5 Chemistry 5 Jim 2 Geography 1 Kate 45 Geography 2 Jack 42 Geography 3 Tom 41 Geography 4 Sid 26 Maths 1 Thor 48 Maths 2 Tony 34 Maths 3 Bob 32 If for some reason you are wanting to store the rank so that you can artificially alter the ranking then it would be possible to use make table to create a new table with the ranking and then update a position field with the rank in the ranking query based on the PKID
QGIS 2.8.1 – Useful Functions and Operators – Field Calculator
Calculate eastings and northings of centroid within polygon layer
xmin(centroid($geometry))
ymin(centroid($geometry))
Calculate area and perimeter of a polygon layer
$area
$perimeter
Calculate eastings and northings of a point layer
$x
$y
Calculate the length of a line layer
$length
Capitalise column values
upper(Field)
eg upper(Town)
Edinburgh becomes EDINBURGH
Camel case column values
title(Field)
EDINBURGH becomes Edinburgh
DUDDINGSTON LOCH becomes Duddingston Loch
Lower case column values
lower(Field)
Replacethis withthat in string
replace(string, replacethis, withthat)
Concatenate string a and string b
Concatenate a || b
Division and next line Multiplication
a/b
a*b
area/10,000 – divides area field by 10,000 (eg going from m2 to Hectares
Remove decimals from a field
toint(area)
eg 7954.235 becomes 7954 and 456525.325 becomes 456525
Index a set of polygons
$rownumber
QGIS 2.8.1 Getting Shape Files into SQL Server 2008 Express R2
For digital mapping the shp extension is the equivalent of csv files – A significant amount of information is still held in shape files and even if it is not, nearly every GIS package can export to shape format. It’s therefore pretty vital that you understand how to get this format into your backends.
Turns out QGIS 2.8.1 comes with a very handy excecutable called ogr2ogr.exe
On my computer this was downloaded with my installation of QGIS 2.8.1 and placed in the the following directory
C:\Program Files\QGIS Wien\bin
It looks like this executable has been a part of the the download for sometime so if you are on a different version of QGIS I suspect the file will be on your machine but in a directory other that QGIS Wien – whatever your version is.
If in doubt a simple search on ogr2ogr should give you the location.
From the command prompt you need to either navigate to the location of ogr2ogr.exe or place the full path into the instruction. In the following I have navigated to the appropriate directory using change directory command at the prompt. I then input the following.
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=MARK-LENOVO\SQLEXPRESS;database=Geodatabase;trusted_connection=yes" "C:\Users\Mark\Documents\shp\polygon\n_america.shp"
On return it will start to import the information creating a new table in your SQL Server instance within the database listed in your parameter string. It looks like it just names the table the same as the shape file, I suspect if that name already exists as a tablename in SQL Server that table will be overwritten with the new shape file. Also note that the import process can take a fair bit of time for large files so be patient. I tested it initially with a small import which it did relatively quickly, I then went and hit it with 500 thousand records and it took a little over 2 hours. Still good to know that it can cope.
Once you have imported the information into SQL you should perform some form of spatial indexing on the table.
I have noted that layers that have spatial indexing are drawn differently than non spatial indexed layers. Layers with spatial indexes are drawn in more rapidly all over the district much like a spray from a can. Non spatial indexed layers appear on screen slower as if painted from one side to the other.
QGIS 2.8.1 Raster / Extraction / Clipper – Extent Quick Reference
Using SQL to parse, clean and format strings
Many datasets can be somewhet confused by the time you get them. Maybe you had no control of the export from the database or maybe you asked for the right information and it came back somewhat warped.
SQL has powerful fuctions that can pretty much clean things up however you would like.
We can use multiple SQL commands within an MS Access module to clean up a source by placing them consecutively within a module here is the structure of some of the queries that I use.
UPDATE SELECTED FIELDS BASED ON A MATCHED STRING IN ANOTHER FIELD
SQL that updates Town and PostalTown fields based on a string in an aggregated PostalAdd field.
Please note that _ sign denotes a movement to another line within the VB Module required to make the SQL String run correctly. This may require alteration if you are cutting and pasting from this page.
Dim SQL As String SQL = "UPDATE Table01 SET Table01.Town = 'Barassie', Table01.PostalTown = 'TROON' " & _ "WHERE (((Table01.PostalAdd) Like '*Barassie, TROON*'));" DoCmd.RunSQL SQL
CONVERT A STRING FIELD TO ALL CAPITALS, ALL CAMEL CASE OR ALL LOWER CASE
The following SQL converts the street field of Table01 to all capitals. This could be run like the previous SQL from within an MS Access module
Dim SQL1 As String SQL1 = "UPDATE Table01 SET Table01.Street = StrConv([Table01].[Street],1);" DoCmd.RunSQL SQL1
In the above code change the trailing number parameter to select type of alteration
1 – ALL CAPIALS
2 – all lower case
3 – Camel Case
PARSE OUT LEFT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
This looks to the Yourfieldname field of TableRainbow and searches from the left for a comma and returns everything to the left into a field called LeftParse
Dim SQL2 As String SQL2 = "SELECT Left$([Yourfieldname],InStr(1,[Yourfieldname],",")-1) AS LeftParse FROM TableRainbow;" DoCmd.RunSQL SQL2
PARSE OUT RIGHT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
If you have a string with commas this string will count the length of the string then count the number of characters to your unique string – in this case a comma – and then return all characters from that string to the right of that comma.
The below code looks to the Yourfieldname of TableRainbow counts its length and then find the first comma from the right and returns the information as a select query result in a field named Right Parse. It should be noted that it searches through the target field searching from the left. IF there are multiple commas then it will stop counting when it hits the first comma. You can substitute the right part of the function with a number.
Dim SQL3 As String SQL3 = "SELECT Right$([Yourfieldname],Len([Yourfieldname])-InStr(1,[Yourfieldname],",")-1) AS RightParse FROM TableRainbow;" DoCmd.RunSQL SQL3
SQL Saturday #388 Edinburgh
Went along to SQL Saturday BI Edition on 13th of June.
Honestly I thought it was really great. Many thanks to Jenny Stirrup for being the event organiser. If you are thinking about going next year you really should.
This was the schedule for SQL Saturday in Edinburgh
Sessions I attended
Key Note with Jon Woodward – on long term future of computing in general
Web Scraping with Python – Iain Elder of Sky Scanner
Better Data Visualisation for CRM and ERP – Adam Vero
The European Economic Crisis and the Euro-A Data Tale – Carmel Gunn
Blue Sky Thinking : SQL Azure Geospatial Mashup – Thomas Sykes
Master Data Management – Dave Lawrence
All the talks were really thought provoking and nice to hear from people who are really at the top of the game. I have already started experimenting with Web Scraping and Python.
Connecting to PostgreSQL 9.3 from QGIS 2.8.1 – local host
First ensure that you have both Postgres and QGIS installed on your machine.
In order for you to be able to connect to Postgres from QGIS on local host you must ensure 2 things. Firstly that the PostGIS plugin has been installed on your laptop AND secondly that you have included the postgis extension in each database that you wish to connect to. Without enabling the extension in the database you won’t be able to connect OR import shape files. Installation of PostGIS is often a default during the install of postgres but you can check whether this was completed correctly by using the Application Stack Builder, a small program that is installed with later versions of postgres.
I navigated to this on the win 8.1 machine I was using by using search.
Opening application stack builder you will be presented with the following.
Expand the spatial extensions tree to identify if you already have the PostGIS plugin installed – if not – select as appropriate the plugin and you will be prompted to install. You will need an internet connection for this. Above you can see that my plugin was already installed.
Next you need to add the PostGIS extension to each Postgres database you wish to link to from QGIS this is done through PG Admin.
This is something that both myself and a colleague got caught out by and it took me an hour of searching to find how to fix it.
Below I have a database called GISData which I have just created. You will note there is only one object within the expanded extensions tree. You will not be able to connect to a database that does not include PostGIS in its list of extensions
Hi-light the database you want to spatially enable then go to Tools – Query Tool( Ctrl + E will do the same). In the above picture I’ve slightly jumped the gun. To add the extension to the database type.
CREATE EXTENSION postgis
Run the query by selecting the green right arrow
There will be a short delay and then upon refresh of the connection postgis should appear in the list of extensions.
You can now close the Postgres administrator and return to QGIS where you should be able to setup the connection to the database.
Parameters should be similar to below and it is useful to test the connection prior to saving.
QGIS 2.8.1 Updating Copyright Label in your QGIS Projects
Specific example QGIS 2.8.1 probably same in other versions.
Ensure you have copyright quote correct on QGIS projects
View / Decorations / Copyright Label