Microsoft Access VBA Delete Error Tables

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

'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

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