Microsoft Access Sydney Australia

Sql Server compatible Syntax...what it means for access

2008-12-10T16:05:00.000-08:00

Had a small hicup with an access database which had a nice little filtering system which just stopped working when moving to a more secure workfile.

Found that setting the filter to be [Surname] Like 'A*' was the issue.

It kept changing to using Alike 'A*' in the advanced criteria.

All we had to do was use % instead of * and all was well.

This will work better when we do move the backend to sql server!

Tom Bizannes

Sydney, Australia

VBA opening comments in Excel

2008-08-18T23:26:00.000-07:00

'This was a pain in the neck but if there was a better way than trapping the error when no comment, it would be good to know!

Sub test()
Call AppendComment(ActiveSheet.Cells(4, 1), "Let's try again")
End Sub

Public Sub AppendComment(r As Range, sCommentToAdd As String)
Dim sComment As String

On Error GoTo Err_AppendComment

sComment = r.Comment.Text
If sComment = "" Then
r.AddComment (sCommentToAdd)
Else
r.Comment.Delete
r.AddComment sComment & vbNewLine & sCommentToAdd
End If
Exit Sub
Err_AppendComment:
If Err.Number = 91 Then
sComment = ""
Resume Next
Else
MsgBox "Error Appending comment to " & r.Parent.Name & vbNewLine & Err.Description & vbNewLine & "Error Number:" & Err.Number, vbCritical
End If
End Sub

City to Surf 2008 is coming

2008-07-22T22:30:00.000-07:00

Have lost count of how many I have entered.

Luckily the sun-herald emailed me past times.

Here goes:

Year:2007 Time:98:12 Place:19,164

HTML Codes for Copyright and Trademark

2008-05-01T00:01:00.000-07:00

You need to put these in to get the TM sign or Copyright symbol.

"™" = ™ Trade Mark Sign
"©" = © Copyright Symbol

The first one is a doozy as it used to be "™

Tom Bizannes
http://www.macroview.com.au

Nokia 6110 Navigator

2008-03-30T05:11:00.000-07:00

Finally got a new mobile phone this week.

The Nokia 6110 with the GPS unit etc.

We'll what fun and games I had trying to load some podcasts!
More on that later.

The GPS is fine, but it does chew up battery time.
We were heading over the harbour from Circular Quay to Manly at 24km/h according to the phone.
This needs alot more testing before I can say whether it is useful or just a gadget!

Regards,
Tom Bizannes
Sydney, Australia

VBA Get the last day of next month

2007-12-08T17:29:00.000-08:00

This is courtesy of Peter Vincent:

He wrote this for an invoicing application he was working on...

Get the last day of next month (VBA)

Get the 1st of 2 months from now and take one day away.

Format(DateAdd("d", -1, Format(DateAdd("m", 2, now()), "1, mmmm, yyyy")), "d, mmmm, yyyy")

Links for games

2007-12-08T16:57:00.000-08:00

Microsoft Access VBA Delete Error Tables

2007-11-22T01:59:00.000-08:00

Public Function iDeleteErrorTables() As Integer
'Returns the number of error tables deleted
Dim obj As AccessObject
Dim dbs As Object
Dim iCount As Integer

Set dbs = Application.CurrentData
iCount = 0
' Check each object of the AllTables collection
For Each obj In dbs.AllTables
If Right(obj.NAME, 6) = "Errors" Or Right(obj.NAME, 7) = "Errors1" Then
DoCmd.DeleteObject acTable, obj.NAME
iCount = iCount + 1
End If
Next obj

DeleteErrorTables = iCount

End Function

Ado Connecting Excel to Microsoft Access

2007-11-14T15:09:00.000-08:00

'Code for the basic structure for getting and ado recordset in excel on an Access Database
Option Explicit
Public Const sql_Connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb;" 'Persist Security Info=False;"
Private AdoRs As ADODB.Recordset
Private AdoCnn As ADODB.Connection
Private sSql As String

Public Sub LoadTotalsAndAverages()

Dim sSql As String

sSql = "SELECT sum(MetricValue) as Total From Snapshot "

Set AdoCnn = New ADODB.Connection
AdoCnn.Open sql_Connection
AdoCnn.CommandTimeout = 120

debug.print GetValue(sSql)

AdoCnn.Close
End Sub

Private Function GetValue(sSql) As Double

Set AdoRs = New ADODB.Recordset
AdoRs.Open sSql, AdoCnn, adOpenStatic, adLockReadOnly

If AdoRs.EOF Then
GetValue = 0
ElseIf IsNull(AdoRs(0)) Then
GetValue = 0
Else
GetValue = CDbl(AdoRs(0))
End If

AdoRs.Close

End Function

Office Dev Con weekend in Sydney 3rd to 4th November 2007

2007-11-05T02:15:00.000-08:00

Saw lots of new tips and tricks!
It's that 10% you don't know that can give you the edge out there, and this was a productive weekend!

There was heaps of people as well as many who are like me a jack of all trades in Microsoft solutions. Office / .net / reporting services and database experts without that funny air those c# only developers have....

A little about the speakers ...

Armen Stein:

Very impressive presentations……
Easily the best presenter of the conference...
He has a book out about Access 2007 from wrox….This sounds quite interesting!
Liked the way he creates his search index pages!

Will have to copy this technique.

Also how he opens another form whilst hiding the originating one but on close you then go back to the original form.

He also presented how he handles projects…
Loved the triangle Cost / Features / Schedule….
Also how he said never show full mocked up screens as the client will think you have done 90% of the work!
He does them all in Visio…With an interesting excel task list he will make available….

Spoke to him afterwards and it was interesting how his firm in Seattle does a lot of hybrid applications. Part Access front end, SQL Server back end and some .net.
As he said, Access front end is great for all those admin functions and reduces the design time versus .net. He adds .net for some mobile or remote users who often have simpler requirements.
A lot like what I do....

Loved the bit about upsizing to SQL Server and all the gotchas….
You can’t just throw your access backends into SQL and expect improved performance unless you do a few other tricks like the proper use of pass through queries.
There is even the case for moving some functions into SQL server as well to improve the performance of particular queries…

Adam Cogan…someone lend him a razor!.

He still hasn't shaved since before the SQL Server Code camp at Wagga Wagga three weeks before!

Says he is a Report Services insider….Will have to bug him to bug Microsoft about some issues with SharePoint integration.

He didn’t go into any issues, tricks with upsizing MsAccess Reports into Reporting Services as he more just covered the basics.

Adam also showed how to move Access tables into SharePoint lists with Access 2007 and the offline capability of Access 2007 with SharePoint lists.

Had to pick him up on a few points as this is my speciality and he makes comments which aren't always technically true!

Still he does present his stuff okay....

Ivan Wilson

He did a quick run through of Moss Searching components…

And then showed how to quickly create a custom search to bring back a phone list for a particular department.

Interesting how Visual Studio makes this much easier than the old way….

Ed Richard

Interesting how he makes Office Addins in Office 2007 look so easy to code for!
All his examples were interesting and followed by his comment, only a few lines of code! He makes the idea of Programming Office 2007 add-ons look too easy!
Some good ideas like forms in Outlook 2007 and showing a reporting services task list in Outlook Today!
He is a jack of all trades with using office addins and reporting services etc etc..

Interestingly he said it is far easier to create vsto add-ins in vb.net than c#!

Andrew Coates

Visual Studio 2008 and click once looks like hitting the mainstream as a simple way to deploy office applications!
Andrew stepped through what was involved and it seems to be a lot easier and simpler to do now!

Cannot wait for the final release of this product!

Regards,
Tom Bizannes
http://www.smartbiz.com.au

MsAccess 2007 run-time released!

2007-07-26T04:57:00.000-07:00

This is one of the most exciting Access Releases yet!

This can only lead to thousands of access applications that will require tweaking at a later date!

Obviously Microsoft are hoping this will lead to more sales of the professional version of Office 2007…( the one with Access 2007)

It used to cost a few dollars to be able to create and distribute run-times and now it’s free..

It’s funny how a competitor is advertising courses on migrating Access to SharePoint!

Bet we can do it better as Reporting Services can suck out the access reports – if they are designed right, plus adding on some asp.net or put data into lists, and hey presto migrated!

Also, like how Access 2007 hooks into SharePoint and just need to tweak my code to refresh the linked lists to cater for changes people make and you have a very powerful internet enabled version of access.

Access 2007 Developer Extensions

http://www.microsoft.com/downloads/details.aspx?FamilyId=D96A8358-ECE4-4BEE-A844-F81856DCEB67&displaylang=en

What else is exciting is some of the other Office 2007 downloads like the icons gallery!

http://www.microsoft.com/downloads/details.aspx?familyid=12B99325-93E8-4ED4-8385-74D0F7661318&displaylang=en

Tom Bizannes
http://www.macroview.com.au

.Net User group on Wednesday 17th January 2007

2007-02-13T04:07:00.000-08:00

No Pizza this time!...only sushi…..almost too healthy….

News:

Timesnapper great for seeing what you did for the day! Could help when you are doing your timesheets….as this is a different twist as it takes snapshots of your desktop at configured intervals…

Firebug which is an extension in firefox is great for checking your web sites….and you can even change things in the browser to see what it would look like if….

Virtual PC 2007 Release Candidate….Some said this was problematic…..compared with the beta…..

The new Expression Web looks like a great tool for pure web designers…..Except we don’t get it with MSDN!

http://www.microsoft.com/expression

What was nice is how is automatically creates separate css files etc which I normally have to do by editing the html pages.

It is also better for “cleaning” up a site as it shows you which styles etc are used and not used etc…

I think that SharePoint designer has almost the same features except for a few different wizards….and less standard compliant code for backward compatibility with Front Page.

Will need to play more with SharePoint designer though….

Note: Currently using SharePoint designer instead of front page 2003 and it works fine…..

The new Microsoft Access 2007 is great! And looks very sexy! …..All it needs now is Jenifer Hawkins…….

Anyway this product will be used by many business users for sure which means more work modifying and enhancing Access Databases for years to come.

Adam picked on me because I asked if the Access 2003 issue with multiple choice fields when linking to SharePoint was fixed in 2007!

Wow….now this many to many concept has been simplified and makes database design with Access so much easier and powerful!

Memo fields are now on steroids!..You can do rich text in memo fields….Now I can do my automated spamming ..oops I mean bulk emailing tool with nice text without any html stuffing around….

Memo fields also have versioning etc….

Graphics now can be stored a jpgs and even linked to a file on a hard disk.

Reports have alternative row colouring and an option.

Emails are fixed in Hyperlinks! Before if you used access to edit\update hyperlinked fields it would put an http in front of the mailto:....

Simple click to upgrade the tables to SharePoint or SQL Server.

Apparently some of the access guys went to the SharePoint team and then came back for the 2007 build….The end result is better linking with SharePoint and this powerful new feature.

Also all the ad-hoc querying is made more powerful in that you can sort, filter and even do aggregates without any coding or sql……

There’s a lot more to the new access even the format to cater for the multi-choice etc….But you can hook into older database versions….

Interesting feature is that you can’t reference both ADO and DAO in the references at the same time!...

This might cause some issues as this is needed sometimes when you use dao for linked table checking etc and ado for sql server access…..

There is no replication. No Security ( Every is putting their tables in SQL so that is where to security is) or you can use the older format for security.

The new formats .accdb, .accde, .accdt etc

Note renaming to .accdr can be used to simulate run-time….awesome…..

And lastly the pdf add-in…

Looking forward to using and deploying Access 2007

Tom Bizannes
Databases & Business Intelligence
http://www.smartbiz.com.au

2006-07-11T05:01:00.000-07:00

Refreshing links in a database.

After looking at many codes samples, most of the time you want to move the databases to a different directory and relink to the data file in that directory.

This code acheives that purpose:
Note: We use the LastInStr function for backward compatibility with older MsAccess Versions

Public Sub RefreshLinks()Dim sPath As String
Dim sOldPath As String
Dim sDatabaseName As String
Dim iPlace As Integer

sPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\"))

For Each tdf In CurrentDb.TableDefs

If InStr(1, tdf.Connect, "DATABASE=") Then

iPlace = LastInStr(tdf.Connect, "\")
sOldPath = Left(tdf.Connect, iPlace)
'Relink if the paths are different

If Not (UCase(sOldPath) = UCase(";Database=" & sPath)) Then
sDatabaseName = Right(tdf.Connect, Len(tdf.Connect) - iPlace)
sNewConnectionName = ";DATABASE=" & sPath & sDatabaseName
tdf.Connect = sNewConnectionName
tdf.RefreshLink

End If

End If

Next
End Sub

Public Function LastInStr(strSearched As String, strSought As String) As Integer
On Error Resume Next

' This function finds the last instance of a character within' a string of characters and returns an integer representing' the final position of the desired character.'' Typically, this function us used to find the final "\" in' a file path string

Dim intCurrVal As Integer
Dim intLastPosition As Integer
intCurrVal = InStr(strSearched, strSought)

Do Until intCurrVal = 0
intLastPosition = intCurrVal
intCurrVal = InStr(intLastPosition + 1, strSearched, strSought)
Loop

LastInStr = intLastPosition

End Function

This code was written by Tom Bizannes
Microsoft Certified Professional
MsAccess and SQL Server Specialist in Sydney Australia
http://www.smartbiz.com.au

Good reference for Microsoft Access

2006-05-08T04:37:00.000-07:00

Found this guy had some great articles and code on MsAccess.

http://www.databasejournal.com/features/msaccess/article.php/3567511

His name is Danny Lesandrini
Danny J. Lesandrini, a Microsoft Certified Professional in Access, Visual Basic and SQL Server, has been programming with Microsoft development tools since 1995. He maintains a web site with free code samples at http://amazecreations.com/datafast.

Loved his simple way of describing advanced reporting and linked of tables etc.

Tom Bizannes
http://www.smartbiz.com.au